subform comboboxes/Textboxes

[terryvanduzee]

Hello
I have a subform that contains a textbox for the user to type a customer's name. I want it to use a "Like" option in order to list all customers who fit the criteria.

Ex: User types "Bob John"
Results should show Bob Johns, Bob Johnson, Bob Johnston etc.
Like * & Text10 & *

The other fields on the subform would populate with the address, phone, etc of the customer the user selects from a combobox (or listbox).

Based on the resulting selection for the customer, all purchases made by that customer would show up in datasheet view in another subform.

I believe it would be a waste of time for the user to select a customer name from the customer table because there could end up being thousands of customers. Also, customers with the same name (uniquely identified in the customer table by 1st purchase date and customer name).

Is there any way to do this?

Thank you


Terry

[NTC]

combobox is way to do what you want

[terryvanduzee]

A combobox would be most convenient design wise, but not practical for users because there could be thousands of customers.

I have found a way to have the user type part of a name and list all names in the table that fit the criteria.

I put an unbound textbox on the form, then using the Afterupdate, I put some code in in order to search the table. Then assigned a combobox's rowsource to be the results of my select statement. This all works except 1 thing.

Here is the code for it:

Private Sub Text22_AfterUpdate()
Dim strSQL As String
Dim searchval
Me.cbo_name.Value = ""

Me.Text22.SetFocus
searchval = Me.Text22.Value
 strSQL = "SELECT [cust_name] from [tbl_Customers] where cust_name Like '*" & searchval & "*';"
 Me!cbo_name.RowSource = strSQL
End Sub

The other fields on the form need to be populated with the address, phone etc of the customer selected in the cbo. But once the search for the customers is done, the link disappears and it does not show the address etc for the selected customer.

Any suggestions on how to fix this?

Thank you
Terry