Results 1 to 2 of 2
  1. #1
    5 Star Lounger
    Join Date
    Jan 2004
    Norfolk, England
    Thanked 0 Times in 0 Posts

    Combo box behaviour is odd (2000)

    I have a database that has a form. On this form, there is a combo box that looks up the records in a specific field. The coding on the After Update event is:
    <hr>Private Sub Combo171_AfterUpdate()
    ' Find the record that matches the control.
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[Defendant] = '" & Me![Combo171] & "'"
    Me.Bookmark = rs.Bookmark
    End Sub<hr>
    However if an entry in the field 'Defendant' is identical, then the record selected will not be displayed.
    So for instance, 3 records relating to SMITH, John. Select the third entry, only the first is displayed.
    Each entry has a unique ID (Autonumber) as a primary key.

    Any ideas?

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Thanked 31 Times in 31 Posts

    Re: Combo box behaviour is odd (2000)

    The code uses FindFirst to find - as the name of the method indicates - the first record with a matching value in the Defendant field. It does not consider the unique ID at all. So the behaviour you describe is to be expected. If you want to match on the unique Id, you must modify both the combo box and the code:
    - Set the Column Count of the combo box to 2.
    - Include the unique ID in the Row Source of the combo box, as the first field.
    - Change the line with FindFirst in the code to

    <code>rs.FindFirst "[UniqueID] = '" & Me![Combo171] & "'"</code>

    where UniqueID is the name of ... you guessed it. This assumes that the ID field is a text field. If it is a number field, omit the extra quotes:

    <code>rs.FindFirst "[UniqueID] = " & Me![Combo171]</code>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts