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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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
|
|||
|
|||
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 | |
|
|