Multiple column combo box problem

[Chalkie42]

I have an unbound combo box on a form that is used to select a record by surname. The complete record is then displayed in various controls on the form. I realised after completing the project that there are multiple instances of the same surname i.e. 5 Smiths. When any of the Smiths are selected from the combo box it is only ever the first instance that is displayed and the user is then required to use the scroll wheel to move to the correct one.

I amended the combo to display two columns so that the first name could be shown also but this hasn't fixed the problem with record selection.

The after Update event on the combo box is:

Private Sub Combo54_AfterUpdate()
' Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[Surname] = '" & Me![Combo54] & "'"
Me.Bookmark = rs.Bookmark
End Sub

Is there a way in which I can select the exact record I need from the combo box?

All help appreciated.

Chalkie.

 

[KenHigg]

The tie between what is selected in the combo box and the form should be the form recordsource primary key.

 

[DCrake]

Simple Software Solutions

In your rowsource for your combobox you should have something along the lines

Select CustID, Trim(([Surname]) & ", " & Trim([Forename)) As FullName From TblCustomers Order By Trim(([Surname]) & ", " & Trim([Forename)) ;

This will give you two columns, in your column count set this to 2, in your columnwidths set this to 0cm,3cm

Set the bound column to 1

When you open your form you will only see the customers name sorted alphabetically.

When you select a customer from the control on the AfterUpdate Event

Find the record that matches the control.
Dim rs As Object

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustID] = " & Me![Combo54]
Me.Bookmark = rs.Bookmark

End Sub


Note:
FindFirst only allows you to use one parameter, whereby Seek allows many and you can also define which indexes to use. For more help on Seek type in Rs.Seek then highlight the word Seek and press F1

CodeMaster: