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 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
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
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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)
...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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,...
|
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)...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
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...
|
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...
|
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....
|
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
| |