I love to start things by saying "Is there a way to", however over the years I have learned that the answer to that questions is almost always invariably yes. So I will start it this way.
Background:
I have a form that lists all my contacts. My contacts are sometimes suppliers, I have a sub-form "subfrmSupplierContacts" on my contact details form "frmContactDetails". I want to be able to search my "frmContactsList" for example for "dan", and have it return to me all the contacts who have the name dan in either first, last, or company name.(I have this allready)
However, I would like it to search my "subfrmSupplierContacts" first, last and company name also, BUT, only return to me the Single Contact Entry.
Here is what I have tried so far:
I can create a query where the one to many fields are joined, however I have 31 Supplier Contacts for "XX Company" so therefore I have 31 entries with the same Company Name all the time. I only want "xx Company" to show 1 time.
I contemplated doing an ADODB.Recordset to open that "qryContactsAllData" and have it search that and create a very complex Filter Criteria with specific numbers, however I know there has to be a better way to do this. Maybe using some combination of Totals (ie group by)
Does anyone have any suggestions as to how I should create a search that will search for for the info I put in a search box on all the one to many tables, but only return to me the ones in the results windows.
Thanks for any time spent trying to assist me with this,
Dan
Bookmarks