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
|
|||
|
|||
Auto-generation of coded primary key
The primary key of my Tbl_Main is a code rather than an auto-number.
The key for the next new record is the next item in the logical key sequence. When adding new records to Tbl_Main I use a VBA subroutine to write the new key to the screen via a Message Box. I then cut and paste this to the key field in Frm_Main (which has DataEntry = yes). I want to replace this cumbersome and error-prone process by something that will cause the new key to automatically appear in Frm_Main.KeyField. I had thought that I could do this by copying the newly calculated key to the next record after the final one in Tbl_Main. The code I'm using is: function NextKey() Dim db As DAO.Database ' Database name Dim rst As DAO.Recordset ' Recordset name Dim strNxtId As String Dim tdf As DAO.TableDef ' Table ' Set table and recordset Set db = CurrentDb() Set tdf = db.TableDefs("Tbl_Main") Set rst = tdf.OpenRecordset ' 30 or so lines of code to evaluate strNxtId. ' The code includes reading from tdf via rst. MsgBox strNxtId ' To be deleted when this routine works ' stop ' Everything works correctly to this point. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function The VBA routine works and is fully tested to the MsgBox stage with the "stop" instruction implemented. The next three lines are designed to transfer the new primary key to the next blank record in Tbl_Main. However when I comment "stop" out the run crashes at the "rst.Fields(0)" line with the message: "Run time Error 3020, Update or CancelUpdate without AddNew or Edit". Could someone suggest how I can get round this or, as an alternative, a better way to do what I'm trying to do? I'm using Access 2000, the function is called by a RunCode instruction within a Macro and the crash occurs before Frm_Main is opened. Hope I've made myself clear John |
#2
|
|||
|
|||
Auto-generation of coded primary key
It would help to know what sort of sequence you are seeking. A simple
incremented number can be calculated as the default value of your field: =DMax("YourField","YourTable")+1 Could you add 1 (or whatever) to the number you calculate, and use the result as the field value? If Me.NewRecord Then Me.YourField = YourCalculation +1 End If "jeh" wrote in message ups.com... The primary key of my Tbl_Main is a code rather than an auto-number. The key for the next new record is the next item in the logical key sequence. When adding new records to Tbl_Main I use a VBA subroutine to write the new key to the screen via a Message Box. I then cut and paste this to the key field in Frm_Main (which has DataEntry = yes). I want to replace this cumbersome and error-prone process by something that will cause the new key to automatically appear in Frm_Main.KeyField. I had thought that I could do this by copying the newly calculated key to the next record after the final one in Tbl_Main. The code I'm using is: function NextKey() Dim db As DAO.Database ' Database name Dim rst As DAO.Recordset ' Recordset name Dim strNxtId As String Dim tdf As DAO.TableDef ' Table ' Set table and recordset Set db = CurrentDb() Set tdf = db.TableDefs("Tbl_Main") Set rst = tdf.OpenRecordset ' 30 or so lines of code to evaluate strNxtId. ' The code includes reading from tdf via rst. MsgBox strNxtId ' To be deleted when this routine works ' stop ' Everything works correctly to this point. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function The VBA routine works and is fully tested to the MsgBox stage with the "stop" instruction implemented. The next three lines are designed to transfer the new primary key to the next blank record in Tbl_Main. However when I comment "stop" out the run crashes at the "rst.Fields(0)" line with the message: "Run time Error 3020, Update or CancelUpdate without AddNew or Edit". Could someone suggest how I can get round this or, as an alternative, a better way to do what I'm trying to do? I'm using Access 2000, the function is called by a RunCode instruction within a Macro and the crash occurs before Frm_Main is opened. Hope I've made myself clear John |
#3
|
|||
|
|||
Auto-generation of coded primary key
This section of code will never work. You move to the Last record, then to
the next record. You are now at End Of File. This is not like navigating in a form where a new record is created. The Move methods are not necessary. The error message you are getting tell exactly what the problem is. To add a new record, you use the .AddNew method and to modify an existing record the ..Edit method. In both cases, you need to use the .Update method to apply your changes. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function Corrected code: With rst .AddNew .Fields(0) = strNxtId .Update End With "jeh" wrote: The primary key of my Tbl_Main is a code rather than an auto-number. The key for the next new record is the next item in the logical key sequence. When adding new records to Tbl_Main I use a VBA subroutine to write the new key to the screen via a Message Box. I then cut and paste this to the key field in Frm_Main (which has DataEntry = yes). I want to replace this cumbersome and error-prone process by something that will cause the new key to automatically appear in Frm_Main.KeyField. I had thought that I could do this by copying the newly calculated key to the next record after the final one in Tbl_Main. The code I'm using is: function NextKey() Dim db As DAO.Database ' Database name Dim rst As DAO.Recordset ' Recordset name Dim strNxtId As String Dim tdf As DAO.TableDef ' Table ' Set table and recordset Set db = CurrentDb() Set tdf = db.TableDefs("Tbl_Main") Set rst = tdf.OpenRecordset ' 30 or so lines of code to evaluate strNxtId. ' The code includes reading from tdf via rst. MsgBox strNxtId ' To be deleted when this routine works ' stop ' Everything works correctly to this point. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function The VBA routine works and is fully tested to the MsgBox stage with the "stop" instruction implemented. The next three lines are designed to transfer the new primary key to the next blank record in Tbl_Main. However when I comment "stop" out the run crashes at the "rst.Fields(0)" line with the message: "Run time Error 3020, Update or CancelUpdate without AddNew or Edit". Could someone suggest how I can get round this or, as an alternative, a better way to do what I'm trying to do? I'm using Access 2000, the function is called by a RunCode instruction within a Macro and the crash occurs before Frm_Main is opened. Hope I've made myself clear John |
#4
|
|||
|
|||
Auto-generation of coded primary key
Thanks Klatuu. That's cleared up a bad misunderstanding I had but
(of course!) has led straight to the next problem. Having generated the new record in Tbl_Main, with the correct key, how can I persuade Frm_Main to open with the new key showing in Frm_Main.KeyField? I need to do that in order to populate the rest of the fields in the new record. I assumed I could achieve this by calling DoCmd.OpenForm "Frm_Main",,,strCrit where strCrit = (rst.Fields(0) = strNxtID) as the final statement of function NextKey, but my tests on a simple db just open the form and instantly close it again as the function completes. The only alternative I can think of is to call the function within a macro and follow it with a call of OpenForm. However in that case how can I get the criterion either into the opening call or into the underlying query? John Klatuu wrote: This section of code will never work. You move to the Last record, then to the next record. You are now at End Of File. This is not like navigating in a form where a new record is created. The Move methods are not necessary. The error message you are getting tell exactly what the problem is. To add a new record, you use the .AddNew method and to modify an existing record the .Edit method. In both cases, you need to use the .Update method to apply your changes. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function Corrected code: With rst .AddNew .Fields(0) = strNxtId .Update End With "jeh" wrote: The primary key of my Tbl_Main is a code rather than an auto-number. The key for the next new record is the next item in the logical key sequence. When adding new records to Tbl_Main I use a VBA subroutine to write the new key to the screen via a Message Box. I then cut and paste this to the key field in Frm_Main (which has DataEntry = yes). I want to replace this cumbersome and error-prone process by something that will cause the new key to automatically appear in Frm_Main.KeyField. I had thought that I could do this by copying the newly calculated key to the next record after the final one in Tbl_Main. The code I'm using is: function NextKey() Dim db As DAO.Database ' Database name Dim rst As DAO.Recordset ' Recordset name Dim strNxtId As String Dim tdf As DAO.TableDef ' Table ' Set table and recordset Set db = CurrentDb() Set tdf = db.TableDefs("Tbl_Main") Set rst = tdf.OpenRecordset ' 30 or so lines of code to evaluate strNxtId. ' The code includes reading from tdf via rst. MsgBox strNxtId ' To be deleted when this routine works ' stop ' Everything works correctly to this point. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function |
#5
|
|||
|
|||
Auto-generation of coded primary key
Why don't you write the key directly to the field when Me.NewRecord is true?
"jeh" wrote in message oups.com... Thanks Klatuu. That's cleared up a bad misunderstanding I had but (of course!) has led straight to the next problem. Having generated the new record in Tbl_Main, with the correct key, how can I persuade Frm_Main to open with the new key showing in Frm_Main.KeyField? I need to do that in order to populate the rest of the fields in the new record. I assumed I could achieve this by calling DoCmd.OpenForm "Frm_Main",,,strCrit where strCrit = (rst.Fields(0) = strNxtID) as the final statement of function NextKey, but my tests on a simple db just open the form and instantly close it again as the function completes. The only alternative I can think of is to call the function within a macro and follow it with a call of OpenForm. However in that case how can I get the criterion either into the opening call or into the underlying query? John Klatuu wrote: This section of code will never work. You move to the Last record, then to the next record. You are now at End Of File. This is not like navigating in a form where a new record is created. The Move methods are not necessary. The error message you are getting tell exactly what the problem is. To add a new record, you use the .AddNew method and to modify an existing record the .Edit method. In both cases, you need to use the .Update method to apply your changes. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function Corrected code: With rst .AddNew .Fields(0) = strNxtId .Update End With "jeh" wrote: The primary key of my Tbl_Main is a code rather than an auto-number. The key for the next new record is the next item in the logical key sequence. When adding new records to Tbl_Main I use a VBA subroutine to write the new key to the screen via a Message Box. I then cut and paste this to the key field in Frm_Main (which has DataEntry = yes). I want to replace this cumbersome and error-prone process by something that will cause the new key to automatically appear in Frm_Main.KeyField. I had thought that I could do this by copying the newly calculated key to the next record after the final one in Tbl_Main. The code I'm using is: function NextKey() Dim db As DAO.Database ' Database name Dim rst As DAO.Recordset ' Recordset name Dim strNxtId As String Dim tdf As DAO.TableDef ' Table ' Set table and recordset Set db = CurrentDb() Set tdf = db.TableDefs("Tbl_Main") Set rst = tdf.OpenRecordset ' 30 or so lines of code to evaluate strNxtId. ' The code includes reading from tdf via rst. MsgBox strNxtId ' To be deleted when this routine works ' stop ' Everything works correctly to this point. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function |
#6
|
|||
|
|||
Auto-generation of coded primary key
You can postition your form on the new record using the key you just created.
With rst .AddNew .Fields(0) = strNxtId .Update End With Set rst = Me.RecordsetClone rst.FindFirst "[MyKeyField] = '" & strNxtID & "'" If Not rst.MoMatch Then Set Me.Bookmark = rst.Bookmark End If Set rst = Nothing "jeh" wrote: Thanks Klatuu. That's cleared up a bad misunderstanding I had but (of course!) has led straight to the next problem. Having generated the new record in Tbl_Main, with the correct key, how can I persuade Frm_Main to open with the new key showing in Frm_Main.KeyField? I need to do that in order to populate the rest of the fields in the new record. I assumed I could achieve this by calling DoCmd.OpenForm "Frm_Main",,,strCrit where strCrit = (rst.Fields(0) = strNxtID) as the final statement of function NextKey, but my tests on a simple db just open the form and instantly close it again as the function completes. The only alternative I can think of is to call the function within a macro and follow it with a call of OpenForm. However in that case how can I get the criterion either into the opening call or into the underlying query? John Klatuu wrote: This section of code will never work. You move to the Last record, then to the next record. You are now at End Of File. This is not like navigating in a form where a new record is created. The Move methods are not necessary. The error message you are getting tell exactly what the problem is. To add a new record, you use the .AddNew method and to modify an existing record the .Edit method. In both cases, you need to use the .Update method to apply your changes. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function Corrected code: With rst .AddNew .Fields(0) = strNxtId .Update End With "jeh" wrote: The primary key of my Tbl_Main is a code rather than an auto-number. The key for the next new record is the next item in the logical key sequence. When adding new records to Tbl_Main I use a VBA subroutine to write the new key to the screen via a Message Box. I then cut and paste this to the key field in Frm_Main (which has DataEntry = yes). I want to replace this cumbersome and error-prone process by something that will cause the new key to automatically appear in Frm_Main.KeyField. I had thought that I could do this by copying the newly calculated key to the next record after the final one in Tbl_Main. The code I'm using is: function NextKey() Dim db As DAO.Database ' Database name Dim rst As DAO.Recordset ' Recordset name Dim strNxtId As String Dim tdf As DAO.TableDef ' Table ' Set table and recordset Set db = CurrentDb() Set tdf = db.TableDefs("Tbl_Main") Set rst = tdf.OpenRecordset ' 30 or so lines of code to evaluate strNxtId. ' The code includes reading from tdf via rst. MsgBox strNxtId ' To be deleted when this routine works ' stop ' Everything works correctly to this point. rst.MoveLast rst.MoveNext rst.Fields(0) = strNxtId end function |
Thread Tools | |
Display Modes | |
|
|