Solved

onclick of a command button insert data into mutliple tables at once in Access 2007

Posted on 2012-03-18
11
539 Views
Last Modified: 2012-03-26
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

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

Open in new window



Can Insert into multiple tables using the same concept.

Thanks and appreciate it
0
Comment
Question by:niceoneishere
  • 6
  • 5
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 37736165
Just repeat lines 6-8 changing the table name
0
 
LVL 1

Author Comment

by:niceoneishere
ID: 37737806
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
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

Open in new window


Thanks and appreciate it
0
 
LVL 77

Accepted Solution

by:
peter57r earned 500 total points
ID: 37737913
No; I said repeat lines 6-8 not just line 6.

Dim strSql As String
   strSql = "INSERT INTO Billing(Student_ID] SELECT Students.Student_ID from Students;"
  DoCmd.RunSQL strSql

   strSql = "INSERT INTO Grading(Student_ID] SELECT Students.Student_ID from Students;"
  DoCmd.RunSQL strSql

   strSql = "INSERT INTO [Financial Aid](Student_ID] SELECT Students.Student_ID from Students;"
  DoCmd.RunSQL strSql


   strSql = "INSERT INTO Sports(Student_ID] SELECT Students.Student_ID from Students;"
    DoCmd.RunSQL strSql

Your error suggests that there is something wrong with the last query.
But you need to test with the above code first and see what happens.
0
How our DevOps Teams Maximize Uptime

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us. Read the use case whitepaper.

 
LVL 1

Author Comment

by:niceoneishere
ID: 37738469
Sorry I changed the code as you suggested but I am still get the same error.

Thanks
0
 
LVL 1

Author Comment

by:niceoneishere
ID: 37738532
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
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 37738662
The warning message applies to an individual query.  I think it would be misleading to turn off messages for 3 of the queries and leave them on for one .  You might as well stop them altogether.  Although be aware that this means that errors will not get reported.

One way to do that is to add the line below after the dim statement..

Docmd.Setwarnings False

and add the next line after the last Runsql command

Docmd.Setwarnings True
0
 
LVL 1

Author Comment

by:niceoneishere
ID: 37739201
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
0
 
LVL 77

Expert Comment

by:peter57r
ID: 37741368
Yes, in principle, but I don't see what theree is to update.  The only field you are inserting is the id.
0
 
LVL 1

Author Comment

by:niceoneishere
ID: 37743255
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
0
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 500 total points
ID: 37743394
Then you need to modify your queries to include a Where clause like...

  strSql = "INSERT INTO Billing(Student_ID] SELECT Students.Student_ID from Students where Students.Student_ID =" & me.Student_ID  

This assumes Students.Student_ID is a number field.

If it is text then you would need this version...

  strSql = "INSERT INTO Billing(Student_ID] SELECT Students.Student_ID from Students where Students.Student_ID ='" & me.Student_ID   & "'"
0
 
LVL 1

Author Comment

by:niceoneishere
ID: 37766339
Thanks worked out perfectly. appreciate it
0

Featured Post

Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Although it can be difficult to imagine, someday your child will have a career of his or her own. He or she will likely start a family, buy a home and start having their own children. So, while being a kid is still extremely important, it’s also …
This article will inform Clients about common and important expectations from the freelancers (Experts) who are looking at your Gig.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

809 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question