Performance IV - fast forms

The purpose of these performance posts is to suggest ways you can try to improve the performance of your Access applications. Some of the tips are just a brain-dump from the author, others have been collated from various Microsoft, FMS and other third-party sources.

Note that some of the tips contradict each other. This is because an optimization in one part of your application may cause a bottleneck in another part. For example, to make a form with subforms open and scroll faster, you may add indexes to each of the fields used to link the form with the subform. This will indeed make the form faster, but the added indexes will make operations like adding and deleting records slower.

Also, there is no such thing as perfect optimization advice. Some of the tips given here may make things run faster on your specific system, some may introduce new performance problems. The point is that optimization is not an exact science. You should evaluate each tip as it applies to your specific application running on your specific set-up. To help in this regard, each tip is rated ■, ■■ or ■■■, with ■■■ being the most advantageous in the author’s experience.

Move startup code onto your startup form... ■

If you use a Startup form instead on an AutoExec macro, place the VBA code needed for that form in the form's module instead of a standard module. Since Access has to load your Startup form, it will automatically load that form's module, which is generally faster than loading a standard module. This technique gives your application the appearance that it is loading faster.

...but also minimize startup form code ■

Minimize the amount of code in your startup form. You may want to defer certain operations, such as opening data access objects in code, or checking objects to a later time. Again, this gives the impression that your application is loading faster.

Avoid using ActiveX controls on startup form ■

ActiveX controls can take longer to load than other controls and will subsequently slow down the load time of your application.

Avoid overlapping controls ■

It takes Access more time to render and draw controls that overlap each other than it does non-overlapping controls.

Think carefully about graphics sparingly ■

Use bitmap and other graphic objects sparingly (or not at all, if you ask me) as they can take more time to load and display than other controls. If you must use graphics, use the Image control instead of unbound object frames to display bitmaps. The Image Control is a faster and more efficient control type for graphic images. Also, if you have to use graphics use bitmaps with the lowest possible color depth. Consider black and white bitmaps where appropriate.

Access Help topic: Add a picture or object

Close unused forms ■

Close forms that aren't being used. Every form that is open consumes memory that could be used by other parts of your applications.

Open forms hidden ■

Consider opening your application's most commonly used forms when your application starts. Set their Visible properties to False, and then make the Visible as needed. This front-loads some performance hits to the application load event, making forms load faster when needed.

Use the DataEntry property of a form ■

If a form's record source contains a large number of records, and the form is primarily used to add new records, set the DataEntry property of the form to Yes. This precludes Access from having to retrieve existing records when the form loads.

Access Help topic: DataEntry property

Don't sort a form's recordset ■

Avoid sorting records in a form's underlying record source unless a particular presentation order is absolutely necessary for the form. This will make the form load faster.

Base forms on queries and minimize fields returned ■

Consider basing forms and subforms on queries rather than tables. By doing this, you can use the query to restrict the number of fields returned, making the form load faster.

Use lightweight forms ■■

Consider replacing VBA in a form's module with calls to standard modules, or with hyperlink objects. Then set the form's HasModule property to False. This turns the form into a 'lightweight' form, making it load faster.

Access Help topic: HasModule property

Index fields used to link subforms to a form ■

Try indexing all fields in the subform that are linked to the main form. Also index all fields in the subform that are used for criteria.

Set editing properties on subforms ■

Set the subform's AllowEdits, AllowAdditions, and AllowDeletions properties to No if the records in the subform aren't going to be edited. Or set the RecordsetType property of the subform to Snapshot.

Access Help topics: AllowEdits property, AllowAdditions property, AllowDeletions property, RecordsetType property

Reduce the number of fields in listbox and combobox rowsources ■

In the RowSource property of listbox and combobox controls, include only the fields that are necessary.

Index listbox and combobox fields ■

Index both the first field displayed in a combobox or listbox, and the bound field.

Set AutoExpand on comboboxes to No ■

Set the AutoExpand property of comboboxes to No if you don't need the "fill in as you type" feature.

Access Help topic: AutoExpand property

The first displayed field of an AutoExpand combobox should be text ■

In a combobox that has the AutoExpand property set to Yes, the first displayed field should be of type Text rather than Number data type. In order to find matches, Access needs to convert numeric values to text. If the data type is Text, this conversion can be skipped.

Optimize bound comboboxes ■

If the bound field in a lookup combobox is not the displayed field, don't use expressions for the bound field or the displayed field, don't use restrictions (the WHERE clause) in the rowsource, and use single-table row sources wherever possible.

Ensure linked data for combobox and listbox controls is local ■

If a the data that fills a listbox or combobox does not change often, and that data comes from a linked table, consider moving that data's table into the database. This can be a huge performance boost, especially if the linked table is located on a network drive.

Minimize the number of controls ■

Minimize the number of controls on your form. Loading controls is one of the biggest performance hits when loading a form.

Group controls on multiple pages ■

Consider grouping controls on multiple pages. When the form loads, prepare only the controls on the form's first page. Defer operations on other page's controls, such as setting the record source until the user moves to that page. This makes the form load faster.

Close forms that contain unbound OLE objects ■

Close forms that contain unbound OLE Objects when they are not in use. When you activate unbound OLE objects, the memory used in that operation is not released until the form is closed.

Convert subforms to listbox or combobox controls ■

Where possible, convert subforms to listbox or combobox controls. It is far quicker to load a control than it is to load an additional form as a subform.

Move form code to a standard module ■

You can reduce a form's load time by moving its code from the form module to a standard module. When the form loads, the form's module doesn't need to be loaded. Of course, the standard module needs to be loaded at some point, but once a standard module is loaded, it stays in memory until you close the database.

Access Help topic: About modules

Avoid unnecessary property assignments ■

Set only the properties that absolutely need to be set. Properties assignments can be relatively expensive in terms of performance. Review your form's startup code to ensure that you are not setting any form or control properties that don't need to be set.

Use the Requery method instead of the Requery action ■

Use the Requery method instead of the Requery action. The method is significantly faster than the action.

Access Help topic: Requery method

Give visual feedback ■

Give the user some visual feedback during long operations. Consider using status meters to display a task's progress. At a minimum, use the Hourglass cursor along with a status message. This won't make your application any faster, but it be perceived to be doing something - this counters perceived slowness!

Use native tab controls ■

If you have databases from old versions of Access that used OLE custom controls for tabbed dividers, replace them with the native tab control found in Access 97 and above - it loads and runs faster.

Access Help topic: Tab control

Keep forms lightweight with hyperlinks ■

Hyperlinks in label controls make it easy to open another Access object. Instead of placing command buttons on your forms to do common operations, investigate the possibility of using a label control with Hyperlink properties. This approach eliminates the need for a command button, and its associated event code.

Access Help topic: About hyperlinks

Split forms into multiple pages ■

Consider using multi-page forms, separated by the page-break character. This allows you to present only the controls needed, and can reduce form-load time. For example, if your form has 10 combobox controls that take along time to fill, split the form into multiple pages using the PageBreak control. Then, pick the 5 combobox controls the user is most likely to use and place them on the first page. Place the remaining controls on the second page. Load time for the form should be substantially reduced, especially if the queries filling those combo box controls are complex.

Access Help topic: Pagebreak control

Choose control types wisely ■

Different control types have different performance "weights" associated with them. When choosing controls for a form, keep the following relative weights in mind:

• Rectangle: weight of 1
• Line: 1
• Page Break: 1
• Label: 3
• Command Button: 5
• Option Button: 5
• Toggle Button: 5
• Checkbox: 5
• Tab Page: 5
• Textbox: 10
• Option Group: 10
• Image: 20
• Tab Control: 25
• Listbox: 40
• Combobox: 40
• Custom Control (OCX): 45
• SubForm: 50
• Object Frame: 50
• Bound Object Frame: 50

Adding an "ALL" option to a combo-box

We're all familiar with using a combo-box (also known as a drop-down list) to select data from a pre-defined or data-driven list of values. It's also quite common to use a combo-box to select a data value which can then be used in some context, for example selecting a value which is then used to filter a form or report. When you're using a combo-box in this way, it can often be good to add an “ALL” option at the top of the list, to show that you want everything... but how do you do this?

Fortunately it's a piece of cake, but there are different methods depending on what the row-source of the combo-box is. Broadly speaking, there are four generic types of combo-box row-source:

1. The Value List - the simplest row-source, with data values typed in and separated by semi-colons, so a row-source of North;South;East;West will give the drop-down list four simple values.
2. The Simple Bind - if a combo is on a form that's bound to a table, then the row-source of that combo can just be a table field name; as long as that field has a lookup set up at a table level, that lookup will cascade to the form and populate the combo-box drop-down list.
3. The Simple SELECT - a combo-box can have a simple SQL statement as the row-source, e.g. SELECT RegionID FROM tblRegion ORDER BY RegionID; would produce a drop-down list of Region names in alphabetical order.
4. The Complex SELECT - often a combo-box will use a SQL statement that returns more than one field, typically a numeric ID field and one or more narrative text fields. The ID field can then be given a column width of zero in the combo-box, allowing the user to see and highlight an intuitive and meaningful text value but to actually be selecting a numeric ID value. Such a row-source might look something like SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;

Okay, so we're happy with the four basic types of combo-box row-source as they might appear on a form control. Before we consider how to change each type to add an "ALL" option, let me first explain the general principle of what we're going to do. We're starting out with a combo-box from which we can select a value and then use that value in some way. For example, you might have a button on your form that opens a report but filters the data therein based on the value selected in your combo-box - in this case, the OnClick event of that button might include some VBA along the lines of DoCmd.OpenReport "rptSalesman", , , "RegionID = " & Me.cboRegion where Me.cboRegion represents the value of the combo-box. But to add an “ALL” option, we're going to have to change our thinking because what we're actually going to be adding is a “*” wildcard character option. So, in our subsequent use of the combo-box value, instead of using an Equals operator (=) we're going to have to think about using a LIKE operator. In other words, the previous example will have to change to something like DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE " & Me.cboRegion

That's easy enough, isn't it? The important thing to remember is this works because the SQL WHERE constraint of “LIKE '*'” returns everything that isn't Null.

Okay, enough theory - here are some examples of how to change each of the four combo-box row-source types to add that “ALL” option.

Row-source	Without “ALL”	With “ALL”
Value List	North;South;East;West	*;ALL;North;North;South;South;East;East;West;West
Simple Bind	The cascaded table field's lookup, e.g. SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;	A UNION statement, e.g. SELECT "*" AS RegionID, "ALL" AS RegionName, 0 AS SortOrder FROM tblRegion UNION SELECT RegionID, RegionName, 1 FROM tblRegion ORDER BY SortOrder, RegionName;
Simple SELECT	A SELECT statement with one output, e.g. SELECT RegionID FROM tblRegion ORDER BY RegionID;
Complex SELECT	A SELECT statement with more than one output, e.g. SELECT RegionID, RegionName FROM tblRegion ORDER BY RegionName;

Okay, so you probably have a few questions now, such as:

Q. Why does my combo-box now show an asterisk or a number, instead of my nice intuitive narrative text?

A. Unless you tell it otherwise, the combo-box will still display the same number of columns, with the same widths, as before you made the changes outlined above. To restore the nice formatting, you need to do all the following:

• increase the combo-box's Column Count property - in the example above, the Column Count property would be 3 (2 for the Value List example), as the SQL statement returns three fields;
• add a column width of 0cm to the start of the Column Widths property (so the */ID field is hidden) - in the example above the Column Widths property would be 0cm;2.54cm;0cm (0cm;2.54cm for the Value List example) as you wouldn't want to see the third column either;
• ensure that the Bound Column property is set to 1.

Q. What's that “SortOrder” value all about?

A. You want your “ALL” to appear at the top of your drop-down list, right? But what happens when sorting alphabetically doesn't put “ALL” at the top? For example, you might have a list of countries, in which you'd find that Albania came above “ALL” in your combo-box. By adding a simple numeric flag called SortOrder, this can be avoided: SortOrder has a value of 0 for your “ALL” option and 1 for everything else. SortOrder can then be used as the first item in your row-source SQL statement's ORDER BY clause, to ensure that “ALL” comes first - subsequent items can then be added to the ORDER BY clause to sort the remaining data items however you like.

Q. Hows does it work again?

A. Time to revisit the example. Let's say you have a combo-box called cboRegion on a form; previously it had a simple SELECT style row-source. You want to use this control to restrict the data included in a report - the report is opened by clicking a button on the same form. Previously, the button's OnClick event would have contained something like this:

DoCmd.OpenReport "rptSalesman", , , "RegionID = " & Me.cboRegion 

If you'd selected a RegionID of 1 from your combo-box, this would have parsed as

DoCmd.OpenReport "rptSalesman", , , "RegionID = 1" 

Now you have something like:

DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE '" & Me.cboRegion & "'" 

If you select “ALL” from your combo-box, this will now parse as

DoCmd.OpenReport "rptSalesman", , , "RegionID LIKE '*'" 

And, as we learnt earlier, "LIKE '*'” returns everything, and so gives you your “ALL” option. Hooray!

Q. I still don't get it - can't I download an example database?

A. Yes you can (I'm good to you, aren't I?). There's no difference in the code at all but, due to popular demand, I've created Access 97, 2000 and 2002 versions of a database to illustrate the example we've talked about. They have two tables (tblRegion and tblSalesman), one form (misleadingly entitled frmSalesman) and one report (rptSalesman). Take a look at the properties of cboRegion and the OnClick event of cmdRpt in frmSalesman - you can surely work the rest out for yourself!

Free download samples: Access 2002 and above | Access 2000 | Access 97 - you'll need unzipping software for these downloads, such as WinZip or 7-Zip.

Creating a “save record?” prompt

By default, any changes made to a bound record in a form are automatically saved whenever you move away from that record - this could be by moving to another record, by creating a new record or by closing the form. This is generally fine, but what if an unplanned change has been made in error? Wouldn't it be nice to get a “record changed - save changes?” type prompt when you move away from a record?

Fortunately this is easily achieved through use of the form's BeforeUpdate event - this is triggered by saving changes to a bound record (that's why it called “before update”, after all), so it's triggered when you move away from a modified record. Of course, it's also triggered if you do an explicit Save operation, but that's okay, it then just gives you a chance to confirm that you want to save, which is generally a good thing too. Anyway, here's the code.

Private Sub Form_BeforeUpdate(Cancel As Integer)
    Select Case MsgBox("Do you want to save changes to this record?", vbYesNoCancel + vbQuestion)
    Case vbYes 'Go-ahead and save
        'Don't have to do anything
    Case vbNo 'Undo all changes, close without saving
        Me.Undo
    Case Else 'Cancel change, continue editing
        Cancel = True
    End Select
End Sub

Creating a scrolling marquee field

Creating a marquee field (you know: a text box or label that scrolls, common on some websites) in an Access Data Page is easy, because there's a control for it in your toolbox. Not so when you're working with forms though. I was surprised to find a number of people searching this site for help on this subject, so here's an easy way of adding a scrolling marquee to your forms.

Let's do a simple example first - a Label control will act as a marquee for us. Here's what you do:

1. Create a form and place a Label control on it - name it lblMarquee and fill it with a nice long text string (for demonstration purposes, size the label so that you can't see all the text on screen when you view the form).
2. Set the form's TimerInterval property to 200 - this property is measured in milliseconds, so a value of 200 equates to one fifth of a second.
3. Create an event procedure for the form's OnTimer event that seems to scroll the label by simply taking off the first character of the string and appending it to the end, like so:

Private Sub Form_Timer()
    lblMarquee.Caption=Mid(lblMarquee.Caption, 2) & Left(lblMarquee.Caption, 1)
End Sub

4. Check out your form - you have a scrolling marquee field!

Okay, but that's a pretty basic example. It would be more useful if the marquee stopped scrolling when the mouse pointer hovered over it. This is a tiny bit more complicated... but still very easy. In fact, I bet I can show you how to do it in eight easy steps...

1. Start the same way as in the simple example: create a form and place a Label control on it called lblMarquee, then fill the label with a long text string.
2. This time though, leave the form's TimerInterval property set to 0.
3. Create an event procedure for the form's OnTimer event that seems to scroll the label - this uses exactly the same code fragment as the simple example, above.
4. Whilst you're in the VBA code editor window, declare a constant called intTimer with a value of 200 - place this declaration in the General Declarations section at the top of your code. This is so that if you subsequently want to change the speed of the scroll you only have to change the timer interval value in one place.
5. Create an event procedure for the Form's OnOpen event that sets the form's TimerInterval property to intTimer (to start the marquee scrolling).
6. Create an event procedure for the lblMarquee's OnMouseMove event that sets the form's TimerInterval property to 0 (to stop the marquee when the mouse hovers over it).
7. Create event procedures for the OnMouseMove events of the Form and its Detail section that set the form's TimerInterval property back to intTimer (these restart the marquee when the mouse moves away from the label).
8. Check out your form - you now have a scrolling marquee that pauses when the mouse hovers over it!

Here's the VBA for this form in full:

Private Const intTimer As Integer=200 'Use a constant for the timer interval

Private Sub Form_Open(Cancel As Integer)
    Me.TimerInterval=intTimer 'Sets initial scrolling speed
End Sub

Private Sub Form_Timer()
    lblMarquee=Mid(lblMarquee.Caption, 2) & Left(lblMarquee.Caption, 1) 'Does the scrolling
End Sub

Private Sub lblMarquee_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.TimerInterval=0 'A zero value stops the Timer event, and hence the scrolling
End Sub

Private Sub Detail_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.TimerInterval=intTimer 'Restarts the marquee
End Sub

Private Sub Form_MouseMove(Button As Integer, Shift As Integer, X As Single, Y As Single)
    Me.TimerInterval=intTimer 'Restarts the marquee
End Sub

Clearly this isn't very sophisticated but you get the idea of how this can be made to work. Try experimenting with values other than 200 to see what value gives the smoothest scrolling (you might find it varies depending on font face and size). Also, I believe this code could be adapted to make other control types scroll too (a text box being the obvious alternative).

Sorting a list-box by clicking its column headings

So you've got a list-box on a form with a number of columns. Wouldn't it be nice if you could click on the column headings to dynamically sort the list-box's contents by the relevant column? Well there's probably a sharp way to do this but I haven't found it yet. Instead, let me show you the somewhat clunky method I use; it lets you sort in ascending order by left-clicking a column heading and descending order by right-clicking the heading or, if you prefer, to toggle sort order with repeated left-clicks.

How does it work? Well, it's based around the list-box's MouseDown event and uses the position of the mouse pointer (its X and Y co-ordinates) over the list-box to decide whether it's over a column heading or not. See, I told you it was clunky! The important thing to remember here is that these co-ordinates are measured in twips - to help you with your conversions, there are 1440 twips in an inch or 567 twips in a centimetre. Also, the co-ordinate 0,0 is the top-left corner of the list-box.

In the example shown, I've got a list-box called lisSummary with a Row Source of "SELECT Surname, Forename, Department FROM tblStaff;" and Column Heads set to Yes. For simplicity, each column is set to be 3cm (that's 1701 twips, lest we forget) wide.

Private Sub lisSummary_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Y <= 225 Then 'This is the right height for column-headings in 8pt MS Sans Serif
    Dim strOrd As String, strSQL As String
    If Button = 1 Then strOrd = " Asc;" Else strOrd = " Desc;" 'Left-click sorts ASC, right-click sorts DESC
    strSQL = "SELECT Surname, Forename, Department FROM tblStaff ORDER BY "
    Select Case X 'Compile the OrderBy clause
    Case 0 To 1700
        strSQL = strSQL & "Surname " & strOrd
    Case 1701 To 3401
        strSQL = strSQL & "Forename " & strOrd
    Case Else
        strSQL = strSQL & "Department " & strOrd
    End Select
    lisSummary.RowSource = strSQL 'Apply the new recordsource
    lisSummary.Requery
End If
End Sub

You could modify this code to toggle the sort order on a column when you repeatedly left-click on it, if you'd prefer. Here's how:

Private Sub lisSummary_MouseDown(Button As Integer, Shift As Integer, X As Single, Y As Single)
If Button = 1 Then 'Check the left mouse button has been clicked, not the right
    If Y <= 225 Then 'This is the right height for column-headings in 8pt MS Sans Serif
        Dim strOrd As String, strSQL As String
        If Right$(lisSummary.RowSource, 5) = " Asc;" Then
            strOrd = " Desc;" 'Sort DESC if currently sorted ASC
        Else
            strOrd = " Asc;" 'Sort ASC if currently sorted DESC or for the first sort
        End If
        strSQL = "SELECT Surname, Forename, Department FROM tblStaff ORDER BY "
        Select Case X 'Compile the OrderBy clause
        Case 0 To 1700
            strSQL = strSQL & "Surname " & strOrd
        Case 1701 To 3401
            strSQL = strSQL & "Forename " & strOrd
        Case Else
            strSQL = strSQL & "Department " & strOrd
        End Select
        lisSummary.RowSource = strSQL 'Apply the new recordsource
        lisSummary.Requery
    End If
End If
End Sub

Deleting a record tidily

When you delete a record from a form, by default the next available record is displayed when the deletion is complete. No problems there, you might think, but what if the record you're deleting is the last in the recordset? A blank record is displayed. Some might argue that this is just an untidy annoyance that can be lived with, but I'd rather not. Also, if you're using Me.NewRecord in the form's Current event to do something in the event of a new record, you'll find that "something" happening, which isn't very convenient! The following example shows the module for a form that moves you back to the previous record if you delete the last record in the form's recordset:

Option Compare Database
Option Explicit
Dim booDel As Boolean

Private Sub Form_Current()
    If booDel Then
        booDel=not booDel
        DoCmd.GoToRecord , , acPrevious
    End If
End Sub

Private Sub Form_Delete(Cancel As Integer)
    booDel=(Me.CurrentRecord=Me.RecordsetClone.RecordCount)
End Sub

Conditional formatting by control type

Ever wanted to apply formatting to all the controls of a form at the same time? Easy! All the examples shown are running from the form's Open event, but could just as easily be applied to the Current event if you want different formatting for different records. The first example simply disables all the controls by setting their Enabled properties to False:

Private Sub Form_Open(Cancel As Integer)
On Error Resume Next 'In case some controls without the relevant property are considered
Dim ctl As Control
For Each ctl In Me.Controls
    ctl.Enabled = False
Next ctl
End Sub

This is fine but has an ugly method of handling those controls that don't support the property you want to modify, i.e. it justs skips the error. Wouldn't it be nicer to just reformat relevant controls? The ControlType property lets you do this, as in the following example which disables all controls except Labels:

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
    'ControlType constants you can use
    'acLabel - Label
    'acRectangle - Rectangle
    'acLine - Line
    'acImage - Image
    'acCommandButton - Command button
    'acOptionButton - Option button
    'acCheckBox - Check box
    'acOptionGroup - Option group
    'acBoundObjectFrame - Bound object frame
    'acTextBox - Text box
    'acListBox - List box
    'acComboBox - Combo box
    'acSubform - SubForm / SubReport
    'acObjectFrame - Unbound object frame or chart
    'acPageBreak - Page break
    'acPage - Page
    'acCustomControl - ActiveX (custom) control
    'acToggleButton - Toggle button
    'acTabCtl - Tab
    ctl.Enabled = (ctl.ControlType <> acLabel)
Next ctl
End Sub

Which is all well and good except what happens if you want to format an eclectic selection of controls of different types? Well you could use the control's Name property to uniquely identify each one and scroll through them all but how laborious is that if you have lots of controls to do this for? Plus if you make changes to the form, adding or removing controls, you have to change your code too. I prefer to make use of each control's Tag property, as in the following example which disables all controls with a Tag property of "X":

Private Sub Form_Open(Cancel As Integer)
Dim ctl As Control
For Each ctl In Me.Controls
    ctl.Enabled = (ctl.Tag <> "X")
Next ctl

Determining a form's state

There are occasions when you will want to know, for example, if a given form is open. This can be quite easily done using SysCmd, a technique I quite often use to automatically close form A when closing B. In the example that follows SysCmd is used to check whether the frmSale form is open and, if so, closes it.

Private Sub Form_Close()
    'There are four states you can check for:
    'acObjStateOpen - open
    'acObjStateNew - new
    'acObjStateDirty - changed but not saved
    '0 (zero) - closed or non-existent
    If SysCmd(acSysCmdGetObjectState, acForm, "frmSale") = acObjStateOpen Then DoCmd.Close acForm, "frmSale"
End Sub