Are you saying your business users understand code like:
Call OpenCon
rst.Open "tblEmployees", con, adOpenStatic, adLockReadOnly, adCmdTable
rst.MoveLast
lngEmployeeID = rst!EmployeeID

rst.Close
Set rst = Nothing
Call CloseCon


They must be very sophisticated users!

I was asking how you would explain WHY you are doing this in terms an
89-year old grandmother would understand.

For example, you might say something like (this is pure speculation, because
I still don't have any idea WHAT you are trying to accomplish):

"I want to collect information about my employees and what training they've
completed."

More info, please!

Regards

Jeff Boyce
Microsoft Office/Access MVP



"Ra" wrote in message
...
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.