How to Incremenet without Autonumber ?

[glenn69]

Hello,

We are running a database that is accessed by up to 4 users at any one time. The main table (tblCaseData) in the database has a primary key (JobNumber - long integer) that is incremented without the use of Autonumber. The below code updates that number when a new record is saved :

Set db = CurrentDb()
Set recordlist = db.Openrecordset("SELECT Max(tblCaseData.JobNumber) AS MaxOfJobNumbers FROM tblCaseData")

JobNumber = (recordlist!MaxOfJobNumbers + 1)

recordlist.Close
Set recordlist = Nothing


Our problem arises when multiple users are keying info. One user will use a JobNumber before the other and the save record will not be allowed.

What is the proper way to accomplish this?

Thank You

 

[EdFred]

Here's what I did for invoicing on my system:

Private Sub Form_BeforeUpdate(Cancel As Integer)

Dim db As Database
Dim strSQL As String
Dim rs As Recordset


If IsNull(Me.Customer_PO) Or IsNull(Me.Invoice_Employee_ID) Or IsNull(Me.Customer_ID) Then
Exit Sub
Else
If IsNull(Me.Invoice_Number) Then
Set db = CurrentDb
strSQL = "Select * From [Invoices]"
Set rs = db.OpenRecordset(strSQL)

rs.MoveLast

Me.Invoice_Number = rs![Invoice Number] + 1

rs.Close
db.Close
End If
End If

End Sub


Doing it this way it assigns the invoice number/job number when the record gets updated as opposed to when it is opened - which seems like it may be the case on your end. I've never had a problem when more than one of us is entering an invoice.at the same time.

 

[Fifty2One]

if they need the jobnumber for other reasons while they complete the form then save the number when they open the form so it is already taken - basically then they will be editing the entry rather than creating it...
if they dont need the jobnumber for any other reason then assign it after completion of the form...

 

[RuralGuy]

There is a *very tiny* window for duplication if you assign the number in the BeforeUpdate event of the form.

 

[MicroE]

Another way is to use DMax:
Me!Recordnumber = Nz(DMax("[RecordNumber]", "[Your Table]"), 0) + 1

 

[The_Doc_Man]

OK, here is a question you need to answer...

Does it matter if your numbering isn't contiguous?

If not, use autonumber and live with the gaps.

Be honest, now... does it really matter that the key numbers always increment by the same amount? You can determine a record count easily enough without using the "key number on most recent record" as a count. There is a Count() or DCount(,,) for that.

Does a customer - or anyone else for that matter - NEED to know that their number is one greater than the previous number? Does that have to happen? (Mike Smart, I know you would laugh on this one...) This is a case where you might think you want a meaningful key - but do you need one?

Autonumber will do everything else smoothly - but not contiguously.

 

[glenn69]

To answer the question of whether it matters that the numbers are contiguous...Yes, because it determines invoice numbers. The IRS frowns upon missing invoice numbers when declaring income. So it is important.

 

[GaryPanic]

Ed frd answer is the one I have used and in 8 years i think I had about 6 or 67 duplicates when 2 persons do the same thing at the same tiem or one PC responds slower than another -
given that we are talking about 30,000 invocies - I am happy with that

 

[GaryPanic]

typ0 6 or 7

 

[RuralGuy]

Again, if you use MicroE's code (post #6) in the BeforeUpdate event of the form, there is very little change of a duplicate. You still need to plan for a duplicate and retry if you get error 3022. If you want the user to see the number then put the same code in the BeforeInsert event of the form but the actual number may be different by the time the BeforeUpdate event occurs so the code *must* be in the BeforeUpdate event as well.