(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
![]() ![]() | 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) |
![]() ![]() | 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.) |
![]() ![]() | 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.) |
![]() ![]() | 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 = 12. 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 |
![]() ![]() | 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 |
![]() ![]() | 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 |
![]() | 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. |
![]() ![]() | 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 SelectThis 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 If8. In the OnClick Event of lblUp add: Me!txtNumber = Me!txtNumber + 1In the OnClick Event of lblDown add: If Me!txtNumber >= 2 Then Me!txtNumber = Me!txtNumber - 19. 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 SubAccess 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 IntegerThat'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 |
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 iYou can do the same thing with referencing fields in table or queries if you are working from any array of values.
|