How to Fill a List Box with a List of Reports

It may be useful to present a user with a list of reports available to print from a specific form in a list box on that form. Here's a method and code that illustrates how to limit a list of reports presented on a form to a specific sub set of reports in your database: 1. Select a prefix to add to the name of the reports you want to display in on the specific form in the List Box. (In the example below, "cmgt_ " is the prefix used.) 2. Set the target list box to use a value list as its Row Source Type 3. Place code like the following to the "On Open" event of the form you are using; note that the code is version independent and will work in Access 97 and above: Sub Form_Open (Cancel As Integer) On Error GoTo ErrHandler Dim App As Object Dim CurProject As Object Dim CollReports As Object Dim objRpt As Object Dim strRptName As String, strReportList as String, i As Integer If SysCmd(acSysCmdAccessVer) < 9 Then i = 0 Set CurProject = CurrentDb Set CollReports = CurProject.Containers("Reports") For i = 0 To CollReports.Documents.Count - 1 strRptName = CollReports.Documents(i).Name If instr(strRptName, "_cmgt")>0 Then strReportList = strReportList & strRptName & ";" End If Next i Else Set App = Application Set CurProject = App.CurrentProject Set CollReports = CurProject.AllReports For Each objRpt In CollReports strRptName = objRpt.Name If instr(strRptName, "_cmgt")>0 Then strReportList = strReportList & strRptName & ";" End If Next End If 'knock of the last ";" strReportList = Left(strReportList, Len(strReportList)-1) Me!RptLstBox.RowSource = strReportList ExitProc: Exit Sub ErrHandler: MsgBox Err & " " & Error, , "Form Open" Resume ExitProc End Sub Check out the "Other Reports" database in our free files area to see how extend this concept to load a list of reports into a command bar combo box and preview that report when the command bar combo is selected.

Back to the Tips List

How To Move to another Tab on a Form

On of the most often asked questions about  is how to move the focus from one tab to another.  The easiest way to do this is to simply set the focus to the first control on the target tab as in Me!SomeControl.SetFocus.   If you want to make your form keyboard friendly (i.e. so the user can simply hit the tab key and move between tabs on the form you can use the On Key Down event of the last control on a tab to set the focus to the next tab.  Here's the code: ShiftDown = (Shift And SHIFT_MASK) > 0     If KeyCode = Key_Tab or KeyCode = Key_ReturnThen         If ShiftDown = 0 Then             KeyCode = 0             Me!SomeContol.SetFocus         End if     Else Exit Sub     End If

Back to the Tips List

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

Access 97 introduced many new features including the option to filter by form which has carried forward into Access 2000, 2002 and beyond. 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 Access 97 and later, forms contain a property called "Filter" and "Filter On" (which applies the 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 opened, 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 "SaveNo" option to the close command as in: DoCmd.Close acForm, Me.Name, acSaveNo Your form will close much faster and preserve the feel of fast performance. Note: If you are using the control wizards in Access 97 to add the proper code to your command buttons 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 witnessed 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.)

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.

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!, Tick2!, Tick3!, dwKeyState as Long '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 key state 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 That's all there is to it.

How to use a variable to reference a table/query field or form control rather than an explicit reference (Almost like a control array)

When working with arrays of fields or controls, an application may need to reference a query field, table field or form control using a variable for part of the field name, rather than using an explicit 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 record set. 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 explicit 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.

How to Easily Change Form Control Names to Comply with Naming Conventions

If you use naming conventions in your code, you may get tired of laboriously naming individual controls every time you create a new form.  Since Access, when using the Form Wizard or when simply dropping fields on a form manually, automatically names the controls to match the Control Source field name (the text box for the "LastName" field as an example will always be named "LastName"), it's easy to save yourself a bit of time by changing the control names with VBA, rather than changing each control name individually. To do so: Create a general module and add the following function to it: Public Function NamingConventions(objForm As Form) As String     Dim c As Control     Dim strNConv As String     Dim intCount As Integer         With objForm     For Each c In objForm.Controls         Select Case c.ControlType             Case acTextBox                 strNConv = "txt"             Case acComboBox                 strNConv = "cbo"             Case acListBox                 strNConv = "lst"             Case acCommandButton                 strNConv = "cmd"             Case acImage                 strNConv = "img"             Case acOptionGroup                 strNConv = "opt"             Case 108                 strNConv = "ole"             Case acCheckBox                 strNConv = "chk"             Case acToggleButton                 strNConv = "tgl"             Case 123                 strNConv = "tab"         End Select             If Not c.Name Like strNConv & "*" Then             c.Name = strNConv & c.Name             intCount = intCount + 1         End If     Next     End With     NamingConventions = "Renamed " & intCount & " controls to comply with " & _         "naming conventions." End Function Then simply open your form in design mode and call this function from the VBA immediate window, supplying the form name as input. This tip kindly provided by Evan D. Carter

Back to the Tips List