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?

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

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.

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