displaying leading zeros in unbound textbox

[Guest]

First of all, thanks to BruceM for the help with an IF/Else statement to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my table
[barcodeID] as "00000" so that when a new record is created the value is
displayed as "00001". From there I have created an unbound textbox on the
form that needs to display the number as ="159" & "00001" & "00000000" in
order to display a barcode number that looks like: 159000010000000000
(18digits in total). Problem is that when I set the control source of the
unbound textbox to: ="159" & [barcodeID] & "00000000" it doesn't display the
leading zeros of the barcodeID field and instead displays it as:
15910000000000 - leaving out the 4 leading zeros that the barcodeID is
formatted to show. If I have a bound textbox display the contents of
barcodeID, it does show the leading zeros, what am I doing wrong?

any help is MUCH appreicated!!
dl

 

[Rick B]

One way is to store these items in a text field in the table, not a number.
Since these are not items you will be performing math on, and since they
will not need to use normal number formats, I'd store them as a text item.

 

[Guest]

Hi Rick, thanks for the reply
I actually tried that. Problem is that these need to be stored in the
table sequentially, as they are also my primary key and are being
automatically generated by the If/else statement. When I changed it to text
it dropped all the leading zeros and became sequenced: 1, 11, 12, 2, etc...

any other ideas?

I appreciate it!

One way is to store these items in a text field in the table, not a number.
Since these are not items you will be performing math on, and since they
will not need to use normal number formats, I'd store them as a text item.

--
Rick B

First of all, thanks to BruceM for the help with an IF/Else statement to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my table
[barcodeID] as "00000" so that when a new record is created the value is
displayed as "00001". From there I have created an unbound textbox on the
form that needs to display the number as ="159" & "00001" & "00000000" in
order to display a barcode number that looks like: 159000010000000000
(18digits in total). Problem is that when I set the control source of the
unbound textbox to: ="159" & [barcodeID] & "00000000" it doesn't display
the
leading zeros of the barcodeID field and instead displays it as:
15910000000000 - leaving out the 4 leading zeros that the barcodeID is
formatted to show. If I have a bound textbox display the contents of
barcodeID, it does show the leading zeros, what am I doing wrong?

any help is MUCH appreicated!!
dl

 

[Rick B]

It dropped the zeros? If it is stored as a number, then there are no
leading zeros. You must have some kind of formatting set up to force extra
zero's in your display.

You might first update a new text field with the "formatted" version of the
entry. Then change your original field to text, then update that field with
the field from that other new field. Then delete the new field.

As far as sorting, the order in your table is irrelevant. You should not be
looking at your tables, You should be working in the queries, reports, and
forms. All of those objects allow you to sort.


--
Rick B

Hi Rick, thanks for the reply
I actually tried that. Problem is that these need to be stored in the
table sequentially, as they are also my primary key and are being
automatically generated by the If/else statement. When I changed it to
text
it dropped all the leading zeros and became sequenced: 1, 11, 12, 2,
etc...

any other ideas?

I appreciate it!

One way is to store these items in a text field in the table, not a
number.
Since these are not items you will be performing math on, and since they
will not need to use normal number formats, I'd store them as a text
item.

--
Rick B

First of all, thanks to BruceM for the help with an IF/Else statement
to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my table
[barcodeID] as "00000" so that when a new record is created the value
is
displayed as "00001". From there I have created an unbound textbox on
the
form that needs to display the number as ="159" & "00001" & "00000000"
in
order to display a barcode number that looks like: 159000010000000000
(18digits in total). Problem is that when I set the control source of
the
unbound textbox to: ="159" & [barcodeID] & "00000000" it doesn't
display
the
leading zeros of the barcodeID field and instead displays it as:
15910000000000 - leaving out the 4 leading zeros that the barcodeID is
formatted to show. If I have a bound textbox display the contents of
barcodeID, it does show the leading zeros, what am I doing wrong?

any help is MUCH appreicated!!
dl

 

[Guest]

I appologize, I should have given more background first. This database is
needed to generate and track barcode numbers being used by my employer.
Historically, this was all done by first generating the barcode number in
excel and then tracking them in access for reporting at a later date. What
I'm trying to do is get rid of the excel sheet and allow access to assign the
barcode number automatically. The reason I need the table to store the
leading zeros is that our reporting department pulls sales based on the
barcodes used for promotions. Since the point of sale systems use a specific
format to trigger specific screens at the checkout, the number has to stay in
this format: 159#####+(10zeros) thus my delema. (of course the only number
being stored would be the ##### not the 159 since it's static)

Another complication is that they'd like to, in the next few months, start
to use a 6th digit on SOME barcodes instead of the 5 being used now, which
would mean that these numbers would have to be generated with an ending zero
and that ending zero would need to be editable - like this
record 1: 159+001230
record 2: 159+001240
record 3: 159+001241 - duplicating record 2 and allowing the user to change
the 0 to a 1

This is seeming like a pie in the sky, but I figured if anyone would be able
to help it'd be this newsgroup.

thanks for any help - again.

dl

It dropped the zeros? If it is stored as a number, then there are no
leading zeros. You must have some kind of formatting set up to force extra
zero's in your display.

You might first update a new text field with the "formatted" version of the
entry. Then change your original field to text, then update that field with
the field from that other new field. Then delete the new field.

As far as sorting, the order in your table is irrelevant. You should not be
looking at your tables, You should be working in the queries, reports, and
forms. All of those objects allow you to sort.


--
Rick B

Hi Rick, thanks for the reply
I actually tried that. Problem is that these need to be stored in the
table sequentially, as they are also my primary key and are being
automatically generated by the If/else statement. When I changed it to
text
it dropped all the leading zeros and became sequenced: 1, 11, 12, 2,
etc...

any other ideas?

I appreciate it!

One way is to store these items in a text field in the table, not a
number.
Since these are not items you will be performing math on, and since they
will not need to use normal number formats, I'd store them as a text
item.

--
Rick B



First of all, thanks to BruceM for the help with an IF/Else statement
to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my table
[barcodeID] as "00000" so that when a new record is created the value
is
displayed as "00001". From there I have created an unbound textbox on
the
form that needs to display the number as ="159" & "00001" & "00000000"
in
order to display a barcode number that looks like: 159000010000000000
(18digits in total). Problem is that when I set the control source of
the
unbound textbox to: ="159" & [barcodeID] & "00000000" it doesn't
display
the
leading zeros of the barcodeID field and instead displays it as:
15910000000000 - leaving out the 4 leading zeros that the barcodeID is
formatted to show. If I have a bound textbox display the contents of
barcodeID, it does show the leading zeros, what am I doing wrong?

any help is MUCH appreicated!!
dl

 

[BruceM]

I am familiar with this specific situation. The field is formatted with
leading zeros, as you surmised. To dl, Rick's instruction apply if you have
already stored data that you need to save. If these are just test records
you can delete them, then change the field from Number to Text in table
design view. Make sure the text box in which the concatenation occurs does
not have anything in its format property, while you're at it. I'm not sure
if it has any bearing on what you're doing, but the text box doesn't need a
format. Most likely, though, is that BarcodeID is a Number field in the
table.

It dropped the zeros? If it is stored as a number, then there are no
leading zeros. You must have some kind of formatting set up to force
extra zero's in your display.

You might first update a new text field with the "formatted" version of
the entry. Then change your original field to text, then update that
field with the field from that other new field. Then delete the new
field.

As far as sorting, the order in your table is irrelevant. You should not
be looking at your tables, You should be working in the queries, reports,
and forms. All of those objects allow you to sort.


--
Rick B

Hi Rick, thanks for the reply
I actually tried that. Problem is that these need to be stored in the
table sequentially, as they are also my primary key and are being
automatically generated by the If/else statement. When I changed it to
text
it dropped all the leading zeros and became sequenced: 1, 11, 12, 2,
etc...

any other ideas?

I appreciate it!

One way is to store these items in a text field in the table, not a
number.
Since these are not items you will be performing math on, and since they
will not need to use normal number formats, I'd store them as a text
item.

--
Rick B



First of all, thanks to BruceM for the help with an IF/Else statement
to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my table
[barcodeID] as "00000" so that when a new record is created the value
is
displayed as "00001". From there I have created an unbound textbox on
the
form that needs to display the number as ="159" & "00001" & "00000000"
in
order to display a barcode number that looks like: 159000010000000000
(18digits in total). Problem is that when I set the control source of
the
unbound textbox to: ="159" & [barcodeID] & "00000000" it doesn't
display
the
leading zeros of the barcodeID field and instead displays it as:
15910000000000 - leaving out the 4 leading zeros that the barcodeID is
formatted to show. If I have a bound textbox display the contents of
barcodeID, it does show the leading zeros, what am I doing wrong?

any help is MUCH appreicated!!
dl

 

[BruceM]

Let's get everything into the same discussion. There is a requirement that
the 5-digit barcode number not end with 0. To that end, I suggested this
code:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),1) = 9 Then
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
2,"00000")
Else
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
1,"00000")
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The trailing zero is a nuisance, but maybe something like this would take
care of it:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),2) = 9 Then
Me.BarcodeID = Format(Left(DMax("BarcodeID","YourTable"),5) +
2,"00000") & "0"
Else
Me.BarcodeID = Format(Left(DMax("BarcodeID","YourTable"),5)
+ 1,"00000") & "0"
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The idea is that BarcodeID will be a sequential five-digit text number (with
leading zeros) with a 0 appended, making it a six-digit number.
You said that the user would be duplicating the record and adding a 1 in
place of the trailing zero. That's not all that difficult, except I'm not
sure how it would work together with the code in the form's Current event.
In other words, if you are creating a new record, the form's Current event
code may override the attempt to duplicate the record, and would assign a
new number based on the incrementing system you have set up so far. One
option is to require the user to click a command button that would assign
the number (instead of using the form's Current event) and save the record.
You could do this by some means such as having a single visible text box for
user input, and a command button. The command button would, along with
assigning the number and saving the record, unhide other text boxes.
All of this assumes that my modified code will work as intended. Rick B, do
you have any comments, observations, etc.? Is this the long way around?
As Rick said, sort order in the table is not relevant. You can use a query
for that. You could also use report sorting, or Order By in a form, but the
general idea is the same.
Also, is this a multi-user environment? If so, you will need some error
handling or some way of dealing with the situation when another user start a
record before the first person is finished. Both records will attempt to
grab the same number, and there will be a conflict. Using the command
button to assign the number will minimize this by narrowing the time frame
between when the record is started and when it is saved, but will not
necessarily eliminate it entirely.

I appologize, I should have given more background first. This database is
needed to generate and track barcode numbers being used by my employer.
Historically, this was all done by first generating the barcode number in
excel and then tracking them in access for reporting at a later date.
What
I'm trying to do is get rid of the excel sheet and allow access to assign
the
barcode number automatically. The reason I need the table to store the
leading zeros is that our reporting department pulls sales based on the
barcodes used for promotions. Since the point of sale systems use a
specific
format to trigger specific screens at the checkout, the number has to stay
in
this format: 159#####+(10zeros) thus my delema. (of course the only
number
being stored would be the ##### not the 159 since it's static)

Another complication is that they'd like to, in the next few months, start
to use a 6th digit on SOME barcodes instead of the 5 being used now, which
would mean that these numbers would have to be generated with an ending
zero
and that ending zero would need to be editable - like this
record 1: 159+001230
record 2: 159+001240
record 3: 159+001241 - duplicating record 2 and allowing the user to
change
the 0 to a 1

This is seeming like a pie in the sky, but I figured if anyone would be
able
to help it'd be this newsgroup.

thanks for any help - again.

dl

It dropped the zeros? If it is stored as a number, then there are no
leading zeros. You must have some kind of formatting set up to force
extra
zero's in your display.

You might first update a new text field with the "formatted" version of
the
entry. Then change your original field to text, then update that field
with
the field from that other new field. Then delete the new field.

As far as sorting, the order in your table is irrelevant. You should not
be
looking at your tables, You should be working in the queries, reports,
and
forms. All of those objects allow you to sort.


--
Rick B

Hi Rick, thanks for the reply
I actually tried that. Problem is that these need to be stored in
the
table sequentially, as they are also my primary key and are being
automatically generated by the If/else statement. When I changed it to
text
it dropped all the leading zeros and became sequenced: 1, 11, 12, 2,
etc...

any other ideas?

I appreciate it!

:

One way is to store these items in a text field in the table, not a
number.
Since these are not items you will be performing math on, and since
they
will not need to use normal number formats, I'd store them as a text
item.

--
Rick B



First of all, thanks to BruceM for the help with an IF/Else
statement
to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my table
[barcodeID] as "00000" so that when a new record is created the
value
is
displayed as "00001". From there I have created an unbound textbox
on
the
form that needs to display the number as ="159" & "00001" &
"00000000"
in
order to display a barcode number that looks like:
159000010000000000
(18digits in total). Problem is that when I set the control source
of
the
unbound textbox to: ="159" & [barcodeID] & "00000000" it doesn't
display
the
leading zeros of the barcodeID field and instead displays it as:
15910000000000 - leaving out the 4 leading zeros that the barcodeID
is
formatted to show. If I have a bound textbox display the contents
of
barcodeID, it does show the leading zeros, what am I doing wrong?

any help is MUCH appreicated!!
dl

 

[Guest]

Hey Bruce,
I've created the assign button, but for some reason it doesn't skip the
zero and add two anymore, any idea why?

thanks!
--
dl

Let's get everything into the same discussion. There is a requirement that
the 5-digit barcode number not end with 0. To that end, I suggested this
code:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),1) = 9 Then
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
2,"00000")
Else
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
1,"00000")
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The trailing zero is a nuisance, but maybe something like this would take
care of it:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),2) = 9 Then
Me.BarcodeID = Format(Left(DMax("BarcodeID","YourTable"),5) +
2,"00000") & "0"
Else
Me.BarcodeID = Format(Left(DMax("BarcodeID","YourTable"),5)
+ 1,"00000") & "0"
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The idea is that BarcodeID will be a sequential five-digit text number (with
leading zeros) with a 0 appended, making it a six-digit number.
You said that the user would be duplicating the record and adding a 1 in
place of the trailing zero. That's not all that difficult, except I'm not
sure how it would work together with the code in the form's Current event.
In other words, if you are creating a new record, the form's Current event
code may override the attempt to duplicate the record, and would assign a
new number based on the incrementing system you have set up so far. One
option is to require the user to click a command button that would assign
the number (instead of using the form's Current event) and save the record.
You could do this by some means such as having a single visible text box for
user input, and a command button. The command button would, along with
assigning the number and saving the record, unhide other text boxes.
All of this assumes that my modified code will work as intended. Rick B, do
you have any comments, observations, etc.? Is this the long way around?
As Rick said, sort order in the table is not relevant. You can use a query
for that. You could also use report sorting, or Order By in a form, but the
general idea is the same.
Also, is this a multi-user environment? If so, you will need some error
handling or some way of dealing with the situation when another user start a
record before the first person is finished. Both records will attempt to
grab the same number, and there will be a conflict. Using the command
button to assign the number will minimize this by narrowing the time frame
between when the record is started and when it is saved, but will not
necessarily eliminate it entirely.

I appologize, I should have given more background first. This database is
needed to generate and track barcode numbers being used by my employer.
Historically, this was all done by first generating the barcode number in
excel and then tracking them in access for reporting at a later date.
What
I'm trying to do is get rid of the excel sheet and allow access to assign
the
barcode number automatically. The reason I need the table to store the
leading zeros is that our reporting department pulls sales based on the
barcodes used for promotions. Since the point of sale systems use a
specific
format to trigger specific screens at the checkout, the number has to stay
in
this format: 159#####+(10zeros) thus my delema. (of course the only
number
being stored would be the ##### not the 159 since it's static)

Another complication is that they'd like to, in the next few months, start
to use a 6th digit on SOME barcodes instead of the 5 being used now, which
would mean that these numbers would have to be generated with an ending
zero
and that ending zero would need to be editable - like this
record 1: 159+001230
record 2: 159+001240
record 3: 159+001241 - duplicating record 2 and allowing the user to
change
the 0 to a 1

This is seeming like a pie in the sky, but I figured if anyone would be
able
to help it'd be this newsgroup.

thanks for any help - again.

dl

It dropped the zeros? If it is stored as a number, then there are no
leading zeros. You must have some kind of formatting set up to force
extra
zero's in your display.

You might first update a new text field with the "formatted" version of
the
entry. Then change your original field to text, then update that field
with
the field from that other new field. Then delete the new field.

As far as sorting, the order in your table is irrelevant. You should not
be
looking at your tables, You should be working in the queries, reports,
and
forms. All of those objects allow you to sort.


--
Rick B



Hi Rick, thanks for the reply
I actually tried that. Problem is that these need to be stored in
the
table sequentially, as they are also my primary key and are being
automatically generated by the If/else statement. When I changed it to
text
it dropped all the leading zeros and became sequenced: 1, 11, 12, 2,
etc...

any other ideas?

I appreciate it!

:

One way is to store these items in a text field in the table, not a
number.
Since these are not items you will be performing math on, and since
they
will not need to use normal number formats, I'd store them as a text
item.

--
Rick B



First of all, thanks to BruceM for the help with an IF/Else
statement
to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my table
[barcodeID] as "00000" so that when a new record is created the
value
is
displayed as "00001". From there I have created an unbound textbox
on
the
form that needs to display the number as ="159" & "00001" &
"00000000"
in
order to display a barcode number that looks like:
159000010000000000
(18digits in total). Problem is that when I set the control source
of
the
unbound textbox to: ="159" & [barcodeID] & "00000000" it doesn't
display
the
leading zeros of the barcodeID field and instead displays it as:
15910000000000 - leaving out the 4 leading zeros that the barcodeID
is
formatted to show. If I have a bound textbox display the contents
of
barcodeID, it does show the leading zeros, what am I doing wrong?

any help is MUCH appreicated!!
dl

 

[BruceM]

Are you using the original code, or the modified code? I assume it is the
modified (6-digit) code.

If so, I have finally gotten around to testing the code, and have discovered
that a change is needed to one of the lines. Here is the revised code.
Note that I have used tblBarcode as the table name.

If Me.NewRecord Then
' This line looks at the five leftmost digits only, checking for a
"9" in the fifth place (ignoring the rightmost "0")
If Right(Left(DMax("BarcodeID", "tblBarcode"), 5), 1) = 9 Then
Me.BarcodeID = Format(Left(DMax("BarcodeID", "tblBarcode"), 5) +
2, "00000") & "0"
Else
Me.BarcodeID = Format(Left(DMax("BarcodeID", "tblBarcode"),
5) + 1, "00000") & "0"
End If
End If

Me.Dirty = False

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

Note that Me.Dirty = False saves the record. For reasons I do not
understand it is generally recommended over DoCmd.SaveRecord. This code
also handles the situation in which you duplicate a record and modify the
sixth digit from 0 to 1 (or whatever). The next record you create will
increment the highest five-digit number. For example, here is BarcodeID:
000010
000020
000030
Now you duplicate the 000020 record and modify the number to 000021. Your
next new record will be 000040, just as if you had not duplicated 000020.
You can use the command button wizard to duplicate a record.

You still need to be careful in a multi-user environment. Using the command
button early in the process to assign the number narrows the time frame
before a new record is saved, but there is still a chance of duplication.
One thing you could do is to leave out Me.Dirty = False, copy the database
to the network, have two users open it, and start a new record on each
computer. Click the command button to assign the number on one, then the
other computer. You should get an error message on the second. Even with
Me.Dirty = False in the code, duplication could still occur. There are ways
(not complicated) around that, but I will not anticipate the situation.
If it is to be a multi-user environment, once the development work is done
you should split the database. There have been many discussions in this
forum on that topic.

Hey Bruce,
I've created the assign button, but for some reason it doesn't skip the
zero and add two anymore, any idea why?

thanks!
--
dl

Let's get everything into the same discussion. There is a requirement
that
the 5-digit barcode number not end with 0. To that end, I suggested this
code:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),1) = 9 Then
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
2,"00000")
Else
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
1,"00000")
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The trailing zero is a nuisance, but maybe something like this would take
care of it:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),2) = 9 Then
Me.BarcodeID = Format(Left(DMax("BarcodeID","YourTable"),5)
+
2,"00000") & "0"
Else
Me.BarcodeID =
Format(Left(DMax("BarcodeID","YourTable"),5)
+ 1,"00000") & "0"
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The idea is that BarcodeID will be a sequential five-digit text number
(with
leading zeros) with a 0 appended, making it a six-digit number.
You said that the user would be duplicating the record and adding a 1 in
place of the trailing zero. That's not all that difficult, except I'm
not
sure how it would work together with the code in the form's Current
event.
In other words, if you are creating a new record, the form's Current
event
code may override the attempt to duplicate the record, and would assign a
new number based on the incrementing system you have set up so far. One
option is to require the user to click a command button that would assign
the number (instead of using the form's Current event) and save the
record.
You could do this by some means such as having a single visible text box
for
user input, and a command button. The command button would, along with
assigning the number and saving the record, unhide other text boxes.
All of this assumes that my modified code will work as intended. Rick B,
do
you have any comments, observations, etc.? Is this the long way around?
As Rick said, sort order in the table is not relevant. You can use a
query
for that. You could also use report sorting, or Order By in a form, but
the
general idea is the same.
Also, is this a multi-user environment? If so, you will need some error
handling or some way of dealing with the situation when another user
start a
record before the first person is finished. Both records will attempt to
grab the same number, and there will be a conflict. Using the command
button to assign the number will minimize this by narrowing the time
frame
between when the record is started and when it is saved, but will not
necessarily eliminate it entirely.

I appologize, I should have given more background first. This database
is
needed to generate and track barcode numbers being used by my employer.
Historically, this was all done by first generating the barcode number
in
excel and then tracking them in access for reporting at a later date.
What
I'm trying to do is get rid of the excel sheet and allow access to
assign
the
barcode number automatically. The reason I need the table to store the
leading zeros is that our reporting department pulls sales based on the
barcodes used for promotions. Since the point of sale systems use a
specific
format to trigger specific screens at the checkout, the number has to
stay
in
this format: 159#####+(10zeros) thus my delema. (of course the only
number
being stored would be the ##### not the 159 since it's static)

Another complication is that they'd like to, in the next few months,
start
to use a 6th digit on SOME barcodes instead of the 5 being used now,
which
would mean that these numbers would have to be generated with an ending
zero
and that ending zero would need to be editable - like this
record 1: 159+001230
record 2: 159+001240
record 3: 159+001241 - duplicating record 2 and allowing the user to
change
the 0 to a 1

This is seeming like a pie in the sky, but I figured if anyone would be
able
to help it'd be this newsgroup.

thanks for any help - again.

dl



:

It dropped the zeros? If it is stored as a number, then there are no
leading zeros. You must have some kind of formatting set up to force
extra
zero's in your display.

You might first update a new text field with the "formatted" version
of
the
entry. Then change your original field to text, then update that
field
with
the field from that other new field. Then delete the new field.

As far as sorting, the order in your table is irrelevant. You should
not
be
looking at your tables, You should be working in the queries, reports,
and
forms. All of those objects allow you to sort.


--
Rick B



Hi Rick, thanks for the reply
I actually tried that. Problem is that these need to be stored in
the
table sequentially, as they are also my primary key and are being
automatically generated by the If/else statement. When I changed it
to
text
it dropped all the leading zeros and became sequenced: 1, 11, 12, 2,
etc...

any other ideas?

I appreciate it!

:

One way is to store these items in a text field in the table, not a
number.
Since these are not items you will be performing math on, and since
they
will not need to use normal number formats, I'd store them as a
text
item.

--
Rick B



First of all, thanks to BruceM for the help with an IF/Else
statement
to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my
table
[barcodeID] as "00000" so that when a new record is created the
value
is
displayed as "00001". From there I have created an unbound
textbox
on
the
form that needs to display the number as ="159" & "00001" &
"00000000"
in
order to display a barcode number that looks like:
159000010000000000
(18digits in total). Problem is that when I set the control
source
of
the
unbound textbox to: ="159" & [barcodeID] & "00000000" it doesn't
display
the
leading zeros of the barcodeID field and instead displays it as:
15910000000000 - leaving out the 4 leading zeros that the
barcodeID
is
formatted to show. If I have a bound textbox display the
contents
of
barcodeID, it does show the leading zeros, what am I doing wrong?

any help is MUCH appreicated!!
dl

 

[Guest]

I'll give it a try and let you know how it goes.

Unfortunately, some other people on my team who will remain un-named deleted
records from a previous version and caused me a bunch of extra work, so I
won't be able to attempt to implement this new code just yet.

Thanks again for all the help, Bruce.
--
dl

Are you using the original code, or the modified code? I assume it is the
modified (6-digit) code.

If so, I have finally gotten around to testing the code, and have discovered
that a change is needed to one of the lines. Here is the revised code.
Note that I have used tblBarcode as the table name.

If Me.NewRecord Then
' This line looks at the five leftmost digits only, checking for a
"9" in the fifth place (ignoring the rightmost "0")
If Right(Left(DMax("BarcodeID", "tblBarcode"), 5), 1) = 9 Then
Me.BarcodeID = Format(Left(DMax("BarcodeID", "tblBarcode"), 5) +
2, "00000") & "0"
Else
Me.BarcodeID = Format(Left(DMax("BarcodeID", "tblBarcode"),
5) + 1, "00000") & "0"
End If
End If

Me.Dirty = False

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

Note that Me.Dirty = False saves the record. For reasons I do not
understand it is generally recommended over DoCmd.SaveRecord. This code
also handles the situation in which you duplicate a record and modify the
sixth digit from 0 to 1 (or whatever). The next record you create will
increment the highest five-digit number. For example, here is BarcodeID:
000010
000020
000030
Now you duplicate the 000020 record and modify the number to 000021. Your
next new record will be 000040, just as if you had not duplicated 000020.
You can use the command button wizard to duplicate a record.

You still need to be careful in a multi-user environment. Using the command
button early in the process to assign the number narrows the time frame
before a new record is saved, but there is still a chance of duplication.
One thing you could do is to leave out Me.Dirty = False, copy the database
to the network, have two users open it, and start a new record on each
computer. Click the command button to assign the number on one, then the
other computer. You should get an error message on the second. Even with
Me.Dirty = False in the code, duplication could still occur. There are ways
(not complicated) around that, but I will not anticipate the situation.
If it is to be a multi-user environment, once the development work is done
you should split the database. There have been many discussions in this
forum on that topic.

Hey Bruce,
I've created the assign button, but for some reason it doesn't skip the
zero and add two anymore, any idea why?

thanks!
--
dl

Let's get everything into the same discussion. There is a requirement
that
the 5-digit barcode number not end with 0. To that end, I suggested this
code:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),1) = 9 Then
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
2,"00000")
Else
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
1,"00000")
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The trailing zero is a nuisance, but maybe something like this would take
care of it:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),2) = 9 Then
Me.BarcodeID = Format(Left(DMax("BarcodeID","YourTable"),5)
+
2,"00000") & "0"
Else
Me.BarcodeID =
Format(Left(DMax("BarcodeID","YourTable"),5)
+ 1,"00000") & "0"
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The idea is that BarcodeID will be a sequential five-digit text number
(with
leading zeros) with a 0 appended, making it a six-digit number.
You said that the user would be duplicating the record and adding a 1 in
place of the trailing zero. That's not all that difficult, except I'm
not
sure how it would work together with the code in the form's Current
event.
In other words, if you are creating a new record, the form's Current
event
code may override the attempt to duplicate the record, and would assign a
new number based on the incrementing system you have set up so far. One
option is to require the user to click a command button that would assign
the number (instead of using the form's Current event) and save the
record.
You could do this by some means such as having a single visible text box
for
user input, and a command button. The command button would, along with
assigning the number and saving the record, unhide other text boxes.
All of this assumes that my modified code will work as intended. Rick B,
do
you have any comments, observations, etc.? Is this the long way around?
As Rick said, sort order in the table is not relevant. You can use a
query
for that. You could also use report sorting, or Order By in a form, but
the
general idea is the same.
Also, is this a multi-user environment? If so, you will need some error
handling or some way of dealing with the situation when another user
start a
record before the first person is finished. Both records will attempt to
grab the same number, and there will be a conflict. Using the command
button to assign the number will minimize this by narrowing the time
frame
between when the record is started and when it is saved, but will not
necessarily eliminate it entirely.

I appologize, I should have given more background first. This database
is
needed to generate and track barcode numbers being used by my employer.
Historically, this was all done by first generating the barcode number
in
excel and then tracking them in access for reporting at a later date.
What
I'm trying to do is get rid of the excel sheet and allow access to
assign
the
barcode number automatically. The reason I need the table to store the
leading zeros is that our reporting department pulls sales based on the
barcodes used for promotions. Since the point of sale systems use a
specific
format to trigger specific screens at the checkout, the number has to
stay
in
this format: 159#####+(10zeros) thus my delema. (of course the only
number
being stored would be the ##### not the 159 since it's static)

Another complication is that they'd like to, in the next few months,
start
to use a 6th digit on SOME barcodes instead of the 5 being used now,
which
would mean that these numbers would have to be generated with an ending
zero
and that ending zero would need to be editable - like this
record 1: 159+001230
record 2: 159+001240
record 3: 159+001241 - duplicating record 2 and allowing the user to
change
the 0 to a 1

This is seeming like a pie in the sky, but I figured if anyone would be
able
to help it'd be this newsgroup.

thanks for any help - again.

dl



:

It dropped the zeros? If it is stored as a number, then there are no
leading zeros. You must have some kind of formatting set up to force
extra
zero's in your display.

You might first update a new text field with the "formatted" version
of
the
entry. Then change your original field to text, then update that
field
with
the field from that other new field. Then delete the new field.

As far as sorting, the order in your table is irrelevant. You should
not
be
looking at your tables, You should be working in the queries, reports,
and
forms. All of those objects allow you to sort.


--
Rick B



Hi Rick, thanks for the reply
I actually tried that. Problem is that these need to be stored in
the
table sequentially, as they are also my primary key and are being
automatically generated by the If/else statement. When I changed it
to
text
it dropped all the leading zeros and became sequenced: 1, 11, 12, 2,
etc...

any other ideas?

I appreciate it!

:

One way is to store these items in a text field in the table, not a
number.
Since these are not items you will be performing math on, and since
they
will not need to use normal number formats, I'd store them as a
text
item.

--
Rick B



First of all, thanks to BruceM for the help with an IF/Else
statement
to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my
table
[barcodeID] as "00000" so that when a new record is created the
value
is
displayed as "00001". From there I have created an unbound
textbox
on
the
form that needs to display the number as ="159" & "00001" &
"00000000"
in
order to display a barcode number that looks like:
159000010000000000
(18digits in total). Problem is that when I set the control
source
of
the
unbound textbox to: ="159" & [barcodeID] & "00000000" it doesn't
display
the
leading zeros of the barcodeID field and instead displays it as:
15910000000000 - leaving out the 4 leading zeros that the
barcodeID
is
formatted to show. If I have a bound textbox display the
contents
of
barcodeID, it does show the leading zeros, what am I doing wrong?

any help is MUCH appreicated!!
dl

 

[BruceM]

Don't you hate that? Good luck with the restoration project.
I need to come up with some custom incremented numbering systems, so this
has been a good exercise. It works, too. Taking the time to implement it
will be well worthwhile. Post a new thread if you have multi-user issues or
other questions. Multi-user issues are very important, but are not usually
all that difficult to solve.

I'll give it a try and let you know how it goes.

Unfortunately, some other people on my team who will remain un-named
deleted
records from a previous version and caused me a bunch of extra work, so I
won't be able to attempt to implement this new code just yet.

Thanks again for all the help, Bruce.
--
dl

Are you using the original code, or the modified code? I assume it is
the
modified (6-digit) code.

If so, I have finally gotten around to testing the code, and have
discovered
that a change is needed to one of the lines. Here is the revised code.
Note that I have used tblBarcode as the table name.

If Me.NewRecord Then
' This line looks at the five leftmost digits only, checking for
a
"9" in the fifth place (ignoring the rightmost "0")
If Right(Left(DMax("BarcodeID", "tblBarcode"), 5), 1) = 9 Then
Me.BarcodeID = Format(Left(DMax("BarcodeID", "tblBarcode"),
5) +
2, "00000") & "0"
Else
Me.BarcodeID = Format(Left(DMax("BarcodeID",
"tblBarcode"),
5) + 1, "00000") & "0"
End If
End If

Me.Dirty = False

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

Note that Me.Dirty = False saves the record. For reasons I do not
understand it is generally recommended over DoCmd.SaveRecord. This code
also handles the situation in which you duplicate a record and modify the
sixth digit from 0 to 1 (or whatever). The next record you create will
increment the highest five-digit number. For example, here is BarcodeID:
000010
000020
000030
Now you duplicate the 000020 record and modify the number to 000021.
Your
next new record will be 000040, just as if you had not duplicated 000020.
You can use the command button wizard to duplicate a record.

You still need to be careful in a multi-user environment. Using the
command
button early in the process to assign the number narrows the time frame
before a new record is saved, but there is still a chance of duplication.
One thing you could do is to leave out Me.Dirty = False, copy the
database
to the network, have two users open it, and start a new record on each
computer. Click the command button to assign the number on one, then the
other computer. You should get an error message on the second. Even
with
Me.Dirty = False in the code, duplication could still occur. There are
ways
(not complicated) around that, but I will not anticipate the situation.
If it is to be a multi-user environment, once the development work is
done
you should split the database. There have been many discussions in this
forum on that topic.

Hey Bruce,
I've created the assign button, but for some reason it doesn't skip
the
zero and add two anymore, any idea why?

thanks!
--
dl


:

Let's get everything into the same discussion. There is a requirement
that
the 5-digit barcode number not end with 0. To that end, I suggested
this
code:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),1) = 9 Then
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
2,"00000")
Else
Me.BarcodeID = Format(DMax("BarcodeID","YourTable") +
1,"00000")
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The trailing zero is a nuisance, but maybe something like this would
take
care of it:

Private Sub Form_Current()

If Me.NewRecord Then
If Right(DMax("BarcodeID","YourTable"),2) = 9 Then
Me.BarcodeID =
Format(Left(DMax("BarcodeID","YourTable"),5)
+
2,"00000") & "0"
Else
Me.BarcodeID =
Format(Left(DMax("BarcodeID","YourTable"),5)
+ 1,"00000") & "0"
End If
End If

Me.txtCompleteNumber = "159" & BarcodeID & "0000000000"

End Sub

The idea is that BarcodeID will be a sequential five-digit text number
(with
leading zeros) with a 0 appended, making it a six-digit number.
You said that the user would be duplicating the record and adding a 1
in
place of the trailing zero. That's not all that difficult, except I'm
not
sure how it would work together with the code in the form's Current
event.
In other words, if you are creating a new record, the form's Current
event
code may override the attempt to duplicate the record, and would
assign a
new number based on the incrementing system you have set up so far.
One
option is to require the user to click a command button that would
assign
the number (instead of using the form's Current event) and save the
record.
You could do this by some means such as having a single visible text
box
for
user input, and a command button. The command button would, along
with
assigning the number and saving the record, unhide other text boxes.
All of this assumes that my modified code will work as intended. Rick
B,
do
you have any comments, observations, etc.? Is this the long way
around?
As Rick said, sort order in the table is not relevant. You can use a
query
for that. You could also use report sorting, or Order By in a form,
but
the
general idea is the same.
Also, is this a multi-user environment? If so, you will need some
error
handling or some way of dealing with the situation when another user
start a
record before the first person is finished. Both records will attempt
to
grab the same number, and there will be a conflict. Using the command
button to assign the number will minimize this by narrowing the time
frame
between when the record is started and when it is saved, but will not
necessarily eliminate it entirely.

I appologize, I should have given more background first. This
database
is
needed to generate and track barcode numbers being used by my
employer.
Historically, this was all done by first generating the barcode
number
in
excel and then tracking them in access for reporting at a later
date.
What
I'm trying to do is get rid of the excel sheet and allow access to
assign
the
barcode number automatically. The reason I need the table to store
the
leading zeros is that our reporting department pulls sales based on
the
barcodes used for promotions. Since the point of sale systems use a
specific
format to trigger specific screens at the checkout, the number has
to
stay
in
this format: 159#####+(10zeros) thus my delema. (of course the only
number
being stored would be the ##### not the 159 since it's static)

Another complication is that they'd like to, in the next few months,
start
to use a 6th digit on SOME barcodes instead of the 5 being used now,
which
would mean that these numbers would have to be generated with an
ending
zero
and that ending zero would need to be editable - like this
record 1: 159+001230
record 2: 159+001240
record 3: 159+001241 - duplicating record 2 and allowing the user to
change
the 0 to a 1

This is seeming like a pie in the sky, but I figured if anyone would
be
able
to help it'd be this newsgroup.

thanks for any help - again.

dl



:

It dropped the zeros? If it is stored as a number, then there are
no
leading zeros. You must have some kind of formatting set up to
force
extra
zero's in your display.

You might first update a new text field with the "formatted"
version
of
the
entry. Then change your original field to text, then update that
field
with
the field from that other new field. Then delete the new field.

As far as sorting, the order in your table is irrelevant. You
should
not
be
looking at your tables, You should be working in the queries,
reports,
and
forms. All of those objects allow you to sort.


--
Rick B



Hi Rick, thanks for the reply
I actually tried that. Problem is that these need to be stored
in
the
table sequentially, as they are also my primary key and are being
automatically generated by the If/else statement. When I changed
it
to
text
it dropped all the leading zeros and became sequenced: 1, 11, 12,
2,
etc...

any other ideas?

I appreciate it!

:

One way is to store these items in a text field in the table,
not a
number.
Since these are not items you will be performing math on, and
since
they
will not need to use normal number formats, I'd store them as a
text
item.

--
Rick B



First of all, thanks to BruceM for the help with an IF/Else
statement
to
generate specific autonumbers, works beautifully.

My problem now is this: I have formatted a number field in my
table
[barcodeID] as "00000" so that when a new record is created
the
value
is
displayed as "00001". From there I have created an unbound
textbox
on
the
form that needs to display the number as ="159" & "00001" &
"00000000"
in
order to display a barcode number that looks like:
159000010000000000
(18digits in total). Problem is that when I set the control
source
of
the
unbound textbox to: ="159" & [barcodeID] & "00000000" it
doesn't
display
the
leading zeros of the barcodeID field and instead displays it
as:
15910000000000 - leaving out the 4 leading zeros that the
barcodeID
is
formatted to show. If I have a bound textbox display the
contents
of
barcodeID, it does show the leading zeros, what am I doing
wrong?

any help is MUCH appreicated!!
dl

 

[Guest]

Works beautifully!!

Thanks again for all the help, I really do appreciate it.

Although they'd like this to be multi-user, I'm just locking down the whole
table to prevent any issues for the time being. They want me to get back to
doing my other work too ; )