I've been tasked with taking two unrealted tables (Providers and Patients, no common field) and allow users, on one form (or form/subform) to search multiple fields in each table, select a record from each table based on those searches, and "somehow" link the two as a new record in a third table. So they have to bring up a search form, choose a doctor from the Providers table (flexible search by either Provider ID or Provider name) and then choose a patient from the Patients table (by either first name, last name, dob, etc.) and then create a record in a new table, based on a record containing the identified provider and patient that were identified in the two searches. HELP!
-
-
"Some how" linking the two in a third table is easy enough. Both the provider and patient table should have a primary key. If they don't, create one. In the third table you simply capture the two primary keys.
The search functionality is easy enough as well and there's probably a number of ways to go about it. Here's an approximation of how I'd go about it without knowing the details of what you've got.
On a single form:
1. create unbound text boxes to capture the search words.
2. Create two buttons, one for the provider search and one for the patient search.
3. Create two combo boxes that will populate with the data that fits each search.
4. Create a button that populates the third table.
5. Create two Subs that dynamically build SQL queries to populate the combo boxes.
6. Create a Sub that populates the third table.
So your form will have as many unbound text boxes as searchable fields, 3 Buttons, and two combo boxes. You'll need to set the bound columns of the combo boxes to whatever columns you have the primary key in (it doesn't have to be visible to the user).
The general gist is that the user populates as many of the search fields for the provider as they want and then presses a button. This button calls a Sub that dynamically builds an SQL query that populates the combo box with all providers that fit the searched criteria. The user then picks a single provider from the combo box.
The user then does the same thing for the patient search.
Once each of the combo boxes has a choice selected, the third button becomes active and the user can push it. It will call a Sub that populates the third table.
See, easy as pie.
I'm sure others will answer as well, so you should probaly wait a few days and see what other ideas you get.
Comment