How do you add records to two tables from a form/subform?

[greaseman]

I've got a form with one subform. What the form attempts is to allow users to choose one or many courses in which to enroll one or many students. It doesn't work. What I want to have happen is when the user presses a SAVE button, to have it go through each course (in a Course table) and add the first course chosen, and then to have it go through each student chosen (in a related Student table); next, if more than one course is chosen to have the whole thing repeat the process. Here's a snippet I'm trying to use in my process:

Private Sub cmdSave_Click()

Dim iNumLstRows1 As Integer
Dim iPtr1 As Integer


With Me.LstCourses

iNumLstRows1 = .ListCount - 1
For iPtr1 = 0 To iNumLstRows1
If .Selected(iPtr1) Then
Me.txtCourseId = .Column(0, iPtr1)
Me.txtCourseName = .Column(1, iPtr1)
Me.Requery
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
End If
Next iPtr1
DBEngine.Idle
.Requery
End With

DoCmd.GoToRecord , , acNewRec

Exit_cmdSave_Click:
Exit Sub

Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click

End Sub

The part I'm confused about is it seems to go through the code, but only the last course chosen seems to work, not other courses.

Am I going about this the right way? Is there something out there that actually works?

Thanks in advance to those willing to help break a brain block.

 

[daveUK]

This may be more of a problem to do with the way your database is setup. It sound as if you have 2 tables - one for students and one for courses. If this is correct you have a many - many relationship i.e. many students can enrol on many courses. You will need to have a link table in between the 2 tables to negate the many - many relationship. The link table will only need to contain the primary keys from the course and student tables.

 

[greaseman]

Thanks to all who have replied to my plight - your answers are appreciated. And, they give food for thought. I'll definitely keep the advice i mind.

Actually, I had used a third table comprised of the keys of the courses table and the student table. And the original form and adding of records worked well. It was when I changed my form from picking a single course with multiple students to trying to pick multiple courses with multiple students that things went strangely. My thought was in attempting a "for x to 99999-- loop" but apparently that didn't work too well.

Thanks again!

 

[greaseman]

It still doesn't work

I've tried your ideas, but my screen still doesn't work. When I have a combo box, "Add An Enrollment", set up to select only one course for many students, everything works OK. But...when I change the combo box to a list box, to select many courses for many students, only the last record gets processed for courses. I've attached a screen shot for those of you who might wish to help a brain-dead person. I wasn't able to upload a zipped copy of my .mdb, due to size issues, but if there is anyone willing to help, I can always e-mail you my zipped database.

Thanks in advance - you people are wonderful!

 

[greaseman]

Actually, this is where I was starting from.... a combo box on the main form, where I could select one course, and then a subform, where I could then select as many students as desired to be enrolled into that one course. My thinking was to change the main form combo box into a multi-select list box (thereby selecting more than one course), and, then on to the subform for enrolling multiple students into those multiple courses. Something along the lines of a for loop within a for loop.

In any case, here is the code as originally written, which does work for a one cours / many students concept:

Code attached to the "SAVE" button:

Private Sub cmdSaveRecord_Click()
On Error GoTo Err_cmdSaveRecord_Click

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

Exit_cmdSaveRecord_Click:
Exit Sub

Err_cmdSaveRecord_Click:
MsgBox Err.Description
Resume Exit_cmdSaveRecord_Click

End Sub


That's it! And Pat, I am aware, as you said in your earlier reply, that that type of DoCmd code is archaic, but I wanted to put everything back to the way it was, and try to restart from scratch - I simply felt I was putting myself into a deeper and deeper hole with my modification attempts.

Here also are the descriptions of my tables used:

tblCourses:
CourseId
CourseName
VendorId
CourseCategoryId
CourseDuration
CoursePublicCost
CourseDelivery
CourseNotes
CourseType

tblELearningSessionStudents ELearningStudentId
SessionId
StudentId
StudentRC
CancelledDate
TiwUpdated
CompletedCourse

tblELearningStudents
StudentID
StudentLastName
StudentFirstName
StudentSBCUserId
StudentRC
StudentJobYiyle
StudentCompany
StudentMgmt
StudentNotes
StudentOldName

tblNonSAPForELearning
SessionId
CourseId
CourseName
StartDate
Notes
EnrolledBy
DateEnrolled

The tblCourses field "CourseId" ties to the tblNonSAPForELearning "CoursesId" field. The tblNonSAPForELearn9ing field "SessionId" ties to the tblELearningSessionStudents field "SessionId" and the tblELearningStudents field "StudentId" ties to the tblELearningSessionStudents field "StudentId". The tblELearningSessionStudents is my linking table, to tie together Courses and Students. The way I want it to come out is: for each new or additioanl course selected, that is a new or additional session (the "SessionId" field. for each new or additional course selected, there will be one or more students related to that course (or, in my case, SessionId). So... for Course 1, I want Students 1, 2, 3, 4 and 5 to attend the course. Then, for Course 2, I want those same students or other students to attend Course 2.... and so on.

I've got a course table and a student table and a linking table apparently all set, so --- how do I go about "picking" a course or courses and then pick a student or group of students to be enrolled in my chosen courses?

That's all I really want to do: change the combo box to a list box, pick entries from the list box, then pick students from a subform, and have all my tables nice and pretty and filled with the selected data.

If I have gone about this the wrong way, I'd like to know that, also. Thanks so so much in advance for your assistance, Pat, and I look forward to your replies.

 

[greaseman]

Perfect!! That's what I thought! When I got home last night and was pacing while thinking, I told myself I most likely could use Pat's concept and expand it to an "outie and innie" type loop.

So if I understand you correctly what I basically should do is place my courses where you have names, and then where you have a meeting date, I should place another multi-select list for students. After that, on the command button, I should modifiy that to do the nested loops. That sounds good, and I'll give it a shot.

Thanks soooooo much! Like I said, my thoughts were a loop in a loop, but I have had a heck of a time turning concept to code! Wouldn't it be nice to have a complete couple of hours or days with no interruptions, so that the two (thoughts and code) would more closely match?

Have a good one and thanks!

 

[greaseman]

It worked!!

Pat,

A quick note to say IT WORKED!! Now I can go home for the weekend and get loaded! (Not really). I am happy, however, and wish to again express my appreciation for your suggestions and assistance.

Can we clone you?????