Macro to assign ID Numbers

[03SCBeast]

I'm trying to replicate the mcrContract macro in the Entertainment "Running Microsoft Access 2000" by John Viescas to assign IDs to new persons without skipping and numbers. I believe I have the conditions and actions set up correctly but I can't figure out how to have the Form start macro. I'd like for the ID to be assigned when I begin typing in the first field or when I save the record.

More specifically, the macro should assign ID 1 if this is the first contract or the highest contract # + 1 if it isn't. I'm using DMax ans ISNull Functions for Expression and Conditions.

Any ideas?

 

[BobStubbs]

This is the code which I use to allocate Record IDs in one of my forms:

    Me.RecordNumber = DMax("[RecordNumber]", "tblMyTable") + 1

I use this in the code of an [Add New Record] button, like this (the rest of the code is the standard code generated by Access when you use the wizard to create the command button):

Private Sub btnNewRecord_Click()
On Error GoTo Err_btnNewRecord_Click

    DoCmd.GoToRecord , , acNewRec
    Me.RecordNumber = DMax("[RecordNumber]", "tblMyTable") + 1

Exit_btnNewRecord_Click:
    Exit Sub

Err_btnNewRecord_Click:
    MsgBox Err.Description
    Resume Exit_btnNewRecord_Click
    
End Sub

If you don't want to use a command button, you could add the code to the On Got Focus event of the first user editable text box. Example - suppose the first two fields displayed on your form are:
RecordNumber:
Enabled = False
Locked = True
TextField1:
Normal, editable text box.

Add this code to TextField1:

Private Sub TextField1_GotFocus()

If IsNull(Me.RecordNumber) Then
    Me.RecordNumber = DMax("[RecordNumber]", "tblMyTable") + 1
End If
End Sub

I hope this is useful.

Bob Stubbs

 

[mph1]

Instead of using a macro, try the following.

1. Table - create a feild and name it Invoice.
a. It's Data Type is Number.
b. Field Properties of the field Invoice
Field Size - Long Integer
Required - Yes

2. Form - create a bound text box from the field Invoice. In the Default Value of this control put
=GETINVOICE()
This will be in the properties of the field Invoice.

3. Create a new Module and enter the following:

PUBLIC FUNCTION GETNEXTINVOICE()
GetNextInvoice=Nz(DMax("[Invoice]", "[NameofTable]",+1,1)

I hope this helps.


An investment in knowledge always pays the best dividends.
by Benjamin Franklin
Autonumber Description - FAQ702-5106