Add a record to multiple tables

S

Shawn

I created a form so people can input request information via series of
combo and text boxes and submit the request by clicking a command
button. The thing is that each request will have multiple steps before
they are completed, so I created an events table to track each step.
At the moment I have a SQL INSERT statement to add the information from
the form to the request table, but I do not know how I would insert a
record into the events table with same id from the request table. In
the events table I have an events ID which is an autonumber datatype
and an req_id with a number datatype, so I can have a relationship
between the two tables. Does anyone have an idea of how I would update
events table with a new record containing the request id from the
submitted request? Below is the code I have so far for the command
button.

Private Sub cmd_request_Click()

Dim strsql


strsql = "INSERT INTO tbl_request (fname, lname, geid, m_fname,
m_lname, m_geid, platform,"
strsql = strsql & " app, req_type, status, isa, submitted)
VALUES ('" & Me.cbo_fname & "',"
strsql = strsql & " '" & Me.cbo_lname & "', '" & Me.txt_geid &
"', '" & Me.cbo_m_fname & "',"
strsql = strsql & " '" & Me.cbo_m_lname & "', '" &
Me.txt_m_geid & "', '" & Me.cbo_platform & "',"
strsql = strsql & " '" & Me.cbo_app & "', '" & Me.cbo_type &
"', 'Open', '" & fOSUserName() & "',"
strsql = strsql & " '" & Me.lbl_datetime.Caption & "')"

DoCmd.RunSQL strsql, False


End Sub
 
D

David Lloyd

Shawn:

I am assuming that the req_id from the Request table is an autonumber field
(I don't see it in your INSERT statement, so my guess is it probably is).
By executing your INSERT statement on a database connection that utilizes
the Jet OLE DB version 4.0 provider, you can make use of an Select
@@Identity query to find the value of the req_id assigned to the newly
inserted record in the Request table. This works for Jet 4.0 databases,
which I believe you can create in Access 2000 and new versions of Access.
You did not say what version of Access you were using.

For example:

Function InsertRecords()
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim req_id as Long

'This connection uses an ADO connection utilizing the Jet OLE DB version
4.0 provider
Set cn = CurrentProject.Connection

strsql = "INSERT INTO tbl_request (fname, lname, geid, m_fname,
m_lname, m_geid, platform,"
strsql = strsql & " app, req_type, status, isa, submitted)
VALUES ('" & Me.cbo_fname & "',"
strsql = strsql & " '" & Me.cbo_lname & "', '" & Me.txt_geid &
"', '" & Me.cbo_m_fname & "',"
strsql = strsql & " '" & Me.cbo_m_lname & "', '" &
Me.txt_m_geid & "', '" & Me.cbo_platform & "',"
strsql = strsql & " '" & Me.cbo_app & "', '" & Me.cbo_type &
"', 'Open', '" & fOSUserName() & "',"
strsql = strsql & " '" & Me.lbl_datetime.Caption & "')"

'Insert the record using the ADO connection
cn.Execute strsql, , adCmdText + adExecuteNoRecords

'Get the req_id value from the Request table
rs.Open "Select @@Identity", cn, adOpenForwardOnly, adLockReadOnly,
adCmdText

'Only one value is returned
req_id = rs(0)

'Put code here to insert into the events table

Set cn = Nothing
Set rs = Nothing

End Function

The following KB article gives additional information on using the Select
@@Identity query.

http://support.microsoft.com/default.aspx?scid=kb;en-us;232144

--
David Lloyd
MCSD .NET
http://LemingtonConsulting.com

This response is supplied "as is" without any representations or warranties.


I created a form so people can input request information via series of
combo and text boxes and submit the request by clicking a command
button. The thing is that each request will have multiple steps before
they are completed, so I created an events table to track each step.
At the moment I have a SQL INSERT statement to add the information from
the form to the request table, but I do not know how I would insert a
record into the events table with same id from the request table. In
the events table I have an events ID which is an autonumber datatype
and an req_id with a number datatype, so I can have a relationship
between the two tables. Does anyone have an idea of how I would update
events table with a new record containing the request id from the
submitted request? Below is the code I have so far for the command
button.

Private Sub cmd_request_Click()

Dim strsql


strsql = "INSERT INTO tbl_request (fname, lname, geid, m_fname,
m_lname, m_geid, platform,"
strsql = strsql & " app, req_type, status, isa, submitted)
VALUES ('" & Me.cbo_fname & "',"
strsql = strsql & " '" & Me.cbo_lname & "', '" & Me.txt_geid &
"', '" & Me.cbo_m_fname & "',"
strsql = strsql & " '" & Me.cbo_m_lname & "', '" &
Me.txt_m_geid & "', '" & Me.cbo_platform & "',"
strsql = strsql & " '" & Me.cbo_app & "', '" & Me.cbo_type &
"', 'Open', '" & fOSUserName() & "',"
strsql = strsql & " '" & Me.lbl_datetime.Caption & "')"

DoCmd.RunSQL strsql, False


End Sub
 
Top