It looks like your Vacation table is where everything is all put together (ie. passenger, flight, airport, etc..) When you start a new Vacation (which I'm guessing your main Vacation data form will be based upon), you can have a subform on that form (based on the Flight table) whereby you then select the plane and pilot.
Your customer form should be a separate form based solely on the customer table (ie. you add a customer and then click a button to open the Vacation main form). You wouldn't necessarily need the Ticket table (you can just store the customerID in the Vacation table unless for some reason you're tracking additional Ticket information in which I'd probably set that to be directly related to the Vacation table (ie. not to the customer) and have a TicketID in the Vacation table. What I mean is that you're Vacation table really looks like it should be the Ticket table. And now that I think on it, the Ticket table should probably be named Vacation instead (I guess it depends on how you want to track it as it could go either way now that I've thought on it a 3rd time.)
1. Vacation table (and form) put's all the pieces together.
2. Separate forms for each of the other tables to edit information specific to that table (ie. form to add/edit customers, form to add/edit Pilots, form to add/edit planes, etc..) - same philosophy for Departure/Destination (and Ticket) tables.
3. Subform (based on Flight table) within the Vacation form to put the Pilot/Plane pieces of info together (ie. Flights) for the specific Vacation.
4. If you want to keep the Ticket table in the picture, then one way is that you could have the Ticket form as a subform (or even a listbox) on the Customer form (or again, open as a separate form). The user would then enter a new customer, create/edit a "Ticket" in the subform (ie. click a "New" button for a new ticket or an "Edit" button for that Ticket in the subform) which then opens the Vacation table (putting the TicketID into the Vacation form if it's a new Vacation record.)
5. If done correctly, you can also have nested subforms (ie. subform within a subform.) I will usually use a listbox on a form though (with 1 subform) versus using multiple nested subforms. There's many different ways to do it but research on listboxes as they can often be your friend (with an "Add New" button to popup a form to quickly add a new record for another table or a "Delete highlighted listbox item" button to delete the item(record) in the listbox) on a specific form (versus actually making a subform.) Sometimes just a subform works best, other times a listbox works nicely in junction with a subform to get/edit information from multiple tables.
Last edited by pkstormy; 05-27-09 at 21:32.
Expert Database Programming
MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)