A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

table not updating



 
 
Thread Tools Display Modes
  #1  
Old February 6th, 2008, 04:43 PM posted to microsoft.public.access.tablesdbdesign
RA
external usenet poster
 
Posts: 191
Default table not updating

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.
  #2  
Old February 6th, 2008, 07:13 PM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default table not updating

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.



  #3  
Old February 6th, 2008, 07:24 PM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default table not updating

On Wed, 6 Feb 2008 07:43:02 -0800, Ra wrote:

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]
  #4  
Old February 6th, 2008, 08:12 PM posted to microsoft.public.access.tablesdbdesign
RA
external usenet poster
 
Posts: 191
Default table not updating

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.




  #5  
Old February 7th, 2008, 12:08 AM posted to microsoft.public.access.tablesdbdesign
Jeff Boyce
external usenet poster
 
Posts: 8,621
Default table not updating

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.






  #6  
Old February 7th, 2008, 04:17 PM posted to microsoft.public.access.tablesdbdesign
RA
external usenet poster
 
Posts: 191
Default table not updating

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.






  #7  
Old February 7th, 2008, 05:35 PM posted to microsoft.public.access.tablesdbdesign
Pat Hartman
external usenet poster
 
Posts: 392
Default table not updating

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.






  #8  
Old February 7th, 2008, 07:18 PM posted to microsoft.public.access.tablesdbdesign
RA
external usenet poster
 
Posts: 191
Default table not updating

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" wrote:

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.






  #9  
Old February 7th, 2008, 08:19 PM posted to microsoft.public.access.tablesdbdesign
Pat Hartman
external usenet poster
 
Posts: 392
Default table not updating

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.


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

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.








 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 02:13 AM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.