Incrementing Numbers (Not DMax+1)

[Matty]

Hi,

I'm developing a medical database that keeps track of referrals from doctors. Each referral record entered into the database gets a unique Referral Number. The way I'm setting that incrementing number is by taking the maximum Referral Number from the Referrals table and adding one to it.

DMax("[RefNumber]","Sleep - Referral")+1

But I've hit a snag. Very soon we may have concurrent users entering referrals, meaning the same referral number will be used (the function i'm using adds one to the last SAVED Referral Number, so both unsaved records will be assigned the same Referral Number). This will give a duplicate key error, something I'm obviously not wanting to see.

Can anyone think of a way to set incrementing Referral Numbers, without using autonumber fields or the DMax function?

 

[lipin]

increment idea

I have a report that prints on a button. Each report has a unique and incrementing number, like an invoice number. It is a textbox that is linked to a table with only 1 field and always only has one record.
Each time a user (I have multiple users also) clicks the print report button on the form, the table updates with the next number. The same idea should work with a form textbox I suppose.

 

[Matty]

Exactly.

I just figured that one out the same time your post came up. I have a Save button on the Referrals form, so rather than assign the Referral Number when a new record is created (i.e. when the user goes to a fresh record), it gets set when the save button is clicked. So that record only gets a Referral Number the instant before it actually saves.

For future reference, the code looks a little something like this:

If Me.NewRecord = True Then
If DCount("[RefNumber]", "Sleep - Referral") = 0 Then
Me!txtRefNumber = 1
Else
Me!txtRefNumber = DMax("[RefNumber]", "Sleep - Referral") + 1
End If
End If

I appreciate the help.

 

[John.Woody]

Thats better but technically it is still possible for 2 users to save and get the same number. Chance in a million but if it can go wrong it ..... I use the following:

Public Sub NextInvoiceNo()
On Error Resume Next

If IsNull(DMax("InvoiceNo", "Invoice")) Then
Forms!F_Invoice!InvoiceNo = 1
Else
Forms!F_Invoice!InvoiceNo = DMax("InvoiceNo", "Invoice") + 1
End If

Forms!F_Invoice.Refresh
If Err = 3022 Then
Forms!F_Invoice!InvoiceNo = ""
Forms!F_Invoice.Refresh
NextInvoiceNo
End If

End Sub


The field InvoiceNo in the table is indexed Yes(No Duplicates) which generates error 3022 if someone tries to assign the duplicate number so I reset the invoiceNo and try again.


Hope you find this useful.

Who is it that keeps telling us "Life would be simple if it wasn't for users!"? How true

 

[Matty]

Thanx, I think I'll give that one a try. I'm not gonna have tons of users on this system (maybe only 2 or 3), but I may as well prepare for the worst. Knowing my luck, the worst is likely to happen...

I guess I never thought to call the same sub within itself. I probably assumed that I'd get a lovely infinite loop, but from reading this piece of code, it looks like that wouldn't be the case.

Thanx again for the revision.