Incrementing Number/Leading Zeros

**Fred Zuckerman** · Jul 31 '06, 11:35 PM

Re: Incrementing Number/Leading Zeros

<brad.goldberg@ gmail.comwrote in message
news:1154386017 .949713.202550@ m79g2000cwm.goo glegroups.com.. .

Hey All,
>
I know this has been addressed in a few different conversations but
none are exactly what I am trying to do, bear with me I am an access
Newbie.
>
Basically I have a form that assigns Run Numbers to each record (EMS
agency, each record is a Run Number, versus like a PO number or Work
Order..).
>
Right now I have a field which I call "displayedRunNu mber" stored in a
table as a number. The default value in the table is 0.
>
In the form where the counter is viewed I have a before update code of:
>
Private Sub Form_BeforeUpda te(Cancel As Integer)
>
If Me.NewRecord Then
Me!displayedRun Number = Nz(DMax("displa yedRunNumber", "fields"),
1000) + 1
>
End If
>
End Sub
>
This basically starts displayedRunNum ber at 1001 for the first record
in the form. The code also works nicely because the number isnt
generated until the user clicks a Save button I have on the form to
ensure he/she fills in all the information before he gets the Run
Number he/she needs to finish a report.
>
**Here's the problem. instead of 1001, I need the starting number to be
0001 and increment from there. I thought it would be OK my way, but the
law says it has to be 0001. I still want the same functionality as far
as the number generating after the record is saved. The problem is the
leading zeros, I guess the format of the displayedRunNum ber has to text
I guess.
>
Also I would prefer not to force the number into looking the way I want
it because I need to be able to search for say 0018 and find it.
>
Any help would be SO GREATLY appreciated!! I am very new at all this so
if you have code or suggestions please tell me where it is supposed to
go as people often reference code and I have no idea where it goes.
>
Thanks so much everyone,
>
Brad G.

Why not omit the 1000+ portion? Just let the the counter use DMax + 1.
Then use the following expression as the Source:

=Format([displayedRunNum ber], '00000')

Fred Zuckerman

**brad.goldberg@gmail.com** · Aug 1 '06, 12:25 AM

Re: Incrementing Number/Leading Zeros

Fred,

Thanks for the help...

I changed the before update field of the form to:

Private Sub Form_BeforeUpda te(Cancel As Integer)

If Me.NewRecord Then
Me!displayedRun Number = (DMax("displaye dRunNumber", "fields")) + 1

End If

End Sub

I changed the Control Source of the text box I'm using to display the
number in the form to:

=Format([displayedRunNum ber],'00000')

When I run the form I get #Error in the text box where I am supposed to
see the Run Number?

Any ideas whats going on?

Thanks

Fred Zuckerman wrote:

<brad.goldberg@ gmail.comwrote in message
news:1154386017 .949713.202550@ m79g2000cwm.goo glegroups.com.. .

Hey All,

I know this has been addressed in a few different conversations but
none are exactly what I am trying to do, bear with me I am an access
Newbie.

Basically I have a form that assigns Run Numbers to each record (EMS
agency, each record is a Run Number, versus like a PO number or Work
Order..).

Right now I have a field which I call "displayedRunNu mber" stored in a
table as a number. The default value in the table is 0.

In the form where the counter is viewed I have a before update code of:

Private Sub Form_BeforeUpda te(Cancel As Integer)

If Me.NewRecord Then
Me!displayedRun Number = Nz(DMax("displa yedRunNumber", "fields"),
1000) + 1

End If

End Sub

This basically starts displayedRunNum ber at 1001 for the first record
in the form. The code also works nicely because the number isnt
generated until the user clicks a Save button I have on the form to
ensure he/she fills in all the information before he gets the Run
Number he/she needs to finish a report.

**Here's the problem. instead of 1001, I need the starting number to be
0001 and increment from there. I thought it would be OK my way, but the
law says it has to be 0001. I still want the same functionality as far
as the number generating after the record is saved. The problem is the
leading zeros, I guess the format of the displayedRunNum ber has to text
I guess.

Also I would prefer not to force the number into looking the way I want
it because I need to be able to search for say 0018 and find it.

Any help would be SO GREATLY appreciated!! I am very new at all this so
if you have code or suggestions please tell me where it is supposed to
go as people often reference code and I have no idea where it goes.

Thanks so much everyone,

Brad G.

>
Why not omit the 1000+ portion? Just let the the counter use DMax + 1.
Then use the following expression as the Source:
>
=Format([displayedRunNum ber], '00000')
>
Fred Zuckerman

**Fred Zuckerman** · Aug 1 '06, 04:25 PM

Re: Incrementing Number/Leading Zeros

Brad,
(I'll top-post because you did. I prefer top-posting, but this group prefers
bottom-posting)

1. Is the name of the control [displayedRunNum ber] ? The fieldname of the
table data and the name of the form display control should be different.

2. I usually use the Default property for the DMax() function, not the
Before Update event.

Fred

<brad.goldberg@ gmail.comwrote in message
news:1154392047 .584103.97900@b 28g2000cwb.goog legroups.com...

Fred,
>
Thanks for the help...
>
I changed the before update field of the form to:
>
Private Sub Form_BeforeUpda te(Cancel As Integer)
>
If Me.NewRecord Then
Me!displayedRun Number = (DMax("displaye dRunNumber", "fields")) + 1
>
End If
>
End Sub
>
I changed the Control Source of the text box I'm using to display the
number in the form to:
>
=Format([displayedRunNum ber],'00000')
>
When I run the form I get #Error in the text box where I am supposed to
see the Run Number?
>
Any ideas whats going on?
>
Thanks
>
>
Fred Zuckerman wrote:

<brad.goldberg@ gmail.comwrote in message
news:1154386017 .949713.202550@ m79g2000cwm.goo glegroups.com.. .

Hey All,
>
I know this has been addressed in a few different conversations but
none are exactly what I am trying to do, bear with me I am an access
Newbie.
>
Basically I have a form that assigns Run Numbers to each record (EMS
agency, each record is a Run Number, versus like a PO number or Work
Order..).
>
Right now I have a field which I call "displayedRunNu mber" stored in a
table as a number. The default value in the table is 0.
>
In the form where the counter is viewed I have a before update code

of:

>
Private Sub Form_BeforeUpda te(Cancel As Integer)
>
If Me.NewRecord Then
Me!displayedRun Number = Nz(DMax("displa yedRunNumber", "fields"),
1000) + 1
>
End If
>
End Sub
>
This basically starts displayedRunNum ber at 1001 for the first record
in the form. The code also works nicely because the number isnt
generated until the user clicks a Save button I have on the form to
ensure he/she fills in all the information before he gets the Run
Number he/she needs to finish a report.
>
**Here's the problem. instead of 1001, I need the starting number to

be

0001 and increment from there. I thought it would be OK my way, but

the

law says it has to be 0001. I still want the same functionality as far
as the number generating after the record is saved. The problem is the
leading zeros, I guess the format of the displayedRunNum ber has to

text

I guess.
>
Also I would prefer not to force the number into looking the way I

want

it because I need to be able to search for say 0018 and find it.
>
Any help would be SO GREATLY appreciated!! I am very new at all this

so

if you have code or suggestions please tell me where it is supposed to
go as people often reference code and I have no idea where it goes.
>
Thanks so much everyone,
>
Brad G.

Why not omit the 1000+ portion? Just let the the counter use DMax + 1.
Then use the following expression as the Source:

=Format([displayedRunNum ber], '00000')

Fred Zuckerman

>

Incrementing Number/Leading Zeros

Incrementing Number/Leading Zeros

Comment

Comment

Comment