Queries and Forms with Subforms

[djh]

Looking for a bit of enlightenment....

Going to just put it real simple. I have three tables.

1. Customers
2. Shows
3. Exhibits

If I choose customers as my entery point, and from a customers form, a user chooses a customer, how would I get a form to show me which shows (while also displaying the customer's info in grayed out, not editable ares) that particular customer has appeared in. And subsequently, what particular exhibits they displayed for each boat show listed?

Thanks... I just can't think straight....

 

[Maryanne]

Look at Pop-up forms and the modal on/off function. I usually put a button on my main form (customer form) which will activate the Pop-up.

 

[TommyIndigo]

If you want to solve this with a form/subform, the relationships between the tables are quite important. How are the CUSTOMERS and SHOWS tables related? It sounds to me that for starters you will need another table, perhaps SHOW_ATTENDANCE that is on the many side of two relationships (one with the CUSTOMERS table, and another with the SHOWS table). Fields for the SHOW_ATTENDANCE table would be ShowAttID, Customer ID, and ShowID. This way, each customer would be able to attend several shows. Your main form would then be based simply on customers, and the subform would be a based on a query using the SHOW_ATTENDANCE table with criteria of the active customer in the main form. You could then have another subform that does essentially the same thing with EXHIBITS. Unless I missed something, I think this should work! Good luck.

 

[djh]

Thanks TommyIndigo.

Great suggestion. I knew that customers and shows is a many-to-many, just didn't quite grasp the idea of having a show attendance table.. duh.

Two related questions:

1) So table Exhibits would then tie into the Show_attendance table or the show table or customer table?

2) And how exactly do I query only the current Customer from my main customer form? That is, once I have a customer form set up, I want a command button to take me to all the shows that particular customer has been in, with a subform that lists all exhibits. How do I pass the current customer string into a subform, and from the subform, how do I pass it again to the exhibits?

Sorry, I'm new at this....it's kind of fun but kinda frustrating as well... Thanks!

 

[TommyIndigo]

Assuming each exhibit would only appear at one show, shows to exhibits would have a one to many relationship.

In the query for the shows form, enter the criteria for the Customer ID field as Forms![Customers]![CustomerID]
(Note, you might need to play around with the exclamation points, and make one/both of them periods...that part always messes me up!)

Good luck..let me know how it turns out.

[This message has been edited by TommyIndigo (edited 07-25-2000).]