Results 1 to 2 of 2
2005-01-24, 12:20 #1
- 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()However if an entry in the field 'Defendant' is identical, then the record selected will not be displayed.
' 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
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.
2005-01-24, 12:30 #2
- Join Date
- Mar 2002
- Thanked 30 Times in 30 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>