Incrementing Number/Leading Zeros

[brad.goldberg]

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 "displayedRunNumber" 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_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!displayedRunNumber = Nz(DMax("displayedRunNumber", "fields"),
1000) + 1

End If

End Sub

This basically starts displayedRunNumber 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 displayedRunNumber 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.

[Fred Zuckerman]

<br***********@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.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 "displayedRunNumber" 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_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!displayedRunNumber = Nz(DMax("displayedRunNumber", "fields"),
1000) + 1

End If

End Sub

This basically starts displayedRunNumber 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 displayedRunNumber 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([displayedRunNumber], '00000')

Fred Zuckerman

[brad.goldberg]

Fred,

Thanks for the help...

I changed the before update field of the form to:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!displayedRunNumber = (DMax("displayedRunNumber", "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([displayedRunNumber],'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:

<br***********@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.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 "displayedRunNumber" 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_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!displayedRunNumber = Nz(DMax("displayedRunNumber", "fields"),
1000) + 1

End If

End Sub

This basically starts displayedRunNumber 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 displayedRunNumber 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([displayedRunNumber], '00000')

Fred Zuckerman

[Fred Zuckerman]

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 [displayedRunNumber] ? 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

<br***********@gmail.comwrote in message
news:11*********************@b28g2000cwb.googlegro ups.com...

Fred,

Thanks for the help...

I changed the before update field of the form to:

Private Sub Form_BeforeUpdate(Cancel As Integer)

If Me.NewRecord Then
Me!displayedRunNumber = (DMax("displayedRunNumber", "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([displayedRunNumber],'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:

<br***********@gmail.comwrote in message
news:11**********************@m79g2000cwm.googlegr oups.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 "displayedRunNumber" 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_BeforeUpdate(Cancel As Integer)
>
If Me.NewRecord Then
Me!displayedRunNumber = Nz(DMax("displayedRunNumber", "fields"),
1000) + 1
>
End If
>
End Sub
>
This basically starts displayedRunNumber 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 displayedRunNumber 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([displayedRunNumber], '00000')

Fred Zuckerman