Use append query add items to subforms

[Pianopizza5]

In my specific project, I want all the subform items to populate when a record is made in the master form (I don’t want the user to have to enter any information, other than the record in the master form). I tried using an append query triggered in the form’s On Load event. The append query adds the necessary information to the composite table, but the problem is that the “link field” from the main form needs to populate next to the appended items for the items to display. I tried passing the link field from the main form, but no luck. Any thoughts on how to do this?



For example:

Table 1: tblSports
Table 2 (composite table): tblSportsStats
Table 3: tblSchoolStudents

The main form is Table 1 (Master field: SportID)
The subform is Table 2 (Link field: SportID)
With a lookup of the school student

I want to be able to easily add the school students to the sport (and add more if any other students come) and add additional sports.

My append query is: StudentID from the tblSchoolStudent to the StudentID field in the tblSportsStats
I have the criteria of: Not In ([tblSportsStats]![StudentID])
This prevents me from adding duplicates
But this creates a problem because then I can’t use the query for adding the students when I have another sport (duplicate StudentIDs)

Any ideas? I’m open to using VBA and any other approaches.

Thanks.

[June7]

First have to commit parent record to table. Then VBA can build and execute an INSERT SELECT action SQL to "batch" create a set of dependent records. This is a fairly common topic. Here is one example https://stackoverflow.com/questions/...-in-access-vba

Your SQL code could be something like:

CurrentDb.Execute "INSERT INTO tblSportsStats(StudentID, SportID) SELECT StudentID, " & Me.cbxSport & " FROM tbSchoolStudents"

Set StudentID and SportID as a compound index (no duplicates allowed) in tblSportsStats and WHERE criteria will not be needed.

[moke123]

Here's an example where you can add a student 1 by 1 or add several at a time and avoid duplicates.

[Pianopizza5]

Hello,

Thank you all so much! Your posts were very helpful and gave me some ideas. One step that I needed to do was reference the form field in my query. Then, I followed the video here (which performs a query and then compares those results to another query).

https://www.youtube.com/watch?v=kflL...V737s&index=74

Thank you! I got everything working beautifully.

[pbaldy]

Post 4 was moderated, I'm posting to trigger email notifications.