Sorry about the lack of info, please see below:
Private Sub AddEmployee()
On Error GoTo Err_AddEmployee

Dim strAddEmployee As String
Dim rst As New ADODB.Recordset
'update tblEmployees
strAddEmployee = "INSERT INTO tblEmployees(GMIN, EmployeeName, Shift, PltSD,
CorpSD, " _
& " STSD, Status, SupervisorID) " _
& "VALUES (" & Me.txtGMIN.Value & ", '" &
Me.txtEmployeeName.Value & "', " _
& Me.txtShift.Value & ", '" & Me.txtPltSD.Value & "', '" &
Me.txtCorpSD & "', '" _
& Me.txtSTSD.Value & "', '" & Me.txtStatus.Value & "', " &
Me.cboSup.Column(0) & ")"
'MsgBox strAddEmployee
DoCmd.RunSQL strAddEmployee

Call OpenCon
rst.Open "tblEmployees", con, adOpenStatic, adLockReadOnly, adCmdTable
rst.MoveLast
lngEmployeeID = rst!EmployeeID

rst.Close
Set rst = Nothing
Call CloseCon

and the second table:
Private Sub AddOCCP()
On Error GoTo Err_AddOCCP

Dim strAddOCCP As String

'update tblOCCP
strAddOCCP = "INSERT INTO tblOCCP(EmployeeID, OCCP, OCCPDescription, Action,
ActDate) " _
& "VALUES (" & lngEmployeeID & ", '" & Me.txtOCCP.Value &
"', '" _
& Me.txtOCCPDesc.Value & "', '" & Me.txtAction.Value & "',
'" _
& Me.txtActionDate.Value & "')"
'MsgBox strAddOCCP
DoCmd.RunSQL strAddOCCP

There is no Autonumber in tblOCCP, rather I use lngEmployeeID to sync the
tables.

"Jeff Boyce" wrote:

You've described "how" you are attempting to do something (e.g.,
"INSERT...", "add records to a second table...", ...).

We don't know enough about "what" you are doing ... a more specific
description of the underlying business need (i.e., what will having these
records in these tables allow your business users to accomplish) may lead to
more specific suggestions.

Regards

Jeff Boyce
Microsoft Office/Access MVP


"Ra" wrote in message
...
I am adding records to tblEmployees using below, no connection:
strAddRecord="INSERT INTO tblEmployees() VALUES ()"
DoCmd.RunSQL strAddRecord
and this works fine. I then open the same table, over connection:
Call ConOpen
rst.Open "tblEmployees", con, adOpenStatic, adReadOnly
rst.MoveLast
lngEmployeeID=rst!EmployeeID
Then, I add records to a second table (tblB) using the lngEmployeeID as
id.
Same syntax, no connection:
strUpdateB = "INSERT INTO tblB() VALUES ()"
DoCmd.RunSQL strUpdateB
The issue is that the lngEmployeeID does not always update to the last
record, and at that point it uses the previous lngEmployeeID for the next
4-5
entries in tblB. Then it jumps to the correct id and everything goes back
to
normal.
So when I pull up an employee name in this range of id's, I only get the
info from tblEmployees and nothing from tblB, due to different is numbers.