-1

Let's say I have a table of events: tblEvents, and a table containing people: tblParticipants. Many people can participate in an event, so we have a one-to-many relationship here, which is stored in a table: tblEvents_Participants, which has records like this: Event_ID,Participant_ID.

I would like to create a data entry form that in its first section has fields for input about the event, and in the second section, a continuous subform using tblParticipants as a source, to which I can add people. Finally, I would like that when I click an cmdAddRecord button, the following will happen:

  1. Add an event record to tblEvents, let's say: Event_ID = X, based on inputs from section 1.
  2. In the junction table, tblEvents_Participants, add the records: "X, participant1_ID", "X,participant2_ID"..., based on the records chosen in the subform.

The problem I'm having is that such a thing supposedly requires 2 queries executed one after another: one to add the event record, and the other to add the records to the junction table, because the event's ID wouldn't exist to use in the junction table before it is added to tblEvents, and I haven't found a way to do this, at least without using VB code to execute queries one after another, which is pretty clumsy and hard to use.

EDIT: As per request, I'll post the data. I used pseudonyms in the post to make it as concise as possible, so I'll try to keep the data the same:

Comboxbox Row source:

SELECT tblParticipants.ID, tblParticipants.[FirstName], tblParticipants[LastName] FROM tblParticipants

Bound Column: 1
Column Count: 3
Column Width: 0,3,3

How the table looks inside the form (As I said, I used pseudonyms in my post so the field names are a bit different: (Every employee has many jobs, so employees==events, jobs == participants, ID refers to the junction table ID, it's irrelevant).

1

When I open the subform in a different tab, the entire table shows.

PS: Does the book record (ID=1, Title=Help) in your other post already exist before adding the authors?

sagooz
  • 31
  • 1
  • 8
  • Tell me why this is not a duplicate of http://stackoverflow.com/questions/12131211/create-form-to-add-records-in-multiple-tables/12132196#12132196 – Fionnuala Jan 01 '15 at 19:02
  • It is, thank you. Seeing as you're the author of the answer in that post, would you mind elaborating what code is needed in the "Not In List" event? I've followed every step except for that one, resulting in being unable to add records (Participants/authors) to the subform, so I'm guessing that might have something to do with it. @Fionnuala – sagooz Jan 01 '15 at 20:10
  • See if http://msdn.microsoft.com/en-us/library/office/ff845736%28v=office.15%29.aspx helps. – Fionnuala Jan 01 '15 at 22:13
  • Guess that's not it, since I don't even have the option to type anything in the table, whether it's in the list or not. – sagooz Jan 01 '15 at 23:11
  • I am sorry, but your comment does not make sense to me. You should not be typing in a table. There is a new option in 2010 ( http://allenbrowne.com/ser-27.html ). The link I previously posted includes code for the NotInList event. It is important to ensure that you have set Limit To List to true. – Fionnuala Jan 02 '15 at 09:47
  • In the final picture in your post (Final Form), you can open the combo box and select an author. For me, it shows as if the table is completely empty, with no ability to select anything from my combo box. – sagooz Jan 02 '15 at 09:50
  • Check your row source, check your column count, check your table, check your column widths, ideally post this information by editing your post above. – Fionnuala Jan 02 '15 at 09:51

1 Answers1

0

i suppose you answered yourself; you need two queries because you need the event_id returned from first query

Rotari Radu
  • 645
  • 6
  • 13