Databases - Picking A Record

How to use a comboBox to select and display records.

(General use of comboBoxes to select a value for a field is covered under Lookup Tables.)

Paradox | MS Access | Delphi | Visual Basic | Java


MS Access 97

Locate record example based on the wizard

When you place a comboBox on a form, and Control Wizards is enabled, you will be presented with 3 choices. The following information is based on using this wizard to design a combo box (Combo12) which finds a record based on a value selected from the list.
TabProperty Value
DataControl source blank - the field is "unbound"
DataRow Source Type Table/Query
DataRow Source SELECT DISTINCTROW [Addresses].[AddressID], [Addresses].[FirstName], [Addresses].[LastName] FROM [Addresses];
DataBound Column 1
FormatColumn Count 3
FormatColumn Widths 0";1";1"
EventAfter Update The following code is called every time a new option is selected
Sub Combo12_AfterUpdate()
    ' Find the record that matches the control.
    Me.RecordsetClone.FindFirst _
         "[AddressID] = " & Me![Combo12]
    Me.Bookmark = Me.RecordsetClone.Bookmark
End Sub
FirstName and LastName had to be in an index for the wizard to work. I even made them "unique" though I'm not sure that it is required. Based on the sample code, it is fairly obvious how to modify it to use non-indexed, and non-unique fields.

When placed in a form, and the down arrow is clicked, the FirstName and LastName are both displayed. When an option is selected, only the FirstName is shown in the field. Notice that only the first field (AddressID) is bound to the field and that its value is never displayed. (The zero in Column Widths hides the value.)

Add a Record

When a comboBox is used to select records, then another method must be provided to enter new records. You can use a button with the following code The built-in data entry dialog box (InputBox) looks pretty lame, but it works.

The example in the MS Access AddNew Method help contains an error

Move To a Record

Move to the first record on a form

Delphi 5.0

Visual Basic 6.0


Author: Robert Clemenzi -
URL: http:// / user / clemenzi / technical / Databases / Picking_A_Record.html