Access displaying first record when using combo box to switch records

Question

I have an Access database that uses linked tables residing on a SQL Server. For one of these tables I created a simple form showing several fields of the underlying table.

I want to switch records using a combo box, so I added one using the Combo Box Wizard, where I selected the option "Find a record on my form based on the value I selected in my combo box".

The combo box works, but I noticed that whenever a new value is selected, Access will briefly return to the first record before displaying the selected record. I can verify this by both noticing a screen flicker (e.g., bound fields briefly display data from the first record), as well as profiling the calls to SQL Server, where I can see one query for the first record, and another query for the selected record.

This problem does not occur if I use the next/previous Navigation Buttons at the bottom of the form.

How can I avoid this unnecessary query?

Answer 1

What using the Wizard did was create a Macro behind the Event of your combobox click. It probably looks something like .. , , First, ="[SomeField] = " & Str(Nz(Screen.ActiveControl,0)) .. What you can actually do, instead of using the Macro, is follow something like this

If we look at what that Macro does (or what arguments it accepts), it is a little more clear on why you may be experiencing that behavior.

The first argument is Object Type then Object Name, then Record, then Offset.

1. Object Type - The type of object that contains the record you want to make current. Click Table, Query, Form, Server View, Stored Procedure, or Function in the Object Type box in the Action Arguments section of the Macro Builder pane. Leave this argument blank to select the active object. <- Yours would be left blank because you're referring to the combobox on the form
2. Object Name - The name of the object that contains the record you want to make the current record. The Object Name box shows all objects in the current database of the type selected by the Object Type argument. If you leave the Object Type argument blank, leave this argument blank also. <- Because the first was left blank
3. Record - The record to make the current record. Click Previous, Next, First, Last, Go To, or New in the Record box. The default is Next. <- This defaulted to First for me and possibly for you too, probably why you were seeing this 'odd' behavior
4. Offset - An integer or expression that evaluates to an integer. An expression must be preceded by an equal sign (=). This argument specifies the record to make the current record. You can use the Offset argument in two ways: When the Record argument is Next or Previous, Microsoft Office Access 2007 moves the number of records forward or backward specified in the Offset argument. When the Record argument is Go To, Access moves to the record with the number equal to the Offset argument. The record number is shown in the record number box at the bottom of the window. Note If you use the First, Last, or New setting for the Record argument, Access ignores the Offset argument. If you enter an Offset argument that is too large, Access displays an error message. You can't enter negative numbers for the Offset argument.

I wish I could give you more details, but I have not used Access in conjunction with SQL Server, so if that plays into account I would not have any insight.