Navigation:  Articles > Jul-1998 >

Right Clicks and Numbers

Previous pageReturn to chapter overviewNext page

Angela J.R. Jones          
Angela solves a few day-to-day form and report problems, including how to stop dangerous right-clickers and how to dynamically add line numbers.
See question 2 called Subforms  that went with this article
I've worked hard to secure my database, but I just noticed that right-clicking a form brings up a popup menu, and the first choice is Form Design! This seriously compromises the security of the system, so how do I disable it?
Good eye! (See Figure 1.) The properties involved in the right-click popup menu are Shortcut Menu and Shortcut Menu Bar, and there are two relatively simple ways to solve your problem.

199807_aj1 Figure 1
The first option you might try in solving your problem is to disable the popup menu altogether. Right now, the Shortcut Menu property on your forms is set to Yes (the default value), which allows your users to go into the design view of the form, even if the standard toolbars are disabled. So all your hard work to implement security measures (and keep your end-users from wandering into confusing and dangerous territory) is in danger of becoming meaningless by a single right-click.
As Access Help puts it, "You might want to disable a shortcut menu to prevent the user from changing the form's underlying record source." No kidding . . . Here's a simple routine that cycles through all the forms in your database, changes the ShortcutMenu property to False (No), and saves the forms if they've been changed.

Sub NoShortcutMenu()

'This function cycles through all forms, 

'sets the ShortcutMenu = False,

'then debug.prints a list of altered forms.


     Dim db As DATABASE, cnt As Container

     Dim doc As Document, x As Integer, frm As String


     Set db = CurrentDb

     Set cnt = db.Containers("Forms")


    ' cycle through the Forms & disable Shortcut menu

     Application.Echo False

     For x = 0 To cnt.Documents.Count - 1

         Set doc = cnt.Documents(x)

         frm = doc.Name

         DoCmd.OpenForm frm, acDesign

         If Forms(frm).ShortcutMenu = False Then

             DoCmd.Close acForm, frm, acSaveNo

             Debug.Print x & " " & frm & " OK"


             Forms(frm).ShortcutMenu = False

             DoCmd.Close acForm, frm, acSaveYes

             Debug.Print x & " " & frm & " FIXED"

         End If

     Next x

     Application.Echo True


End Sub

My co-workers and I run this periodically during development, and always before deployment.
Your second option is to program your own popup menu that only includes helpful and harmless functionality. When the Shortcut Menu property is set to Yes and the Shortcut Menu Bar is set to the name of a custom toolbar, then the custom toolbar is shown when the user right-clicks instead of the default Shortcut Menu Bar with the dangerous Form Design functionality. Unfortunately, describing how to program your own toolbar is too big a topic for this column, but there's a lot of good information in Access help. A good topic to start with is "Create a custom shortcut menu for the current database."
If you use the option of creating your own Shortcut toolbar, make sure you set the toolbar's Type properties to Popup. Setting the Type property to Popup causes the toolbar show up in the list of choices for the Shortcut Menu Bar form property.
My system needs the same report sorted three different ways. I could make three separate reports, but then I'd have to make any change to the report in all three versions. This makes me nervous, since I work for a start-up company and seem to get change requests every day. Any suggestions?
You're absolutely right -- if you were in a situation where the report was extremely well-defined and stable, you might want to make three reports. However, in your circumstance, you definitely don't want to make three separate reports. Good for you for planning ahead!
First, you'll need to run the report from a form that allows the user to select sorting options (see Figure 3). In my example form, the report can run by Branch office or by Date. I chose to use an Option Group so the user would have to choose one sort order.

199807_aj3 Figure 3
The code for the On Click event of the OK button on the form is what makes the solution work. By manipulating the OrderBy and OrderByOn properties of the report, I'm able to choose a sort order at runtime. Notice in the following example that the report really sorts on four fields, not one, and I pass the whole OrderBy string to the routine:

Dim strReport As String, strCriteria As String 

Application.Echo False


strReport = "OverallTransactions"


Select Case Me.optSortBy 

 Case 1 

   strCriteria = "Lender, Branch, ProjID, TransacDate"

 Case 2

   strCriteria = "TransacDate, Lender, Branch, ProjID"

End Select


DoCmd.OpenReport strReport, acViewPreview

Reports(strReport).OrderByOn = True

Reports(strReport).OrderBy = strCriteria

Application.Echo True

Since my example only has two sort possibilities, I could have used an If...Then statement instead of the Select Case statement. I prefer Case statements, just in case I need 10 more options in the future (heaven forbid!).
You might find that even though you turned off the Echo, you can still see disturbing screen flashes as your report re-sorts. If that happens, just put a time-killer between the OrderBy line and the Application.Echo line. My standby time-killer code is:

Dim tmr

tmr = Timer + 3

Do Until Timer >= tmr



That's guaranteed to kill at least three seconds and the flicker. If you need more time, just adjust the code accordingly.
I need to have dynamically created line numbers that match on a form and a report, but I don't actually want to store the line numbers in a table because they could end up in a different sort order once things are altered (details are sorted alphabetically). How do I do this?
Putting dynamic line numbers on a report is fairly straightforward. First, in the query underlying your report, create an extra field called LNum (or whatever you want to call it) that has a constant value of 1 (so the field syntax is LNum: 1). That way, each record has the number 1 in it. Next, put the LNum field on your report, and set the RunningSum property of the control to "Over Group" or "Over All." Presto! Instant dynamic line numbers.
Putting dynamic line numbers on a form is less straightforward, but certainly possible. The key is to harness the power of the form's RecordsetClone property, which returns a Recordset object. Therefore, you can access the AbsoluteValue property of the form's recordset without having to re-run your query by getting the form's recordset from the RecordsetClone property! Very spiffy. (By the way, if you're a Technet member, article Q120913 has a similar solution, but I've modified the code to run more quickly.)
The form's recordset must have a primary key field (a field that uniquely identifies each record). If it does, then you can go on to create a text box control on your form to display your line numbers. After adding the text box, set the text box's ControlSource property to:

=LineNum([Form], "KeyFieldName", [KeyFieldName])

This passes in the active form, the name of the key field, and the value of that key field for the current record. Then, create the following function, either in the form as a private function or in a separate public module:

Function LineNum(frm As Form, KeyFieldName As String, _

     KeyFieldValue) As Long

Dim rst As Recordset

On Error GoTo LineNum_Err


Set rst = frm.RecordsetClone


'Determine field type, then find that record.

Select Case rst.Fields(KeyFieldName).Type

  'Numeric data type

  Case dbByte, dbInteger, dbLong, dbSingle, _

      dbDouble, dbCurrency

      rst.FindFirst "[" & KeyFieldName & "] = " & _


  'Text data type

  Case dbText

      rst.FindFirst "[" & KeyFieldName & "] = '" & _

          KeyFieldValue & "'"

  'Date data type

  Case dbDate

      rst.FindFirst "[" & KeyFieldName & "] = #" & _

          KeyFieldValue & "#"


  Case Else

      GoTo LineNum_Err

      Exit Function

End Select


LineNum = rst.AbsolutePosition + 1



   Exit Function



   LineNum = 0

   MsgBox "Error finding line number! " & _

       Err.number & ": " & Err.Description

   Resume LineNum_Exit


End Function

Then you have line numbers! See Figure 4 for an example -- a rapid data entry form where the line numbers represent order questions from an online questionnaire. Since question order is determined at the time a person takes the questionnaire, the answers must be dynamically generated, not coded into the question.

199807_aj4 Figure 4
In closing
As much as I enjoy venturing into fun stuff like ActiveX controls or OLE graphing, I keep finding that most of my customers just need solid working forms and easy-to-read, accurate reports. All four of these questions are things I run across all the time, and the answers are immediately usable for most developers.
Angela J.R. Jones, MCPS, is an analyst/developer for Apex Consulting Group in Northbrook, IL, and a co-author of New Riders' MCSD Study Guide: Access 95. The J.R. stands for Jane Reeves (middle and maiden names), not Junior, as her junk mail suggests. A pet peeve of hers is software that can't accommodate two middle initials.