Submitting to multiple tables at once

[TheDust]

Forgive me if I'm asking the obvious, but I'm very new with using Access to create interfaces...

I have a form where the user creates a client profile. Some of the data being entered should be inserted into the "CLIENTS" table, and some of it belongs in the "CONTACTS" table. Finally, upon clicking "Submit", I need the primary key autoNum identifier that was created for each table to be inserted into the table "CLIENTSCONTACTS", which links clients to different contacts (this is so it is possible to have multiple contacts for each client).

My question is: Is it possible to not have Access update the database until the user clicks "Submit", and also, how would such an INSERT query look? Access has no wizard tools for creating INSERT statements, so I have no idea how to incorporate elements from the form in my INSERT statement. Any help is really appreciated, and thanks in advance!

 

[Rolliee]

Do this with VB. I would use DAO code to add the new records with a command button.


rollie@bwsys.net

 

[TheDust]

Very cool.... does anyone know where I can find an example or two of this?

 

[TheDust]

Also, one part of my question wasn't answered... is it possible to submit data to two tables, and then take the autoNum primary key identifier from each table's new row and use those values to populate other tables?

 

[Rolliee]

yes! send me a "SMALL" sample of your database as a ZIP file and what you would like it to do.

rollie@bwsys.net

 

[5694]

I have the same problem. What is the solution for this? I have a form (frmProject) where users create/view projects. Some of the data being entered should be inserted into the "PROJECT" table, and some of it should go to the "Status" table and some of it to "Team" table. Finally, upon clicking "Submit", I need the primary key autoNum identifier that was created for each table to be inserted into the table "Teamlookup”, which links Projects to different teams (this is so it is possible to have multiple team and team members for each project).

 

[Rolliee]

Suppose you have and unbound box with five text boxes 0 - 4. and a cmdButton1. The code in cmdButton1_click() would be as follows.

DIM rs as DAO.recordset, r1 as DAO.recordset, r2 as recordset

set rs = me.recordseclone
set r1 = currentdb.openrecordset("Table1&quot
set r2 = currentdb.openrecordset("Table2&quot

now you have three recordsets, one bound to the form and the others just two other tables in the current database
rs.movelast ' populate the recordset
rs.movefirst ' as you would suppose

do while not rs.eof
if rs![fieldsomething] = me.text0 then
me.bookmark = rs.bookmark ' this sets you form to the record which filesomething is like texto
exit do
endif
rs.movenext
loop
' suppose you want two fields in rs - the form recordset - to become fields in r1 and r2

r1.addnew
r1![fieldone] = rs![Fieldthree]
r1.update
me.[key1]= r1![Autokey1]
r1.update
r2.addnew
r2![fieldtwo] = rs![key3]
r2.update
me.[key2] = r2![Autokey]
rs.close
r1.close
r2.close
set rs = nothing
set r1 = nothing
set r2 = nothing

exit sub

rollie@bwsys.net