Access Form Tips

(Note many code examples here are in Access 2 format some syntax adjustment may be required for Access 95 or 97)

Table Topics | Queries and Recordsets

Report Topics | Modules & API Code| General Access Topics

Forms Movement

Controlling Movement in and out of a Sub-Form

How to Tell if the User Moves to a New Record

More to Come.....

Form Speed

Speeding up Combo Boxes

General Issues on Form Load Speed

Do Your Forms Which Have Filter Options Take "Forever" to Close in Access 97?

Presentation

Using the "Tag" property to manipulate a Form's Controls

Create A Custom Meter/Progress Bar

How to Fill a List Box with a List of Reports

Other

Add Just One Delete or Record Button To Control Both A Main Form and SubForm(s)

How to Create your own Spinner Control

Use a variable to reference a table/query field or form control

© 1996-98 ATTAC Consulting Group






Back to the Tips List

Speeding Up Combo Boxes on Forms and the loading of Forms that contain Combo Boxes.
Combo Boxes are one of the easiest way to allow the user to pick from a category of input options or to pick a specific record. However, if the combo box is filed with data such as a list of customers, products or orders, or any other data which can run into the thousands of records, or is based on a complex query, this can dramatically slow down both the time it takes to open the combo box itself, as will as the time it takes to open and load the Form which contains the combo box. Form loading can become a problem because Access runs the query and sorts the records to fill the combo box at least twice just when opening the form. If you have multiple combo boxes on your form, this can make the form seem sluggish. Here's some hints to speed up the process of form loading and opening of combo boxes with large number of records:

1. It may sound self evident but make sure that the sort and parameter fields in the underlying query are indexed fields in the tables used.

2. Limit the number of fields returned to the combo box in the underling query.

3. Leave the Row Source for the combo box blank in its properties sheet. Then add an event procedure to the "OnEnter" event of the combo box. In that procedure set the combo box's RowSource, to the desired query or table by specifying:

       Me!ComboBoxName.RowSource = "queryortablename"

This will make the form load faster by not running the combo box query until the user requests the data. (The combo box query is usually run again when the user selects it anyway.)

4. Set the "Auto Expand" property to No.

5. Limit the number of rows the combo box returns. Displaying 50 records is much faster than filling a combo box with 1000 or more records. There are many strategies to accomplish limiting the number of records that a combo box is filled with when it loads. One option is to make the combo box query dependent on, or filtered according to criteria of a text field on the same form. This is easy to do if you are using an alphabetical list such as a list of Customers.

Place a condition in the criteria field of the query which fills the combo which looks at another text control on the form for the first (and subsequent letters entered into the text box) such as:

      Like [Form]![txtAlpha] & "*" 
(Note you don't need the formal form name in the criteria, Access will look to the current form for the field,) then in the OnChange event of the field txtAlpha enter code in an event proceedure similar to "MyCombo.Requery". Another advantage to this technique is that since the query returns no rows when the form is opened, the form loads faster as well.

6. If the Bound Column of the Combo Box is a numeric column of the query, don't hide this column. (Access 2)

Back to the Tips List

Use the "Tag" Property of a Form or Report Control to manage Form or Report functionality.
The "Tag" property of a form or report control is not used by Access directly, and can be used by the programmer to manage how a form or report functions in many ways. As an example, you may have a Form which under certain circumstances you want to show one set of multiple fields and under other circumstances show another set of multiple fields. You could write an event procedure to hide or show the controls, similar to the following list, in the AfterUpdate event of an option group:
If Opt=1 then

        Me!Control1.visible=True

        Me!Control2.visible=True

        Me!Control3.visible=False

        Me!Control4.visible=False

        etc.

Else

        Me!Contol1.visible=False

        Me!Control2.visible=True

        Me!Control3.visible=False

        Me!Control4.visible=False

        etc.

End If

The former type of code requires you to change the code if you add more controls to those you want to hide or show. Alternately you could type in either "Opt1" or "Opt2" to the "Tag" property of each control you want to change whether to hide or show according to the option by the user. Then you could write your code, entering it to the "After Update" event of an Option Group, to loop through the controls on the form and make the changes to the identified controls automatically. The code might look like this:

Dim Frm as Form

Dim I as integer

Set Frm = Me



If Opt=1 then

    For I = 0 to Frm.count -1

       If Instr(Frm( I ).tag, "Opt1")>0 then Frm( I ).visible = True

       If Instr(Frm( I ).tag, "Opt2")>0 then Frm( I ).visible = False

    Next I

Else

    For I = 0 to Frm.count -1

        If Instr(Frm( I ).tag, "Opt1")>0 then Frm( I ).visible = False 

        If Instr(Frm( I ).tag, "Opt2")>0 then Frm( I ).visible = True

   Next I

End If

This later set of code also allows you to add or delete controls from the those that are visible or hidden without changing your code, simply by adding one of the options to a controls "Tag" property. (Note, we check whether the Tag property has "Opt1" or "Opt2" in the string contained in the Tag property rather than checking whether the Tag is equal to either "Opt1" or "Opt2" because we may place more than one action tags in the Tag property of the same control.)

Back to the Tips List

How to Create your own custom Progress/Status Meter/Bar Chart
You may find a need for creating a "Progress Meter" on your Form, separate from the status bar progress meter which is callable by using the sysCmd function in Access. A Progress meter can be created by using two label controls, directly on top of one another in the form. Here's how you do it:

1. Create a label control, with a sunken style, (we'll call this lblbase) and make it's backcolor transparent, and forecolor to black.

2. Create a label control, of 0.00 width, identical in height to the baselbl (we'll call this lblmeter); align the lblmeter exactly with the left edge of the lblbase control, and send it to the back (i.e. behind the lblbase.) Make its edges transparent.

3. To update the progress meter place the following code in the form's module and send it the current and total amounts to measure progress as appropriate (you can use a timer event or place the calls in looping code etc.):

Sub updatemtr (currentamt, totalamount)

'    This function changes the color based on progress.

'    You set the back color of lblmeter to be a single color if desired.



    Dim MtrPercent as Single

    MtrPercent = currentamt/totalamount

    Me!lblbase.Caption = Int(MtrPercent*100) & "%"

    Me!lblmeter.Width = CLng(Me!lblbase.Width * MtrPercent)

    Select Case MtrPercent

        Case Is < .33

            Me!lblmeter.BackColor = 255       'red

        Case Is < .66

            Me!lblmeter.BackColor = 65535   'yellow

        Case Else

            Me!lblmeter.BackColor = 65280   'green

    End Select

End Sub

4. Note this same tip can also be used to create a simple horizontal bar chart on a report, and is especially handy if you don't know what the total amount might be before you run the report. (The total amount would be required to be known a priori if you were to use MS Graph to create the chart, because you would have to set the scale when creating the chart; with this method, you don't have to know the total amount before the report is run.)

Back to the Tips List

How to control Tabbing and "Enter" key movement in and out of a sub-Form and a Main Form.
When a user presses the tab key or the enter key when they are in the last field on a main form, where the next field is a sub form, this will automatically result in the cursor moving to the first control in the subform. However, if the user then holds down the Shift key and presses the tab key, (to move backward in the form,) rather than re-entering the main form, the cursor will either move to the last field on the sub form (if on the first record of the sub form,) or to the previous record in the sub form. Similarly, if the user is on the last control in the sub form and presses the enter key, they will be taken to the next record in the sub form, rather than to next control in the main form. This behavior can be modified to progress directly between the main form and current record of the sub form by using event procedures in the "On Key Down" event of the sub-Form's first and last controls. To do this use the code below. (Note the "Parent" property of the sub form refers to the main form):

1. On the Declarations page of the Form's module enter the following lines:

Const Key_Tab = &H9

Const Key_Return = &HD

Const SHIFT_MASK = 1
2. In the "On Key Down" event of the first control on the subform create an event procedure and enter the following code:
ShiftDown = (Shift And SHIFT_MASK) > 0



   If KeyCode = Key_Tab Then

        If ShiftDown Then 

            Me.Parent!SomeControl.SetFocus  

            KeyCode=0

       End If

   End If

3. In the "On Key Down" event of the last control on the subform create an event procedure and enter the following code:

  ShiftDown = (Shift And SHIFT_MASK) > 0



    If KeyCode = Key_Tab Then

        If ShiftDown = 0 Then 

            KeyCode = 0

            Me.Parent!SomeControl.SetFocus

        End if

    ElseIf KeyCode = Key_Return Then

           KeyCode = 0

           Me.Parent!SomeControl.SetFocus

    Else Exit Sub

    End If

To stop the user from re-entering the main form, without moving to the next record, simply remove the lines of code which set the focus on a control of the Main Form.

Access 95 and 97: If you need to trap key actions and movement in Access 95 and 97, especially if it involves multiple controls and/or subforms (e.g. subforms on a tab control,) you can save time and coding by creating a single function for the form by setting the KeyPreview property of the form to "Yes", and writing a single function in the OnKeyDown event of the form. You can use a select case routine to test the CurrentControl.name and set your form movement from there.

There is a simple sample db of how to implement this located in the ACG Free File Library which you can download. The file's called "Inandout" ACG Free Files

Back to the Tips List

How to Fill a List Box with a List of Reports
If you would like to present your users with a list of reports available to print from a specific form you can use the Collections Object of Access to create a string to fill a list box for the user to select from. Here's an example of how to do it ( an " _ " means the line continues on the next line show here):

1. Select a prefix to add to the name of the reports you want to display in the List Box. (In the example below, "cmgt_ " is the prefix used.)

2. Place the following code to the "On Open" event of the form you are using:

Sub Form_Open (Cancel As Integer)

On Error GoTo Err_OF

    Dim db As Database

    Dim I %

    Dim contr As Container

    Dim strReportList$

    Dim StrReportName$

    Dim Length%

   

   Set db = CurrentDB()

   Set contr = db.Containers("Reports")



    strReportList = ""

    For I = 0 To contr.Documents.Count - 1

       StrReportName = contr.Documents(I).Name

       If Left(StrReportName, 5) = "cmgt_" Then

            If strReportList <> "" Then strReportList = strReportList & ";"

            Length = Len(StrReportName)

            StrReportName = Right(StrReportName, (Length - 5))

            strReportList = strReportList & StrReportName

       End If

   Next I



  Me!RptLstBox.RowSource = strReportList



Exit_OF:

     Exit Sub

Err_OF:

    MsgBox Err & " " & Error, , "Form Open"

    Resume Exit_OF

End Sub

Back to the Tips List

How To Tell if the User has moved to a New Record on a Form
You may need to execute some procedure or update, such as creating a record number, when a user moves to a new record. In Access 95 and 97 you can check the NewRecord property in the OnCurrent event of the form.

Unfortunately, in Access 2, there is no New Record property of a form. But its rather easy to create your own function to test whether the current record is a new record, using a record's bookmark property. To do this add the following code to your form's module:

Create a new function in the form module named something like "TestNewRec() as Integer". The code for TestNewRec would look like:

Function TestNewRec () As Integer



    Dim RetVal As Variant   'Bookmarks are best declared as variants

    On Error Resume Next

    Const NO_Current_Record = 3021



    RetVal = Me.Bookmark



    If Err = NO_Current_Record Then

        TestNewRec = True

    Else

        TestNewRec = False

    End If



End Function

To use the function, place a call in the OnCurrent event of the form, testing whether to execute your conditional code which runs when there is a new record, using an If..Then..Else condition, such as

If TestNewRec() Then

    DoSomething

Else

    DoNothing

End If

This function utilizes the fact that each record has a bookmark property, but that the book mark property is not established until the record is saved. Therefore on a new record an error occurs which triggers the running of your procedure for new records. If you need to use this function with numerous forms, then add the function to a general module, adding the parameter "TestForm" as Form to the function call, as in TestNewRec(TestForm as Form) as Integer. Then change the Me.Bookmark in the function to TestForm.Bookmark, and pass a reference to the form when the function is called as in If TestNewRec(Me) Then…

Back to the Tips List

Do Your Forms Which Have Filter Options Take "Forever" to Close in Access 97?
Access 97 introduces many new features including the option to filter by form. Many developers also add options and methods to set filters on a form to search for or limit the form's records to a selected set.

In addition to its new functionality, Access 97 introduces a new form property called "Filter", which is set each time you set a filter on the form. The problem is that when you close the form with a filter remaining set on the form, that filter is set as the form's "Filter" property, and Access, sensing the form has changed from when it was first opend, saves the form on closing it, which appears to the user to slow down performance.

To rectify this problem is fairly simple since most developer's add a "Close" or "Exit" button to their form. In the code which you use in the event procedure for this button, make sure that you include the "acSaveNo" option to the close command as in:

DoCmd.Close  acForm, "frm_MyForm", acSaveNo

Your form will close much faster and preserve the feel of fast performance.

Note: If you are using the control wizards to add the proper code to your command buttons in Access 97, you may need to change the code. Many of Access 97's control wizards place old style Access 2 and 95 "DoCmd.DoMenuItem" code in your event procedures (as withnessed by the A_MENU_VER70 tag,) and this can be updated to the code above.

Back to the Tips List

General Form Loading Speed Issues
In all versions of Access the issue of form loading speed is critical to the sense of performance of the application. Here's some general rules for maximizing form loading speed, and minimizing load time:

1.) Minimize the number of controls and fields on your form. This may seem self evident, but its important. Use separate popup forms for various sections of data or for fields only used by certain individuals. If you must use a form with many fields, then create a front end record selector form to pick the record you want to view or edit and then open the form using a filter or Where clause to pick the proper record.

2.) Minimize the number of combo boxes and list boxes on the form, especially if they are based on another table; each combo or listbox will require Access to load one or more table pointers into memory for each table in the underlying queries. Convert combos and list boxes where possible to being based on value lists rather than queries. (See also the combo box topic on this page.)

3.) Don't display OLE fields or Memo fields when the form loads. Either place these fields on another page, or cover them with a box control, and in the OnClick event of the box control, set the box's visible property to false to display the memo or OLE field. Non-visible OLE and Memo fields aren't read from the db until they are made visible.

4.) Minimize the number of indexes in the underlying table, and be smart about indexes: Don't index both the Customer name and its ID number, since one is directly related to the other.

5.) If you split your database between data on the server and application on the local workstation and use look up tables for combo boxes for items such as state abbreviations, keep those tables (where the data doesn't change) in the local application database, rather then on the server. This will lower network traffic and look up will be faster from the local disk.

6.) For lookup forms such as Address books and forms used by individuals who only inquire about data rather than enter it, set the form to be read only, this is often overlooked and is a real boost, since it eliminates record lock requirements. (See the help file for the various versions of Access as to how to do this.)

7.) Move code behind the form to general database modules, and combine where feasible this code into one module so that all the code is loaded at the same time and ready for use. Better yet include in this code in a module has other code which is executed when the application starts (such as reattaching tables,) so that the form code is loaded when the db loads, rather than when the form loads. (If you make use of the Me object in your form module, when you move the code to a general module simply place a parameter in the Sub or Function call specifying a form object (e.g. Sub frmOrders_Order_AfterUpdate(Frm as Form), and replace Me in your code with "Frm". Call the function from the orders AfterUpdate event by specifying "Call frmOrders_Order_AfterUpdate(Me)").

8.) If you use a query for the form record source or for combo or list boxes, used saved queries rather than SQL statements for the record source. Saved Access queries are "pre-optimized" by the JET query optimizer, whereas SQL statements must be optimized at run time.

9.) See also the query section on query optimization.

10.) Dependent on your computer's amount of memory especially if it is less than 32 meg on a Windows 95 machine you may want to increase the default size of your virtual memory swap file. To do this in the computer's settings for virtual memory, (System properties, performance, virtual memory) increase your minimum virtual memory settings to at least 1.5-2 times the amount of base memory your computer has installed, if the minimum virtual memory setting is currently at zero. This will result in a large swap file being created at startup that is already available to handle Access' needs for virtual memory when it loads and opens forms. (Each time Windows needs more virtual memory it causes two disk writes once to mark the space and once to write to it when swapping. You can cut this time in half by having a large swapfile already available.)

Back to the Tips List

How to Have Just One Delete, Save, or Record Movement Button Control Both Your Main Form and Your SubForm(s)
When you place a sub form on a main form which contains command buttons for deleting a record or moving between records, normally each time you click the command button for the desired action the action is only carried out on the main form itself, even if you are editing a record in the sub form, because the sub form looses the focus, and the main form regains the focus when you click on the command button. But you can create a "smart button" which will take the desired action on the sub form when you are in the sub form by following a few simple steps.

1.) Place your subform on a separate page, or use the new tab control in Access 97 and place your subform on a different tab. (You can use the Tab ActiveX controls in Access 2 and Access 95.)

2.) In the OnClick event procedure for the command button check which tab you're on, or which page of the form you are on and if its one which contains the subform, set the focus to the subform before carrying out the desired action. Here's same code for a two tab form with a subform on tab 2 for a delete record button using Access 97 syntax:

Dim tabObj as Control

Dim pge as integer



Set tabObj = Me!TabControlName

pge = tabObj.value



Select Case pge



Case 0

     DoCmd.RunCommand acCmdDeleteRecord

Case 1    'Tab 2

    Me!mysubform.SetFocus

    DoCmd.RunCommand acCmdDeleteRecord

End Select

This tactic event works with nested sub forms.....

Keep in mind, that for Save Record Buttons, you don't have to do anything to check if you are on the subform because when the subform looses the focus by moving to click the save button on the main form, the subform's record is automatically saved.

Back to the Tips Index

Creating Your Own Automatic Spin Control Using Visual Basic.
Spin Controls on forms are useful to allow a user to increment up or down values in a field (e.g. number of products ordered etc.) Active X controls are available to provide this functionality, but rather than including another control and file in your distribution set for the application, its really rather simple to create a spin control in Visual Basic. Here's how to do it:

1. First you need a spinner bit map to add to the form for the up and down arrows. An easy place to "steal" these from is the print dialog. Open the print dialog by selecting print from the File Menu. Then while the dialog is displayed, hold down the alt button and press "Print Screen" from the keyboard.

2. Open Paint from the accessories and select "Edit/Paste". Then while the print dialog image is displayed, use the cut tool to select the number of copies spinner arrows (don't select the text box that the copies are shown in, just the arrows) and then while those are selected, on the Edit menu select "Copy To" and save the image of the spinners as a 16 colour bitmap.

3. On an Access Form add a new unbound object control, and then on the insert menu select "Insert Object" click insert from file and select the spinner bitmap file. Size the control to fit the spinners.

4. Next to the spinners image add a text box (we'll call it txtNumber) and size it to match the spinners, set its default property to 1.

5. Place one label control over each spinner (up and down) and set their back property to transparent and their special effect to flat. This will in essence make them invisible (we'll call these lblUp and lblDown).

6. Now for the code: On the declarations page of the form's module add the following (See Special Notes at the bottom of the page for Access 2 implementation, this is for Access 95 and 97.):

Private Declare Function GetAsyncKeyState Lib "user32" _

         (ByVal vKey As Long) As Integer

Private Declare Function GetSystemMetrics Lib "user32" _

        (ByVal nIndex As Long) As Long



Const VK_LBUTTON = &H1

Const VK_RBUTTON = &H2

Const SM_SWAPBUTTON = 23

Dim BUTTONTOLOOKFOR As Integer

7. In the Form's OnOpen or OnLoad Event add the following code. This is to check whether the user has the mouse set to left handed use (i.e. the buttons are swapped.) We'll use this setting later when we monitor the spinners.

If GetSystemMetrics(SM_SWAPBUTTON) = 1 Then

        BUTTONTOLOOKFOR = VK_RBUTTON

    Else

        BUTTONTOLOOKFOR = VK_LBUTTON

    End If

8. In the OnClick Event of lblUp add:
Me!txtNumber = Me!txtNumber + 1
In the OnClick Event of lblDown add:
If Me!txtNumber >= 2 Then Me!txtNumber = Me!txtNumber - 1
9. In the OnMouseDown Event of both the spinner labels (lbl Up and lblDown) you will add a variant of the following code, which regulates the up and down value of the spinner textbox. This code is for lblUp, it has comments showing the changes necessary for lblDown.
Private Sub lblUp_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)

On Error Resume Next

    Dim Tick1!, dwKeyState%

    Dim Tick2!, Tick3!

   

       'If the textbox has the focus we need to use the Text property

       'to start with any number the user has entered.

    Me!txtNumber = Me!txtNumber.Text  

       'Check holding down the "left" button

    If Button = 1 Then

        Tick1 = Timer

             'We first use a tick timer to set a delay so that if the 

              'user just clicks, then we won't spin automatically

             'Increase or decrease the .3 below to adjust sensitivity

        Do Until Tick2 > Tick1 + 0.3

            Tick2 = Timer

            DoEvents

                'check the keystate of the "left" button, if its 0 then

                'the user has released and we exit the sub, otherwise add 1

            dwKeyState = GetAsyncKeyState(BUTTONTOLOOKFOR)

            If dwKeyState = 0 Then Exit Sub

        Loop

            'For lblDown use the code from the OnClick Event for that control.

        Me!txtNumber = Me!txtNumber + 1

           'Now here's our spin for additional increments in value beyond the first

Add1:

            'This sets the speed for the increase or decrease in value

            'Lower numbers spin faster, since this is just a delay.

        Do Until Tick3 > Tick2 + 0.2

            Tick3 = Timer

            DoEvents

        Loop

             'check release of mouse button if its up we exit gracefully

        dwKeyState = GetAsyncKeyState(BUTTONTOLOOKFOR)

             '(This is not equal to 0, it may not show up well in your browser)

        If dwKeyState <> 0 Then  

               'Adjust for lblDown as before

            Me!txtNumber = Me!txtNumber + 1

            Tick2 = Timer

            DoEvents

                'Just keep looping until the mouse is released

            GoTo Add1

        End If

    End If

 End Sub

Access 2 Notes: Access 2 doesn't support the key word "Private" to hide api calls so its important to alias your api calls to avoid conflicts between forms, general modules and/or code libraries. You also need to change the dll called to the 16 bit version. Here's some revised declares:
Declare Function api_GetAsyncKeyState lib "user" Alias _

         "GetAsyncKeyState" (ByVal vKey As Integer) As Integer

Declare Function api_GetSystemMetrics Lib "user" Alias_

          "GetSystemMetrics" (ByVal nIndex As Integer) As Integer

That's all there is to it.

Back to the Tips Index

How to use a variable to reference a table/query field or form control rather than an explicit reference
When working with arrays of fields or controls your application may need to reference a query field, table field or form control using a variable, rather than an explict reference to the particular field name. Access provides a documented feature to allow you to do this, but it is often overlooked, resulting in long VBA code.

For example, you may have a form which contains controls named "RATE1", "RATE2", "RATE3", etc. which you need to fill from a recordset. If your VBA code needs to reference all three controls to set a value the standard way to do this would be to code: "Me!Rate1 = MyRecordset!Rate" etc for all the fields, which can amount to a whole lot of wasted coding.

Using the parentheses and quotation marks (" ") instead of using the ! operator allows you to use variables in a reference to a control or field. Rather than using the explict reference to the control, change your syntax of the code to something like the following:

For i = 1 to 3

    Me("Rate" & i) = MyRecordset!Rate

    MyRecordSet.MoveNext

Next i

You can do the same thing with referencing fields in table or queries if you are working from any array of values.

Back to the Tips List