I thought the code was self explanatory. I have a form (Me, below) which I
use to enter data into 2 tables (tblEmployees and tblOCCP). The users enter
data into text boxes in the form and push Enter when done entering.
Some of the data is employee specific and goes into tblEmployees, other data
pertains to training and seniority and goes into tblOCCP.
I use "Private Sub AddEmployee" to insert employee data into tblEmployees
(the code is below, except error handler), and "Private Sub AddOCCP" to
insert the other set of information into tblOCCP. I insert one record at a
time.
I need to sync the two tables, so I use lngEmployeeID which I set to the
rst!EmployeeID (AutoNumber) in tblEmployees, then use it as EmployeeID in
tblOCCP.
Once in a while I see in tblOCCP that EmployeeID repeats itself for 4-5
records, then it jumps back into correct sequence. In other words
lngEmployeeID does not get the correct EmployeeID number.
I am trying to find out why the code below:

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

does not work all the time.
Thank you in advance.



"Jeff Boyce" wrote:

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.