Automatically numbering a record (not autonumber)

[Chad1984]

In my database, the form needs to assign a case number to new records; currently I'm using a dmax and adding 1 to come up with the new value. This is working fine (although I'm open to suggestions on how to do that differently), but here's the real issue: getting it to display the value. Currently I'm using BeforeInsert. I suppose it may also be possible to use BeforeUpdate with an "If Me.NewRecord" It works beautifully on my machine, but the client says it does not work on hers. We both use Access 2003, and she's too far away for me to play around with thedatabase on one of her machines. Does anyone have any thoughts why the method might not fire? For me, the household ID fills in (in a disabled field, so the user cannot and does not try to change it) as soon as I start typing in any of the fields. According to her, she has to completely fill in a record, then change records and change back before it will display. (That sounds like Form_Current to me, but my code is not there- it is in BeforeInsert). Any thoughts?

 

[maxhugen]

As soon as a user starts entering any data, the form's Dirty property becomes True, and the OnDirty event fires.

So try using the form's OnDirty event, to add the case number you've generated.

Max Hugen
Australia

 

[MichaelRed]

DMax and other functions which depend on the values in the recordset are quite prone to failure in this function. For multiuser applications in particular, it is easy for two instances of the app to access the function 'simultaneously". this results in one of two failures: both receive the same return value - thus creating dueling keys OR one is locked out and gets an error message, but the work is essientially 'lost'. In the "Jet" (native database for access) there is no intrinsic function which is completly safe for this function, although Autonumber is certainly an improvement over DMax.

For a more robust soloution, search the faqs here (Tek-Tips).



MichaelRed

 

[Chad1984]

I must be blind, MichaelRed. I can't find anything in the Access Form FAQs about a good way to create a unique value in a multiuser environment. Mind giving me a little more guidance?

 

[Remou]

How about the Tables and Relationships forum, which will give you faq700-184, an often recommended FAQ.

 

[MichaelRed]

thank Remou for a direct reference, there are others of a similar nature ...




MichaelRed

 

[Chad1984]

Thanks guys... holy matrimony, those examples look complicated! This database will only be used by people on three different computers, and rarely at the same time. The odds of duplicating this value are rare indeed... but in case it happened, I wanted a way for the user to get around it. Finally I found a solution that seems to work (hopefully there are no hidden gotchas!)

I have a procedure that I call from BeforeInsert (and Dirty, thanks to maxhugen's advice) like so:

Private Sub CaseNumber()
    If Me.NewRecord Then
        If DCount("Hou_ID", "Household") > 0 Then
            Me!Hou_ID = DMax("Hou_ID", "Household") + 1
        Else
            Me!Hou_ID = 10000 'Start numbering at 10,000
        End If
    End If
End Sub

Then I include a few lines in my Form_Error:

Private Sub Form_Error(DataErr As Integer, Response As Integer)
    If DataErr = 3022 Then
        MsgBox "The case number desired by this record was taken by a record saved while this was being entered. " & _
            "A new case number is being assigned."
        CaseNumber
        Response = acDataErrContinue
    End If
End Sub

It works beautifully. In the unlikely event that one person creates a new household record while another person has created but not yet saved theirs, the error message will pop up and a new case number is created (and displayed in a disabled textbox). The user has to repeat whatever action they took to save the form (click on a subform, etc), but that's a small price to pay.