473,326 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,326 software developers and data experts.

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

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
3 1741
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
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
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: EricRobineau | last post by:
hello I have a DB with many inter-related tables (MySQL) My main table called "content" has almost only foreign keys (integers) some have a 3 level relation (ex: content->city->region->country) ...
1
by: Ian Davies | last post by:
Hello In a php file I have a drop down list with index numbers in whos default value is feed into an sql query that filters records from my database and displays them in an html table. Trouble...
16
by: Philip Boonzaaier | last post by:
I want to be able to generate SQL statements that will go through a list of data, effectively row by row, enquire on the database if this exists in the selected table- If it exists, then the colums...
6
by: Paul T. Rong | last post by:
Dear all, Here is my problem: There is a table "products" in my access database, since some of the products are out of date and stopped manufacture, I would like to delete those PRODUCTS from...
3
by: Bob Alston | last post by:
I have a routine to copy data to new versions of my app via insert into sql statements. Unfortunately, due to evolution of my app, sometimes the new version has more restrictive editing than an...
7
by: GJPeacock | last post by:
Hi all, Please excuse my limited technical knowledge of PHP, so far i have only created PHP sites using Dreamweaver, i find know that i need features outside of it's capabilities. I am trying...
2
by: assgar | last post by:
Hi Developemnt on win2003 server. Final server will be linux Apache,Mysql and PHP is being used. I use 2 scripts(form and process). The form displays multiple dynamic rows with chechboxs,...
11
by: shriil | last post by:
Hi I have this database that calculates and stores the incentive amount earned by employees of a particular department. Each record is entered by entering the Date, Shift (morn, eve, or night)...
58
by: bonneylake | last post by:
Hey Everyone, Well recently i been inserting multiple fields for a section in my form called "serial". Well now i am trying to insert multiple fields for the not only the serial section but also...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.