How to generate an Automatic number in a Form?

[gimmyd]

How would I go about generating and displaying an automatic and distinct work order number each time a form is opened and before the information is saved to a table?

Regards,
JD

 

[JJOHNS]

Create the field in your table and set the data type to autonumber. You'll probably want to make that your primary key as well.

 

[tunsarod]

If you don't want to use the Autonumber feature then you will have to create a permanent variable to store a value that can be read by your form every time it opens and updated every time you save a record with the current value as an ID.

Here are a few ideas to get you going. The names are suggestions of course.

Create a table called: tblValues

Add a field called: RecNum
type: autonumber
required: Yes
allow duplicates: No
validation rule: = 1

This will create a table with one record which you can populate with as many permanent variables as you need.

Add a field called: intIncrement
type: Long Integer

Save the table.

In the header of your form's code module add:
Dim UniqueID As Long

In the On Open Event code for the form add:

UniqueID = DLookup("intIncrement","tblValues","RecNum = 1&quot


The following code will increment the value held in the intIncrement field in tblValues. If you use a command button to Save the record then you could place the code in the button's Click event.


DoCmd.RunSQL "UPDATE tblValues SET " _
& "tblValues.intIncrement = [intIncrement]+1" _
& " WHERE (((tblValues.RecNum)=1));"


Regards
Rod

 

[rajeessh]

The more simple will be this

Before forming a insert startment or Rst.AddNew

Id = NZ(DMax("Id","Table_Name&quot,0) + 1

So u get always the maximum id if u are not using the AutoNumber