Formatting a textbox (text data type) to add leading 0's

[IncidentalProgrammer]

Good morning, all!



I'm working on a form to issue insurance policies, and trying to get it to create unique policy numbers each time. While I have most of the setup for that done, there is one field that is giving me fits. The truly unique part of each policy number is 3 digits; the rest can be repeated in other policy numbers. The field has to be text, because it must be combined with other parts to create the full policy number.

The "PolID", as I'm calling it, needs to be 3 digits, with leading 0's where applicable (start with 001, and go up), unique, text, and unable to be manipulated by the user--completely system generated, and in place when the user opens the form to issue a policy (I do have code in place that should keep simultaneous users from getting the same #). I cannot for the life of me get those leading 0's to stick. I had them working yesterday, but work that I've done today has somehow shut it off, and I haven't been able to get them back. Right now, the textbox (ctlPolID) has the following in place:

Default Value set to: =DMax("PolID","tblUwNew")+1
(Basically trying to simulate auto# here, but without skipping values.)

Private Sub ctlPolID_AfterUpdate()
     'Format PolID to 3 digits with leading 0s.
    Me.ctlPolID = Format([PolID], "000")
End Sub

That code was working yesterday, but isn't today. I tried it on the form's load event, but then I didn't get anything to generate in the textbox at all.

I've tried google, but not really finding things that apply to my situation (a lot of it is update queries for adding leading 0's later). Does anyone know how to do this?

[pbaldy]

The after update event is only going to fire if the user makes a change, and it doesn't sound like they are. Why not add the Format() function to the default value?

[IncidentalProgrammer]

I was afraid it was something like that. I'm still struggling a bit with syntax. How would I properly connect the Format to the existing expression? I had tried that first, but couldn't get it to work and switched to the code.

[pbaldy]

=Format(DMax("PolID","tblUwNew")+1, "000")

[IncidentalProgrammer]

That's where I was going wrong! Was trying to tack the whole Format function onto the end. Derp!

Thanks so much!

[pbaldy]

Happy to help!