Auto-generation of coded primary key

[jeh]

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

 

[BruceM]

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

 

[Klatuu]

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]

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

 

[BruceM]

Why don't you write the key directly to the field when Me.NewRecord is true?

 

[Klatuu]

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