Searching by combo box

[TheEnigma]

Hi there,

I have a problem where I have a combo box set up on a form which I use as a search field to bring up various invoice numbers.

My problem is that in the search field, there are at times duplicate invoice numbers, but the invoice numbers have different information against them. Both show up in the search field, but even when I select the next record with the same invoice number, the record won't update to reflect the details of that invoice number. I tried adding the ID to display in the combo box as well, but it still doesn't change the information displayed.

If anyone has an answer to this it would be much appreciated!

 

[PHV]

Which code are you using to display the information ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheEnigma]

SELECT [VendorInvoice_tbl].[CustomerInvoice], [VendorInvoice_tbl].[id] FROM [VendorInvoice_tbl] ORDER BY [VendorInvoice_tbl].[CustomerInvoice], DESC;

 

[PHV]

Which code are you using to display the details information ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheEnigma]

Hi, I think I am missing something here? The sql statement is in the data/row source properties of the combo box. I use that combo box to search and display the details relating to that invoice number.

Other than that there really isn't any other code relating to searching and displaying that data. It is just a standard form with that combo box.

Does this answer your question?

 

[PHV]

to search and display the details relating to that invoice number
How are you doing that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheEnigma]

I will either type the invoice number in this field and hit enter, or type the invoice number in and select the record from drop down menu from the combo box to display the invoice numbers and then select the correct invoice number from the drop down menu. This will then bring up the details on the form.

 

[PHV]

This will then bring up the details on the form
Which code is doing that ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheEnigma]

This code.

SELECT [VendorInvoice_tbl].[CustomerInvoice], [VendorInvoice_tbl].[id] FROM [VendorInvoice_tbl] ORDER BY [VendorInvoice_tbl].[CustomerInvoice], DESC;

 

[PHV]

Isn't this "code" the RowSource property of the ComboBox ?
Have you some code in any event procedure of the combo.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheEnigma]

I originally created the combo box using the combo box wizard in the MS Access forms. I adjusted the code slightly so that it would sort that field numerically rather than the order they were entered into the database.

 

[TheEnigma]

Ok there is this code in the event procedure, but the combo box wizard must have created it as I didn't. It is in the after update field.

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[VendorInvoices] = '" & Me![Combo106] & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

 

[PHV]

OK, you've got it.
Replace this:
rs.FindFirst "[VendorInvoices] = '" & Me![Combo106] & "'"
By this:
rs.FindFirst "[VendorInvoices] = '" & Me![Combo106] & "'" _
& " AND [name of ID field]=" & ME!Combo106.Column(1)


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheEnigma]

Thanks! As you can probably tell I am not very affluent in my knowledge of code.

I take it that [name of ID field]should simply be [id] as that is what I am using in the database as my primary key and also as the extra field in this combo box. So the following code is what I am now using:

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

Set rs = Me.Recordset.Clone
rs.FindFirst "[VendorInvoices] = '" & Me![Combo106] & "'" _
& " AND [id]=" & Me!Combo106.Column(1)
If Not rs.EOF Then Me.Bookmark = rs.Bookmark

End Sub

I am getting a syntax error 3077 missing operator in expression. I take it I am doing something else wrong!?

 

[PHV]

Is ID correctly displayed in the combo as 2nd column ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[TheEnigma]

It is displayed as the second column.