Global FIND/REPLACE for Data

[BonCourage]

In Access 2000, I would like to search for data on other tabs that contain subforms linked to their respective tables. To clarify, my main form (on tab 1) has customer company data. My second tab has a subform linked to a different table (contacts of the company on tab 1). Since I often remember peoples names and not their company name, I would like to do a FIND from the main form based on their name, which unfortunately resides in another table than that of the main form. Any ideas?

 

[purpleflash]

Why not try creating a combo box based on a query which joins the company table to the customer table, ordered by Customer name then company. You can then enter a customer name and return the associated company to the combo box. Then use the resultant selection to requery the main form and you should then get the company info you require along with the relevant contacts in your subform!

good luck!

 

[BonCourage]

I am just getting into queries and SQL. Am I looking at a difficult query statement? Can you show me what it would look like or give me more specific steps?

 

[tempclerk]

Use your ComboBox Wizard. Select the Contact name and CompanyID fields from your Contacts table, and have the box bound to the CompanyID field. The wizard should then ask what you want to do with this value, and you should choose to find a record based on it. Not hard at all.

 

[BonCourage]

tempclerk-
Are you saying that I utilize the combobox wizard anytime I need to locate a contact or are you saying that I setup a "Find" combobox to do my search? Right now my Company names are displayed in a textbox and the Contacts in a datasheet subform.

 

[purpleflash]

Use the combo box wizard to create an unbound combo box on your form which will return the required company when you pick the contact name. Then use the returned value (company) to requery the main form.

So base your main form on a query that uses the contents of the combo box as a parameter. then programatically requery the form on the on click event or exit event.

 

[BonCourage]

Thanks. I will try this, hopefully before I leave on vacation tomorrow morning. If not, I hope you can check this thread in a week or so to see if I made it work. Wish me luck.