By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
455,489 Members | 1,777 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 455,489 IT Pros & Developers. It's quick & easy.

Insert records to a related table when an option is selected from list

P: n/a
Hello!

Here is part of my database:

tblStudents: StudentID
tblMajorsAndStudents: StudentID, MajorID
tblMajors: MajorID
tblMajorsAndClasses: MajorID, ClassID, QuarterTaken
tblClasses: ClassID
tblStudentsAndClasses: StudentID, ClassID

When you create a new student, you can assign a major to that new
student.
Majors have a predefined set of classes that students have to take to
graduate.

When I add a major to a student, I would like to populate the
tblStudentsAndClasses with the classes that this student has to take in
order to graduate.
Moreover, if a student decides to change his major, I would like to
update the tblStudentsAndClasses, and remove all records that are
associated with the major, except the classes that have been taken
(QuarterTaken is defined).

Could anyone help me with that?

Thank you very much,
Celine

Nov 13 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a
Use the AfterInsert event procedure of the form where you add the record to
tblMajorsAndStudents. In that event, execute an append query statement to
add the records to tblStudentsAndClasses.

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

To get the specific SQL statement, mock up a query that selects records from
tblMajorsAndClasses, Put some literal value in the Criteria under MajorID.
In a fresh column in the Field row, type:
StudentID: 99
so you have a literal there also. Then change it to an Append query (Append
on Query menu.) Now switch to SQL View (View menu), and you have an example
of the string you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ce*******@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Hello!

Here is part of my database:

tblStudents: StudentID
tblMajorsAndStudents: StudentID, MajorID
tblMajors: MajorID
tblMajorsAndClasses: MajorID, ClassID, QuarterTaken
tblClasses: ClassID
tblStudentsAndClasses: StudentID, ClassID

When you create a new student, you can assign a major to that new
student.
Majors have a predefined set of classes that students have to take to
graduate.

When I add a major to a student, I would like to populate the
tblStudentsAndClasses with the classes that this student has to take in
order to graduate.
Moreover, if a student decides to change his major, I would like to
update the tblStudentsAndClasses, and remove all records that are
associated with the major, except the classes that have been taken
(QuarterTaken is defined).

Could anyone help me with that?

Thank you very much,
Celine

Nov 13 '05 #2

P: n/a
Hey thank you very much for your answer.
I still got a little problem:
On my student form, I have a students and majors subform that gives me
a list of all available majors.
When I select a major, it adds the record to the tblStudentsAndMajors
(StudentID and MajorID).
Now, I added the after update event with the INSERT statement.
If I try to select a major for a student that doesn't have one yet, it
says it's going to append 0 rows.
If I reselect a major that has already been assigned to a student (it
is already in the tblStudentsAndMajors), then it will append all the
rows I need.
In fact, when I do the afterupdate, access doesn't add the record to
the tblStudentsAndMajors before processing my INSERT statement.
I tried to create another SQL statement to insert that data, but still
didn't get any rows to be added.

Any thoughts on that?

Thank you!
Celine
Allen Browne wrote:
Use the AfterInsert event procedure of the form where you add the record to
tblMajorsAndStudents. In that event, execute an append query statement to
add the records to tblStudentsAndClasses.

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

To get the specific SQL statement, mock up a query that selects records from
tblMajorsAndClasses, Put some literal value in the Criteria under MajorID.
In a fresh column in the Field row, type:
StudentID: 99
so you have a literal there also. Then change it to an Append query (Append
on Query menu.) Now switch to SQL View (View menu), and you have an example
of the string you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ce*******@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Hello!

Here is part of my database:

tblStudents: StudentID
tblMajorsAndStudents: StudentID, MajorID
tblMajors: MajorID
tblMajorsAndClasses: MajorID, ClassID, QuarterTaken
tblClasses: ClassID
tblStudentsAndClasses: StudentID, ClassID

When you create a new student, you can assign a major to that new
student.
Majors have a predefined set of classes that students have to take to
graduate.

When I add a major to a student, I would like to populate the
tblStudentsAndClasses with the classes that this student has to take in
order to graduate.
Moreover, if a student decides to change his major, I would like to
update the tblStudentsAndClasses, and remove all records that are
associated with the major, except the classes that have been taken
(QuarterTaken is defined).

Could anyone help me with that?

Thank you very much,
Celine


Nov 13 '05 #3

P: n/a
Well, I got it...
Used: If Me.Dirty = True Then Me.Dirty = False
To save the record before doing the afterupdate, and it works! it adds
all the rows I want!
ce*******@gmail.com wrote:
Hey thank you very much for your answer.
I still got a little problem:
On my student form, I have a students and majors subform that gives me
a list of all available majors.
When I select a major, it adds the record to the tblStudentsAndMajors
(StudentID and MajorID).
Now, I added the after update event with the INSERT statement.
If I try to select a major for a student that doesn't have one yet, it
says it's going to append 0 rows.
If I reselect a major that has already been assigned to a student (it
is already in the tblStudentsAndMajors), then it will append all the
rows I need.
In fact, when I do the afterupdate, access doesn't add the record to
the tblStudentsAndMajors before processing my INSERT statement.
I tried to create another SQL statement to insert that data, but still
didn't get any rows to be added.

Any thoughts on that?

Thank you!
Celine
Allen Browne wrote:
Use the AfterInsert event procedure of the form where you add the record to
tblMajorsAndStudents. In that event, execute an append query statement to
add the records to tblStudentsAndClasses.

Private Sub Form_AfterInsert()
Dim strSql As String
strSql = "INSERT INTO ...
dbEngine(0)(0).Execute strSql, dbFailOnError
End Sub

To get the specific SQL statement, mock up a query that selects records from
tblMajorsAndClasses, Put some literal value in the Criteria under MajorID.
In a fresh column in the Field row, type:
StudentID: 99
so you have a literal there also. Then change it to an Append query (Append
on Query menu.) Now switch to SQL View (View menu), and you have an example
of the string you need to create.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

<ce*******@gmail.com> wrote in message
news:11*********************@f14g2000cwb.googlegro ups.com...
Hello!

Here is part of my database:

tblStudents: StudentID
tblMajorsAndStudents: StudentID, MajorID
tblMajors: MajorID
tblMajorsAndClasses: MajorID, ClassID, QuarterTaken
tblClasses: ClassID
tblStudentsAndClasses: StudentID, ClassID

When you create a new student, you can assign a major to that new
student.
Majors have a predefined set of classes that students have to take to
graduate.

When I add a major to a student, I would like to populate the
tblStudentsAndClasses with the classes that this student has to take in
order to graduate.
Moreover, if a student decides to change his major, I would like to
update the tblStudentsAndClasses, and remove all records that are
associated with the major, except the classes that have been taken
(QuarterTaken is defined).

Could anyone help me with that?

Thank you very much,
Celine


Nov 13 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.