Performing Searches Form using two separate tables

[NGFLNG1]

Hi All,



I have a form derived from two different tables. The "contacts" table is the main table and the "Appointments" is the secondary table. It is used to track the appointments of employees.

I have added search functionality via a command button with an event procedure using a "Private sub-routine" that I got from the forum. The event works fine for the "contacts" table search. However, I cannot get it to work for the secondary table. I have modified the 'If Else' routines to match the field I want to search on--but it does not work.

My form displays one employee/contact at a time with all of their appointments linked to them.

Question: Is it possible to search the form for information from both tables using the same search button in the header section of the form? Right now, it does not do anything when I search for a particular appointment (the child form).

Ex: I want to search for an appointment from a txt field using "certifying Officer" as the terms to search. I enter this information into the search box, hit the search button, and the employee with an appointment that matches those terms should come up, along with all of their other appointments, if any.

Thank you.

[June7]

This is a form/subform arrangement?

What you want will not be simple.

Couldn't there be multiple contacts having appointments with the same certifying officer?

[NGFLNG1]

Yes. More than one employee may possibly have the same appointment. Each appointment has a PK (of course), description (short text), and an Appt Number (short txt).

[June7]

User selects certifying officer, code applies filter criteria to main form where contacts have that certifying officer.

A query would look like:

SELECT * From Contacts WHERE ContactID IN (SELECT ContactID FROM Appointments WHERE OfficerID=Forms!formmain![tbxCriteria]);

Translating this to a dynamic search on a form that allows for any field and parameters in the subquery would be complicated. I've never tried this.