Jul 19 2005, 03:49 AM
I have two tables connected to a query, then to a form. The first table would be an event, while the other are attendees. They are in a many to many relationship. In the form, how do I create a new record with out having to go to each individual table and inserting the record. When I try to simple insert the record into the forum, it will just say it cannot because there is no related record. I want to be able to insert new people into the forum if they don't currently exist.
Jul 19 2005, 04:34 AM
If you have a M:M you need a 3rd joining table..
EventID - Autonumber
EventName - Text
AttendID - Autonumber
AttFName - Text
AttLName - Text
EventAttendID - autonumber
fkEventID - Number
fkAttendID - Number
So the table tblEventAttend hold which event and who's going..
Jul 19 2005, 05:15 AM
As Larry has already told you, the way to do this is with a Junction table. One table for attendees, one table for events, one table for eventsattended. The eventsattended table would contain one row for each different attendee, event combination. So if attendeeID1 attended 5 events there would be five rows in this table, each would have the attendeeID and the eventID. You could have other data in this table as well.
The way I would do this is with a form/subform -- the form would display the attendee table data, the subform would display the eventsattended data for that attendee. The events table would be the source of a combo box that would allow the user to select the event.
You could use the Not In List event to open a form to allow entry of new attendees and/or new events.
Jul 19 2005, 05:47 AM
I actually have two example. the describe above, I have a third table as the m:m relationship.
in this example, when i tried to insert new record for the third table it would not allow me because of related table does not have the related data.
another example I had was 1:M but when one attendee can only go to one event. I have a form with the event, the subform would list the attendees. I can't insert new attendees to the the form.
when I was describing it the first time I kind of skip a step and combine the two.
I know this should be an easy task, but what do you mean "Not in List?"
Jul 19 2005, 06:08 AM
A combo box has a Not In List event, for when a user wants to enter an item into the database that is not already in the list that the combo box has. He just types it in, and indicates that he is through typing in some way, then the not in list event does what it does. It would usually be to open a form to enter the data into the correct table.
I don't carry the code around in my head, you'll need to look it up in a book or it's probably on this site someplace.
You really want it to be so that one attendee can attend many events not one event can have many attendees.
The best way IMHO to do this is as I have already described. A main form to display the Attendee data, a subform to display the EventsAttended data. You would use a combobox on the main form to set main form to a particular attendee---with the Not In List event to allow new attendees.
The subform would fetch its data from the EventsAttended table---so the main form would show you who and the subform would show you what attended. When adding a new record to the EventsAttended table, there would be a combo box to pick the Event from the Events table. The Not In List event could be used here to allow entry of the event data.
The problem about record insertion is because of referential integrity. You can't enter a record on the many side unless there is a corresponding record on the one side.
Jul 19 2005, 06:15 AM
the examples are really total different, I just use the same type of example for illustration purpose. I will check out the not in function. I has hoping that if I insert a new record on the many side, it will also insert a new record on the one side.
Jul 20 2005, 07:14 AM
No, not by itself it won't. You'll need to insert the one record first then the many record.
Jul 22 2005, 12:02 AM
are there any vba codes to handle this?
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here