Discuss "Look up client" in the Microsoft Access forum.

I am looking for help with how to allow the EU to type the first couple of letters of the clients name. As a result in the combo box they are typing in, a selection of clients will be available based on the input. Once the EU selects the client, the form will display the record for that client. Any suggestions on how to structure this? I am not sure how to approach it. This will only be used to search for records. It will not be used to update any tables.

Alan

First of all, start with a blank form in design view. Next, create an unbound textbox, name it txtsearch in the properties/other part of the textbox. Create a button, cancel the wizard. So now you should have an unbound textbox and a button with no action. Save the form as form1.

Now, create a query based on the client list you will be searching. Include all fields, and on the field name that you will be searching put this in the criteria:
Like forms!form1.txtsearch &*
Save the query as query1.

Open Form1 in design view again, use the subform wizard, create a box for the subform, and choose query1 as the source, name the subform "SUBFORM1". Right click the button you made with no action, go to properties, then events, then on click event. Right click and choose "Build", or hit the button with the three dots. For the vba, you simply put, me.subform1.requery. You now have a functional search form. Save Form1.

Open subform1 in design view, and for each of the fields, on dblclick event, put this in the vba:
DoCmd.OpenForm "Customers"
Save Subform1

create a query for the customers form you will be using, and in the criteria for the name, put this:
Like [forms]![form1].[SubForm1].[Form]![CustID] & "*"
Save the query as custquery1

Create a form called "Customers" using custquery1 as the source using the wizard.

That's it.

Here is a sample.

Attached Files

ChooseClient.zip (18.5 KB, 11 views)

Thanks Ben;
I will give this a try. However, I could not open the file you created. My machine said it was outside my intranet or on a network. It wasn't. It was on my desktop. Just wouldn't open. Not sure what caused that.
Alan

That's weird.... I created it at work and downloaded it at home (2 separate ISP's) It works at both places...... ummmm, try downloading the zip again, and extract it to a new folder..... If you have trouble setting it up per my instructions, let me know. I'm happy to help.

Not sure what happened. I opened at the office and it worked just fine. Couldn't open at home. Magic. Will tackle it tomorrow.
Thanks
Alan