Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
Password Reminder
Register
| FAQ | Members List | Search | Today's Posts | Mark Forums Read
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old November 13th, 2008, 05:26 PM
Authorized User
 
Join Date: Jan 2006
Location: , , .
Posts: 48
Thanks: 0
Thanked 0 Times in 0 Posts
Default Command to insert data in multiple tables?

i use a form to collect data in my text fields and i string the information into a SQL string and have it insert it into my Work Order table. is it possible to have the same form insert data into multiple tables all at the same time? for example when i click save the data is inserted into 1 table (T_WorkOrder) and it creates a record WOID# but i would like to create 3 check boxes on my form so if they are checked then the WOID# is inserted into 3 other tables depending on what boxes are checked. my goal is to establish the WOID# in the other tables if the work order applies to those tables. in other words those tables represent the different departments in the company and i would like to establish a record for those departments that links it automatically to the work order at the time the work order is created. make sense???

right now i'm using this to insert my SQL string into my work order table:
stSQL = "INSERT INTO T_WorkOrder(ClientID.....
Set rs = New ADODB.Recordset
rs.Open stSQL, CurrentProject.Connection, adOpenDynamic, adLockOptimistic

the challenge i see is the WOID# isn't created until this is executed then i need to grab that WOID# and insert it into the other tables depending on what check boxes are checked.

is this possible?? or is there another way to approach this?

Reply With Quote
  #2 (permalink)  
Old November 17th, 2008, 09:54 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

Did you resolve this yet?

You see why this can be a problem. Nromally you might bind the form to the table and the ID issue goes away. You can create your record first, then query the Work Order table for Max(WOID), and then use that in your other insert statements. This is normally not a problem, but really depends on how many concurrent users you have. If there are a lot, you could create another field, and then assign the record a temporary ID as you submit it, and then query for that temporary ID. There may be other more elegant ways of doing this, but the issue has never come up with me.

So you might create a field called T_WOID, then insert a value based on the userID of the current user, and the number of seconds since 12/31/1999, for example. Then paste that into a variable, and use the variable to query subsequent inserts.

Did any of that help?

mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
  #3 (permalink)  
Old November 17th, 2008, 07:02 PM
Friend of Wrox
 
Join Date: Jun 2008
Location: Snohomish, WA, USA
Posts: 1,649
Thanks: 3
Thanked 141 Times in 140 Posts
Default

I'm not sure this works with Access, but...

If you use an ADO connection (ADODB.Connection object) and use the JET OLEDB driver with it (instead of the Access driver!) then you *can* do an INSERT query in SQL and follow it with
    SELECT @@IDENTITY
an voila! You have the autonumber field value for the just added record.

Note that you *MUST* use the same OPEN connection for the insert and the SELECT @@IDENTITY. And you must use the JET OLEDB driver.

Obviously, if you are using implicit binding from form to DB in Access, I would assume this couldn't be used.
Reply With Quote
  #4 (permalink)  
Old November 18th, 2008, 09:16 AM
Friend of Wrox
Points: 9,611, Level: 42
Points: 9,611, Level: 42 Points: 9,611, Level: 42 Points: 9,611, Level: 42
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Mar 2004
Location: Washington, DC, USA.
Posts: 3,069
Thanks: 0
Thanked 10 Times in 10 Posts
Default

If it were a bound form, then you wouldn't need to use anything other than code on the After Insert event to get the ID.

@@IDENTITY is not going to work for an Access table, I would bet lunch on that.

I assumed Access, but it could be linked SQL Server tables - not good - since the code shows "CurrentProject.Connection" as the Connection string.


mmcdonal

Look it up at: http://wrox.books24x7.com
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
insert data in two tables from form mfarooqw ASP.NET 1.0 and 1.1 Professional 1 July 10th, 2007 08:34 AM
Insert Data into two tables Lofa PHP Databases 4 July 7th, 2007 10:36 PM
Insert into Multiple Tables bmalex1 Beginning PHP 0 February 6th, 2006 12:45 PM
data from multiple tables keyvanjan Classic ASP Basics 1 January 24th, 2006 06:32 PM
How do I INSERT INTO multiple tables using array? aspiretech PHP Databases 0 December 10th, 2005 12:53 AM



All times are GMT -4. The time now is 01:28 PM.


Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.