Create Multiple Records In a Subtable From Single Subform

Ok, for some reason. I cannot focus on this and I keep missing something.

My intention is not for you to hold my hand through this, but at this point, I'm clearly not doing something right.

Actually, part of it is my fault. When Paul showed me how to do this, I set it up as a main form/subform, whereas I told you to pass the value between two separate forms. There is a bit of programming sleight-of-hand that has to be done.

I'll have to look at this tonight, as my boss is breathing down my neck right now, unless Paul's watching, and he is more than free to take the reins here, since my ideas are coming from his suggestions.
 
Paul has been checking in, but Patrick has had things well in hand so he stayed out of the way. :D

For starters, you have tblPersonnel as the target table, but it's not designed to hold the info. As Patrick mentioned earlier, you need another table. It would be what's often called a junction table, to hold a many-to-many relationship. Trips can have many participants, participants can go on many trips. I added one called tblPaul (which you'll obviously want to change, but I wanted it to be clear which one it was); that and various other changes are in this quick and dirty sample. Changes noted in comments.
 

Attachments

Paul has been checking in, but Patrick has had things well in hand so he stayed out of the way. :D

For starters, you have tblPersonnel as the target table, but it's not designed to hold the info. As Patrick mentioned earlier, you need another table. It would be what's often called a junction table, to hold a many-to-many relationship. Trips can have many participants, participants can go on many trips. I added one called tblPaul (which you'll obviously want to change, but I wanted it to be clear which one it was); that and various other changes are in this quick and dirty sample. Changes noted in comments.

What he said. :D And since I had to stick my nose into it, I added the code to let you either select all records in the list or clear the list. Just so I felt like I actually contributed. :p
 

Attachments

Just thought of something for this. How would you go about preventing a person from being entered more than once for one trip?
 
Just thought of something for this. How would you go about preventing a person from being entered more than once for one trip?

Well, the quick-and-dirty answer would be to set the TripID and ParticipantID fields in that table to be primary keys so that the table won't accept a name and trip ID twice. The good news is, Access won't allow the entry if you duplicate records. The bad news is, it won't tell you which of the names is in violation of the primary key.
 
Duh. Now that I think about it (It was a long day yesterday and my brain was fried), I should have known that. :)

Thanks again, Patrick!
 
Duh. Now that I think about it (It was a long day yesterday and my brain was fried), I should have known that. :)

Thanks again, Patrick!

Oh, I've spent many a day brain-fried. I sympathize. :o
 
pbaldy,

Can you check out another thread I where I have a similar problem to this?

I can't post a link apparently since I haven't had more than 10 posts but it's a thread titled "Create Multiple Records on One Form". Please review and provide any feedback!
 
spudracer,

I've tried using a subform but I can't figure out how to create two or more records by using one form unless I add some code. I'm also not sure what a continuous form is and how it differs from using a subform.
 

Users who are viewing this thread

Back
Top Bottom