calling the wrong field name in combo box

[bfamo]

I'm using the following code in a combo box to search my customers:

Private Sub CtrlCustomer_AfterUpdate()
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    rs.FindFirst "[CustomerName] = '" & Replace(Me![CtrlSearch], "'", "''") & "'"
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

My problem is that the search function is not able to tell the difference between customers with the same name but different address. This is because I'm calling "CustomerName" instead of CustomerID.

The form is based on a query "qryCustomerSearch" which returns CustomerID, CutomerName and Address. The combo box has the row source set to:

SELECT QryCustomerSearch.CustomerName, QryCustomerSearch.Address FROM QryCustomerSearch ORDER BY [CustomerName];

I have tried to call CustomerID instead of CustomerName in the code, but this only give me a error message. Any suggestions on how to fix this?

thanks!

 

[Remou]

The combbox should be:

SELECT QryCustomerSearch.CustomerID, QryCustomerSearch.CustomerName, QryCustomerSearch.Address FROM QryCustomerSearch ORDER BY [CustomerName];

The code should be:

Private Sub CtrlCustomer_AfterUpdate()
    Dim rs As Object

    Set rs = Me.Recordset.Clone
    'Assuming CustomerID is numeric
    rs.FindFirst "[CustomerID] = " & Me!CtrlSearch
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

You may need to adjust Column Widths and Column Count.

 

[PHV]

Use this as RowSource:
SELECT CustomerID, CustomerName, Address FROM QryCustomerSearch ORDER BY 2

Set the width of the 1st column to 0 and set the BoundColumn=1

And now, your search:
rs.FindFirst "CustomerID=" & Me![CtrlSearch]
If Not rs.NoMatch Then Me.Bookmark = rs.Bookmark

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bfamo]

thanks a lot guys,

When I used to start typing in the combo box, it would start making suggestions as i typed along. This does not work anymore, obviously because I'm calling CustomerID and not CustomerName. Is there a way search CustomerName again?


Thanks!

 

[PHV]

Did you set the 1st column's width to 0 ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[bfamo]

woops... that did it

thanks yet again for greatly appreciated answers!

 

[bfamo]

I'm back.

The DB I'm working in is relicated, with my replica member beeing the design master. Although the new changes in the search form worked just fine in the design master, some of the other users has encountered some problems. After they have synchronized, the search combo box shows [blue]address[/blue] as the first column, and [blue]CustomerName[/blue] is not visible at all.

I have checked the combo box's properties from both the replica and the design master, and they are identical.
Why does two identical forms look different? strange...