invoice number without using autonumber

[lymi6977]

In an earlier thread, someone suggested using this code to figure the Invoice #, instead of using Autonumber. My question is where does this code go?

Dim varID As Variant

With recordset
.MoveLast 'Moves to the last record
varID = ![ID] 'Gets the number first
varID = varID + 1 'Increment the no.
.AddNew 'Add a new record
![ID] = varID 'Add the new number to start of
record
End With

Thanks for your help.
lymi6977

 

[Remou]

The code would probably go on a command button that said something like 'Add Record'.
There is an FAQ by MichaelRed on unique keys that you may wish to read:
Why AutoNumber shouldn't be used in MultiUser databases (And How to ge the Unique Number)
faq700-184

 

[fneily]

Here are three basic ways to create your own autonumber. The second option is like what you presented but without coding.

Let’s say you want the numbering to begin with a different number other then 1. Let’s say 57. Create your table with NO data and no primary key. Have a field called ID and make the data type Number. Save your table. Select your table, right click and select Copy. Then right click and select Paste. Give it a new table name and select Structure Only. Open up this new table and create one record with ID the starting number one less then the one you want, in this case 56. Close this table. Open the first table and now make the ID field an Autonumber type. Close the table. Create an append query from the second table appending the one record to the first table. Now open the first table and add another record. It will automatically be 57. Delete record number 56.

Let’s say you want to create your own autonumber field. Create a table with a field called ID. Create a form for that table. On the form, go to design view and click on the text box of the ID field, and open the properties sheet. Click on Default value and enter:
=Dmax(“[ID]”,”tablename”)+1
Now when you go to the next new record, it will be incremented by 1.

Let’s say you want an autonumbered field by Microsoft but want some characters in front of the number like mc001, mc002, etc. Go to design view, click on the autonumber field and in the Format box type “mc”00
This places mc next to 2 zeros and then tacks on the autonumber.

 

[ssatech]

Or the on-click event of your command button to add a new record. The ID being your Invoice No. Use a starting Invoice No and the code increments the Invoice No when you add a new record.


Private Sub cmdNew_Click()
On Error GoTo Err_cmdNew_Click


DoCmd.GoToRecord , , acNewRec
Me.ID = 1 + DMax("ID", "tblName")


Exit_cmdNew_Click:
Exit Sub

Err_cmdNew_Click:
MsgBox Err.Description
Resume Exit_cmdNew_Click

End Sub