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

Paradox

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.

Tab	Property	Value
Data	Control source	blank - the field is "unbound"
Data	Row Source Type	Table/Query
Data	Row Source	SELECT DISTINCTROW [Addresses].[AddressID], [Addresses].[FirstName], [Addresses].[LastName] FROM [Addresses];
Data	Bound Column	1
Format	Column Count	3
Format	Column Widths	0";1";1"
Event	After 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

Private Sub AddRecord_UIButton_Click()
Dim tempStr As String
On Error GoTo Err_AddRecord_UIButton_Click

  tempStr = InputBox("Enter [modify this]")
  If tempStr <> "" Then          ' Do nothing if Cancel is pressed
    With RecordsetClone
        .AddNew                  ' Creates the new record
        !FieldName = tempStr     ' Set the field values
        .Update                  ' Post (save) the new record

        Bookmark = .LastModified ' Display the new record
    End With
      
      ' Add this item to the pick list (comboBox)
    Organization.Requery
  End If

Exit_AddRecord_UIButton_Click:
    Exit Sub

Err_AddRecord_UIButton_Click:
    MsgBox err.Description
    Resume Exit_AddRecord_UIButton_Click
    
End Sub

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

  .Bookmark = .LastModified
should be
  Bookmark = .LastModified
or
  form.Bookmark = .LastModified

Move To a Record

Move to the first record on a form

DoCmd.GoToRecord acFirst

Delphi 5.0

Visual Basic 6.0

Java

Author: Robert Clemenzi - clemenzi@cpcug.org