More than one BoundColumn?? 1

[RageMatrix]

Hey all.
I've got a combo box which is linked to a query that returns the last and first names of a customer. The BoundColumn is current set to last name. However, if I have two customers with the same last name, the combo box only updates the form to the first record found with that last name. Is there a way to have more than one bound column in the combo box, so that I can get the record I'm looking for, rather than just the first record with that last name?
I'm currently using rs.FindFirst to do this. Is there a better way? I created the combo box functionality using the built-in wizard.

Thank you for any help!

Regards,

Jon.

 

[Rolliee]

YOu have access to all the columns in a combo box. They are available through

me.combo0.column(i) where 'i' is the ith column.

rollie@bwsys.net

 

[BillPower]

Hi RageMatrix,

Does the table with the names in have a unique identifier like an AutoNumber or CustomerID. If no, I suggest that you create one.

Make the ID Field the 3rd Field in your combo's query.
Make the Column Count 3 in the combo.
Add ;0 to Column Widths in the combo so that ID doesn't show in the list. (You should now have 3 widths)

In your Form's Recordsource query/SQL, you will also have to add the ID field.

On your form, add a hidden text box.
Name = txtID
Control Source = ID.

In the AfterUpdate event of your combo paste this:

Dim rs As DAO.Recordset
Set rs = Me.Recordset.Clone
rs.FindFirst "[ID] = " & Me!YourComboName.Column(2)
Me.Bookmark = rs.Bookmark

Change YourComboName to your combo name.

Basically, now it won't matter how many Smiths, Jones, Patels you have, you will be searching on their ID.

Hope this makes sense.

Bill

 

[RageMatrix]

Thanks very much for the help, guys! In the end, I kind of used a combination approach.

I set the RowSource of the combo box to the three fields of the query I was interested in, LastName, FirstName and CustomerID

SELECT qryListCustomers.LastName, qryListCustomers.FirstName, qryListCustomers.CustomerID FROM qryListCustomers;

Then I set the BoundColumn to column 3, which was the CustomerID.

Lastly, I used the following code in the After_Update event sub.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[CustomerID] = " & Str(Nz(Me![Combo18], 0))
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

Now, all I have to do is link the current record to the value in the combo box so that the normal record nav controls show the LastName in the combo box when I switch records by other means rather than the combo box.

Thanks all!

Jon.