Alternative to AutoNumber 1

[manfran]

I'm terrible at writing expressions. I'm looking for a way to create a sequentially incrementing ID number in a form. I want to get a new sequential number every time I add a new record. I believe I can use the DMAX function, but I'm not sure how to word it.

 

[Carol57]

Try the following code:

Private Sub Form_BeforeInsert(Cancel As Integer)
On Error GoTo Form_BeforeInsert_Err

' Set Purchase Order No to "Max" + 1
Forms!PurchaseOrder!PurchaseOrderNo = DMax("[PurchaseOrderNo]", "Purchasing&quot + 1
If (IsNull(Forms!PurchaseOrder!PurchaseOrderNo)) Then
' If result was Null (this is the first Purchase Order), set to 1
Forms!PurchaseOrder!PurchaseOrderNo = 1
End If


Form_BeforeInsert_Exit:
Exit Sub

Form_BeforeInsert_Err:
MsgBox Error$
Resume Form_BeforeInsert_Exit

End Sub

-------

Modify the code to suit your needs. If you want the numbering to start at a different number other than 1, then modify the following line of code to read the 1st number you want the records to start at:

Forms!PurchaseOrder!PurchaseOrderNo = 1

 

[danvlas]

= Nz(DMax("[PurchaseOrderNo]", "Purchasing&quot, 0) + 1

To start from 1000, change 0 to 999.

Not a very reliable solution in a multi-user environment, if you want a bullet-proof solution do a search for Michael Red's FAQ on the subject.


HTH




Daniel Vlas
Systems Consultant

http://www.geocities.com/danvlas/AutoMail.html

 

[manfran]

OK. I tried using both of these, but I couldn't figure out one part. You both include the word Purchasing and I can't figure out the eqivalent value in my application. Here's what I have:
A form named Contacts. A field named ContactID. I want to use DMax to return an integer value. Please complete this for me. By the way should it say (Cancel As Integer) if I need the result to be an integer? Thanks for putting up with novice questions.

' Set ContactID to "Max" + 1
Forms!Contacts!ContactID = DMax("[ContactID]", "?&quot + 1

 

[jrbarnett]

Try:

Forms!Contacts!ContactID = DMax("[ContactID]", "table in which contacts are stored&quot + 1

I would, however, as soon as this code has been added, insert a value into the table just so it won't get used again. Very important in a multi user situation so you won't get the possibility of two contacts with the same ID.

The (Cancel As Integer) is a parameter to the BeforeInsert event that allows code to stop it running by setting the variable Cancel to True.
It isn't needed in your application unless there is the possibility of somebody adding a contact and not saving it.

John

 

[manfran]

Thanks for all the help. What I finally realized was that the existing ID numbers had to be taken into consideration. Once I adjusted the code to reflect them in each form it worked like a charm.

 

[BLBurden]

Private Sub Form_BeforeInsert(Cancel As Integer)
Forms![SIPP DATABASE-Form]!ID1 = DMax("[ID1]", "APPEND&quot + 0

How do I increase the number to 1, 2, 3, 4, 5 etc.. for the day I am inputing the records. But when the date change to the next day , I need to start over with the number of inputs for that day. 1, 2 3, 4, 5 etc. Is this possible.


BLB
Always Grateful