A description of the process would certainly help. But I can explain the ID
issue without it. "Last" has no real meaning in a relational world. "Last"
is simply the last record in a recordset but you are referring directly to
the table so Access is going to create the recordset for you and it will
create it in what ever order it chooses which is not necessarily (as you can
see) what you expect. Tables opened in data sheet view are usually
presented in primary key order (at least immediately after they are
compacted) which fools people into expecting this behavior. But in
actuality, if you want a recordset to be in a particular order, you MUST use
a query with an Order By clause. When records are added to a relational
table they are "generally" added at the physical end of the data for Jet
tables. That is not at all the case for SQL Server or other RDBMS tables.
For server based tables, rows are added where ever there is free space which
could be physically any place in the table space. A better method of
retrieving the "last" record is to use DMax() which will retrieve the record
with the highest key value - which is probably what you had in mind since
the key is an autonumber. The BEST method is to use DAO or ADO and the
..AddNew method. In that case, you will have access to the ACTUAL key that
you just inserted and you won't have to worry about picking up a key
inserted by a different user as can happen with the DMax() method. If your
tables are jet, place your "lngEmployeeID = rst!EmployeeID" statement
somewhere between the .AddNew and .Update methods. If your tables are SQL
server, you will need to use a different method to retrieve the identity
column because SQL Server can't generate the autonumber until you actually
run the .update method. Post back if you need that code.


"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.