table not updating

[Ra]

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.

 

[Jeff Boyce]

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

 

[John W. Vinson]

I am adding records to tblEmployees using below, no connection:
strAddRecord="INSERT INTO tblEmployees() VALUES ()"
DoCmd.RunSQL strAddRecord

So you're adding no data into no fields????

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,

Why would you expect it to do so? You're not specifying the value to be
inserted.

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.

If you're trying to have Autonumbers synchronized between two tables - and
this is really unclear to me, since you're evidently not posting the actual
code you're using! - you're on the wrong track. Your tblB should NOT have an
autonumber EmployeeID; it should instead have a Long Integer. Also, rather
than opening a recordset, why not just run an Append query from tblEmployees
into tblB???

John W. Vinson [MVP]

 

[Ra]

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]

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]

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.

 

[Pat Hartman]

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]

Pat,

Thank you for your reply. I am trying to understand how this works, so
please bear with me. I am using JET tables, and my understanding was that a
new record was added in PK order, in my case EmployeeID. I am going to change
the structure of strAddEmployee by adding an OrderBy clause to it, to do the
ordering as you suggest.
What is confusing is that it works most of the time, however not all the time.

Another question is if I assign lngEmployeeID between rst.AddNew and
rst.Update, how is that going to have the last value, since the Update was
not performed?

Thanks again, that was helpful!

 

[Pat Hartman]

I thought I explained why you don't always get the record you expect.
Unless you specifically sort a recordset, the rows will not necessarily be
returned in the same order each time the query runs. That is why sometimes
the "last" record is the one you want and sometimes it is not. Using
..MoveLast or DLast() are both unreliable methods of retrieving the most
recently assigned autonumber. DMax() is better but in a multi-user
environment, you still run the risk of retrieving an autonumber assigned by
a different user. The only sure method is the one I mentioned where you use
..AddNew and retrieve the autonumber within the framework of .AddNew and
..Update.

With Jet tables, the autonumber is assigned as soon as the record is
dirtied. Notice that when you open a form to a new record, the autonumber
field says "autonumber". As soon as you type the first character in the
form, Jet assigns the autonumber. The record has not yet been saved. In
fact, if you don't save the record, you will "loose" the autonumber because
they cannot be reused even if no subsequent record is added.

With SQL Server (and DB2, Oracle, etc), Access doesn't assign the autonumber
value, the server does. So, Access actually has to send the append query to
the server and the server assigns the autonumber before it inserts the row.

I question your design. You seem to have a 1-1 relationship that probably
should be 1-m. If it is 1-1, you can create a query that joins the two
tables and use that as the recordsource for your form. As long as you put a
value in at least 1 child-side field, Jet will insert both rows and will
put the proper FK value in the child table - Make sure that you have
relationships defined in the relationship window for this to work.

If the relationship is 1-m, you should be using a main form for the 1-side
and a subform for the many-side and again, no code is required as long as
you use bound forms. I am not sure why you are not using bound forms. You
are giving up one of the most powerful features of Access and creating tons
of unnecessary work for yourself.