Tammu
asked on
onclick of a command button insert data into mutliple tables at once in Access 2007
Hello Experts,
I have table in Access 2007 called Students whose fields are
ID autonumber
Student ID Number
Student Name Text
I created a form based on this table called Student Form. on this form I have a command Button called Save. Basically What I am looking is onclick of this Save button I want to grab the Student ID from Student table and Insert into Student ID of Billing table, Student ID of Grades Table, Student ID of Financial Aid table.
Is this possible, I know I can insert into one table by using the code below
Can Insert into multiple tables using the same concept.
Thanks and appreciate it
I have table in Access 2007 called Students whose fields are
ID autonumber
Student ID Number
Student Name Text
I created a form based on this table called Student Form. on this form I have a command Button called Save. Basically What I am looking is onclick of this Save button I want to grab the Student ID from Student table and Insert into Student ID of Billing table, Student ID of Grades Table, Student ID of Financial Aid table.
Is this possible, I know I can insert into one table by using the code below
Private Sub cmdSave_Click()
On Error GoTo Err_cmdSave_Click
Dim strSql As String
strSql = "INSERT INTO Billing([Student ID]) SELECT Students.[Student ID] from Students;"
DoCmd.RunSQL strSql
Exit_cmdSave_Click:
Exit Sub
Err_cmdSave_Click:
MsgBox Err.Description
Resume Exit_cmdSave_Click
End Sub
Can Insert into multiple tables using the same concept.
Thanks and appreciate it
Just repeat lines 6-8 changing the table name
ASKER
Thanks for replying I changed the code to below and now its throwing an run time error 3134 insert into statement error on the line DoCmd.RunSQL strSql.
here is update code
Thanks and appreciate it
here is update code
Dim strSql As String
strSql = "INSERT INTO Billing(Student_ID] SELECT Students.Student_ID from Students;"
strSql = "INSERT INTO Grading(Student_ID] SELECT Students.Student_ID from Students;"
strSql = "INSERT INTO [Financial Aid](Student_ID] SELECT Students.Student_ID from Students;"
strSql = "INSERT INTO Sports(Student_ID] SELECT Students.Student_ID from Students;"
DoCmd.RunSQL strSql
Thanks and appreciate it
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Sorry I changed the code as you suggested but I am still get the same error.
Thanks
Thanks
ASKER
Ok I got it working but I have something come up, when I click on the save button , it shows an alert box saying you are about append 3 rows which is fine but the message comes for each and every table. How make that message come just for once.
Thanks and appreciate your help
Thanks and appreciate your help
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Ok thanks for the tip, can I ask you something else sir, Instead of inserting all the time, is there any way to modify the code in such way that if Student ID contains a value just upadate it rather then inserting and if its blank insert it.
Thanks
Thanks
Yes, in principle, but I don't see what theree is to update. The only field you are inserting is the id.
ASKER
I understand that sir, whats happening now is every time I am hit the button, it keeps inserting all the Student Id's instead of just the new ones. so basically in all the these tables I have the student Id repeated
Thanks and appreciate it
Thanks and appreciate it
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thanks worked out perfectly. appreciate it