Incremental numbering (1 Viewer)

Big Pat

Registered User.
Local time
Today, 12:50
Joined
Sep 29, 2004
Messages
555
Hi,
I need to sequentially number new records that my users enter into a form. I have RequestID as a hidden autonumber field, but that will be used solely to identify records within the db and the users will never see it. So instead I'm using another field RequestNo and I would like this to increment by one when the user enters a RequestDate.

Having read through some threads on here, I've come up with

Code:
Private Sub RequestDate_AfterUpdate()
'Increment the RequestNo by 1
Me.RequestNo = Nz(DMax("[RequestNo]", "tblRequests") + 1)
End Sub

This seems to work OK except that starting from a completely empty database the first record is being numbered 0. How do I make it start at 1?

Second thing: once the RequestNo has been set, I'd like to "lock it in" somehow, so that even if the user updates the RequestDate later, the RequestNo will not change.

Thank you.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:50
Joined
Aug 30, 2003
Messages
36,118
Try this:

Me.RequestNo = Nz(DMax("[RequestNo]", "tblRequests"), 0) + 1

You can test for a new record with

Me.NewRecord

and only perform that if new. Or you can test the RequestNo field and only proceed if it's empty (Null).
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,614
you are setting this number in the wrong place

you probably need to set the number in the before update event for the form, and then only if its a new record

so

Code:
if me.newrecord then
   your code to get the next number
end if


that way, you dont modify the number for pre-existing records.



you ought to set this number in the b
 

Big Pat

Registered User.
Local time
Today, 12:50
Joined
Sep 29, 2004
Messages
555
Thanks Paul, that's done the trick. I've deleted my test records, started again and now it's numbering from 1. Perfect! I'm guessing the 0 in your code "primes" the field first?

Dave - I took your suggestion and moved the code to the BeforeUpdate event of the form. But I should probably have mentioned that the user will need to see the RequestNo once the database assigns it, as it needs to be logged on some paperwork. That's why I was doing it via the RequestDate, because that's the first field on the form and it's mandatory.

Doing it your way works, yes, but the user doesn't see the RequestNo without navigating away from that record and back again, which is messy. Especially as data-entry will only ever be one record at a time, so I have a Close button which takes the user back to a Menu and I've removed the navigation buttons

Is there another way to approach this? Some other event I can run the code from maybe? Or is there a way of saying "update the RequestNo, but only if it was Null to begin with" (if Null is even what it starts from and I'm unclear on that!) I've tried
Code:
Private Sub RequestDate_AfterUpdate()
'Increment the RequestNo by 1 only if it doesn't already have a value
If Me.RequestNo.Value = Null Then
    Me.RequestNo = Nz(DMax("[RequestNo]", "tblFOIRequests"), 0) + 1
End If
End Sub

and variations such as If Me.RequestNo.Value <1, but it seems to have no effect i.e. the RequestNo remains blank.

I'd love some further suggestions. The two consraints are
  • The user should see the RequestNo before they close the form
  • The RequestNo should not be updateable except when adding a new record.

I'm sure there's a combination of code that can do this but I can't figure it out.

Thanks for the help so far.

Pat.
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 05:50
Joined
Aug 30, 2003
Messages
36,118
Well, the zero in my code is the second argument of the Nz() function, which you had left out (as well as misplacing the "+ 1"). The second argument says "here's the value to use if the DMax() returns Null". In your code:

Nz(DMax("[RequestNo]", "tblRequests") + 1)

When the DMax() returned Null, you added that to 1, which is still Null (Null plus anything is Null). You had left out the second argument of the Nz() function; the default if unspecified for a numeric field is 0, which is why you got 0.

Dave's point about the event is to prevent duplicates. If you have multiple users and get the number at the beginning of your process, you run the risk of somebody else getting the same number while the first person is still working. One option is the update event. Another would be to commit the record as soon as you assign the number:

Me.RequestNo = Nz(DMax("[RequestNo]", "tblFOIRequests"), 0) + 1
DoCmd.RunCommand acCmdSaveRecord

But of course that runs the risk of the user changing their mind and abandoning the record. The "best" method probably depends on your specifics. Perhaps you could use the update event with a message box to the user with the number.

I'm a little surprised your test works, because nothing is ever "equal to" Null. I'd use this:

If Len(Me.RequestNo.Value & vbNullString) = 0 Then

which tests for both Null and ZLS ("").
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 12:50
Joined
Sep 12, 2006
Messages
15,614
you can still get the number at the last stage, and then pop it up to the user in a msgbox

the underlying issue is the point that Paul just made - you have to be careful how you allocate sequential numbers to keep a sequence intact, avoid gaps, and ensure no duplicates are issued. The only safe way is to issue the number at the very latest point.
 

Big Pat

Registered User.
Local time
Today, 12:50
Joined
Sep 29, 2004
Messages
555
Thanks both. I'll have a think about what you've said. Maybe a message box at the end will be the best way as you suggest. I think I'll try more than one method and come up with various test scenarios to see what works best.
 

Users who are viewing this thread

Top Bottom