Provided by Allen Browne.
Warning: In Access 97 or earlier, this tip can trigger the Bookmark Bug.
If you receive an error in Access 2000 or later, see Solving problems with References.
It is possible to use an unbound combo box in the header of a form as a means of record navigation. The idea is to select an entry from the drop-down list, and have Access take you to that record.
Assume you have a table called "tblCustomers" with the following structure:
CustomerID AutoNumber (indexed as Primary Key). Company Text ContactPerson Text
A form displays data from this table in Single Form view. Add a combo box to the form's header, with the following properties:
Name cboMoveTo Control Source [leave this blank] Row Source Type Table/Query Row Source tblCustomers Column Count 3 Column Widths 0.6 in; 1.2 in; 1.2 in Bound Column 1 List Width 3.2 in Limit to List Yes
Now attach this code to the AfterUpdate property of the Combo Box:
Sub CboMoveTo_AfterUpdate () Dim rs As DAO.Recordset If Not IsNull(Me.cboMoveTo) Then 'Save before move. If Me.Dirty Then Me.Dirty = False End If 'Search in the clone set. Set rs = Me.RecordsetClone rs.FindFirst "[CustomerID] = " & Me.cboMoveTo If rs.NoMatch Then MsgBox "Not found: filtered?" Else 'Display the found record in the form. Me.Bookmark = rs.Bookmark End If Set rs = Nothing End If End Sub
The steps this procedure takes are:
Note: If CustomerID is a Text type field in your table, you need extra quotes, i.e.:
rs.FindFirst "[CustomerID] = """ & Me.cboMoveTo & """"
For an explanation, see Quotation marks within quotes.
If you want to create a search form, not merely a navigation combo, see the Search Criteria database.
|Home||Index of tips||Top|