Generating Serialized Numbers

[Mari]

OK here is the situation. I need to be able to generate a list of numbers
based on a quantity and item number.
For example:
User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004
I want it to keep these issued numbers in a table.
I tried a while loop but I think there is something wrong...
If anyone can help I would muy appreciate it.

 

[Mari]

By the way this is for Microsoft Access 2003

 

[Clifford Bass]

Hi Mari,

How about posting you code? It is kind of hard to help without that.

Clifford Bass

 

[Mari]

Dim stCount As String

stCount = [Quantity]

Do While stCount > 0
Me![Number] = [Number] + 1
stCount = stCount - 1
Loop
But one of the major problems with this is it does not hold the number from
a previous record. This would just start it over everytime.

 

[Clifford Bass]

Hi Mari,

You will need to store the results somewhere. And then before
generating the numbers you will need to check to see what value was last
used. Added questions: What is the table where you are storing the new
values? If you have an item DEF, does it also get assigned the XXnnnn? Or
does it get assigned YYnnnn?

Clifford Bass

 

[BruceM]

The numbers need to be stored if they are part of the record. If you are
talking about a ranking query or something of the sort, that could work as
long as the data are always sorted the same way, never filtered, and records
are never deleted.

 

[Mari]

1. Yes, the number will always start with the "XX" value. It is a code for
the item.
2. The numbers followi ng the item code is a four digit number that can be
0001 to 9999. But I do need the system to represent the zeros.
Also, I do the issued numbers to be in a table for later use. I am time
stamping when they were issued. But that code works.
By the way thanks for the help....
this has been eating at me and I know it is simple I just can not seem
to think of it.

 

[Mari]

The table is a simple sub table attached to the main table of items.
Main Table-information on the item
Sub Table - Has the list of numbers that have been issued
The "XX" in the beginning does not change because it is the item ID tag.

Hi Mari,

You will need to store the results somewhere. And then before
generating the numbers you will need to check to see what value was last
used. Added questions: What is the table where you are storing the new
values? If you have an item DEF, does it also get assigned the XXnnnn? Or
does it get assigned YYnnnn?

Clifford Bass

Dim stCount As String

stCount = [Quantity]

Do While stCount > 0
Me![Number] = [Number] + 1
stCount = stCount - 1
Loop
But one of the major problems with this is it does not hold the number from
a previous record. This would just start it over everytime.

 

[BruceM]

You wrote:
"User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004"

Is "XX" the prefix no matter what, or are you using XX to represent a prefix
that could change? If the former, bind a text box to the incrementing field
and set its Default Value to:
=DMax("[SomeField]","[YourTable]")
Since you are placing "XX" in front of every number, just concatenate it as
needed. In the same text box the Control Source could be:
="XX" & [SomeField]

I think this is a case where the text box needs to have a different name
than the field.

If the latter, what is the source of the prefix? Is it a field in the
record, or what exactly?

 

[Mari]

It is getting closer... The code for DMAX () works but gives the last one
used. When I tried just +1 it removed the zeros that were in place
So if the last issued number was 0006 it returns a 7

You wrote:
"User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004"

Is "XX" the prefix no matter what, or are you using XX to represent a prefix
that could change? If the former, bind a text box to the incrementing field
and set its Default Value to:
=DMax("[SomeField]","[YourTable]")
Since you are placing "XX" in front of every number, just concatenate it as
needed. In the same text box the Control Source could be:
="XX" & [SomeField]

I think this is a case where the text box needs to have a different name
than the field.

If the latter, what is the source of the prefix? Is it a field in the
record, or what exactly?

1. Yes, the number will always start with the "XX" value. It is a code for
the item.
2. The numbers followi ng the item code is a four digit number that can be
0001 to 9999. But I do need the system to represent the zeros.
Also, I do the issued numbers to be in a table for later use. I am time
stamping when they were issued. But that code works.
By the way thanks for the help....
this has been eating at me and I know it is simple I just can not seem
to think of it.

 

[BruceM]

Sorry, forgot to add 1. It should have been:
=DMax("[SomeField]","[YourTable]") + 1

You should be able simply to format the text box. Set its Format property
to:
0000

Or you can use the format function:
="XX" & Format([SomeField]),"0000")

It is getting closer... The code for DMAX () works but gives the last one
used. When I tried just +1 it removed the zeros that were in place
So if the last issued number was 0006 it returns a 7

You wrote:
"User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004"

Is "XX" the prefix no matter what, or are you using XX to represent a
prefix
that could change? If the former, bind a text box to the incrementing
field
and set its Default Value to:
=DMax("[SomeField]","[YourTable]")
Since you are placing "XX" in front of every number, just concatenate it
as
needed. In the same text box the Control Source could be:
="XX" & [SomeField]

I think this is a case where the text box needs to have a different name
than the field.

If the latter, what is the source of the prefix? Is it a field in the
record, or what exactly?

1. Yes, the number will always start with the "XX" value. It is a code
for
the item.
2. The numbers followi ng the item code is a four digit number that can
be
0001 to 9999. But I do need the system to represent the zeros.
Also, I do the issued numbers to be in a table for later use. I am time
stamping when they were issued. But that code works.
By the way thanks for the help....
this has been eating at me and I know it is simple I just can not
seem
to think of it.

:

First, two questions ......
1. Does your number always start with "XX"?
2. What happens when you have 1000? Are your numbers XX1000 to
XX0001?

You don't need to store all those numbers! You just need to store how
many
you have at the current time (say 1000) then use a function to
generate
all
the numers XX1000 to XX0001 when you need the numbers.

Steve



OK here is the situation. I need to be able to generate a list of
numbers
based on a quantity and item number.
For example:
User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004
I want it to keep these issued numbers in a table.
I tried a while loop but I think there is something wrong...
If anyone can help I would muy appreciate it.

 

[BruceM]

In a multi-user environment you should take precautions against two users
entering a record at the same time, thus both trying to use the same number.
One way to prevent that is shown here:
http://www.rogersaccesslibrary.com/...?TID=395&SID=83z7c11zc7b721d2a1e51989c53d7ffb

The URL should be on one line. You could also go here and look for
AutonumberProblem:
http://www.rogersaccesslibrary.com/forum/forum_topics.asp?FID=1

It is getting closer... The code for DMAX () works but gives the last one
used. When I tried just +1 it removed the zeros that were in place
So if the last issued number was 0006 it returns a 7

You wrote:
"User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004"

Is "XX" the prefix no matter what, or are you using XX to represent a
prefix
that could change? If the former, bind a text box to the incrementing
field
and set its Default Value to:
=DMax("[SomeField]","[YourTable]")
Since you are placing "XX" in front of every number, just concatenate it
as
needed. In the same text box the Control Source could be:
="XX" & [SomeField]

I think this is a case where the text box needs to have a different name
than the field.

If the latter, what is the source of the prefix? Is it a field in the
record, or what exactly?

1. Yes, the number will always start with the "XX" value. It is a code
for
the item.
2. The numbers followi ng the item code is a four digit number that can
be
0001 to 9999. But I do need the system to represent the zeros.
Also, I do the issued numbers to be in a table for later use. I am time
stamping when they were issued. But that code works.
By the way thanks for the help....
this has been eating at me and I know it is simple I just can not
seem
to think of it.

:

First, two questions ......
1. Does your number always start with "XX"?
2. What happens when you have 1000? Are your numbers XX1000 to
XX0001?

You don't need to store all those numbers! You just need to store how
many
you have at the current time (say 1000) then use a function to
generate
all
the numers XX1000 to XX0001 when you need the numbers.

Steve



OK here is the situation. I need to be able to generate a list of
numbers
based on a quantity and item number.
For example:
User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004
I want it to keep these issued numbers in a table.
I tried a while loop but I think there is something wrong...
If anyone can help I would muy appreciate it.

 

[Mari]

OK Tried this using

Dim stCount As Variant

stCount = Me.[Quantity]

Do While stCount > 0

Me![HWAlbuquerqueLabel-Sub]![SerialNumber] = DMax("[SerialNumber]",
"[HWAlbuquerqueSerialNumbers]")
Me![HWAlbuquerqueLabel-Sub]![SerialNumber] =
Format([HWAlbuquerqueLabel-Sub]![SerialNumber], "0000")
stCount = stCount - 1
Loop

A couple of things go wrong one if I add the plus 1 to the DMAX function the
system gives a Type Match error because it is a text
Also, when it repeats the "XX" twice then three times, etc.
example the first one will be XX0001 then XXXX0001 then XXXXXX0001

Another part of this is I want the code to create a new record for each
serial number until the entered quantity is reached.

Sorry, forgot to add 1. It should have been:
=DMax("[SomeField]","[YourTable]") + 1

You should be able simply to format the text box. Set its Format property
to:
0000

Or you can use the format function:
="XX" & Format([SomeField]),"0000")

It is getting closer... The code for DMAX () works but gives the last one
used. When I tried just +1 it removed the zeros that were in place
So if the last issued number was 0006 it returns a 7

You wrote:
"User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004"

Is "XX" the prefix no matter what, or are you using XX to represent a
prefix
that could change? If the former, bind a text box to the incrementing
field
and set its Default Value to:
=DMax("[SomeField]","[YourTable]")
Since you are placing "XX" in front of every number, just concatenate it
as
needed. In the same text box the Control Source could be:
="XX" & [SomeField]

I think this is a case where the text box needs to have a different name
than the field.

If the latter, what is the source of the prefix? Is it a field in the
record, or what exactly?

1. Yes, the number will always start with the "XX" value. It is a code
for
the item.
2. The numbers followi ng the item code is a four digit number that can
be
0001 to 9999. But I do need the system to represent the zeros.
Also, I do the issued numbers to be in a table for later use. I am time
stamping when they were issued. But that code works.
By the way thanks for the help....
this has been eating at me and I know it is simple I just can not
seem
to think of it.

:

First, two questions ......
1. Does your number always start with "XX"?
2. What happens when you have 1000? Are your numbers XX1000 to
XX0001?

You don't need to store all those numbers! You just need to store how
many
you have at the current time (say 1000) then use a function to
generate
all
the numers XX1000 to XX0001 when you need the numbers.

Steve



OK here is the situation. I need to be able to generate a list of
numbers
based on a quantity and item number.
For example:
User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004
I want it to keep these issued numbers in a table.
I tried a while loop but I think there is something wrong...
If anyone can help I would muy appreciate it.

 

[BruceM]

Just use the expression as the DefaultValue of a text box bound to the
SerialNumber field, if that is the field with the incrementing number.
Another option is to use DefaultValue in the form's Before Insert event:

Me.txtSerialNumber.DefaultValue = Nz(DMax("SerialNumber","YourTable"),0) + 1

You could use the Current event too, or the Before Update event to help
guard against duplication in a multi-user environment. There was no need
for a loop until you said you want the code to create a new record for each
Serial Number is a pretty big "Oh, by the way" remark. It is not what you
asked in the first place as far as I can tell.

I think you will need to put together some SQL and insert the required
records, but I don't know how to do that. If you describe in a new thread
exactly what you hope to accomplish, along with a description of the
database structure and relationships, it may be possible for somebody to
suggest a solution, but you need to describe things such as
HWAlbuquerqueLabel-Sub. If it is a subform, the syntax it is wrong. If it
is a table, where does it fit into the picture? Things like that. You can
see your project, and you understand the real-world situation behind it. We
cannot do either without some details from you.

OK Tried this using

Dim stCount As Variant

stCount = Me.[Quantity]

Do While stCount > 0

Me![HWAlbuquerqueLabel-Sub]![SerialNumber] = DMax("[SerialNumber]",
"[HWAlbuquerqueSerialNumbers]")
Me![HWAlbuquerqueLabel-Sub]![SerialNumber] =
Format([HWAlbuquerqueLabel-Sub]![SerialNumber], "0000")
stCount = stCount - 1
Loop

A couple of things go wrong one if I add the plus 1 to the DMAX function
the
system gives a Type Match error because it is a text
Also, when it repeats the "XX" twice then three times, etc.
example the first one will be XX0001 then XXXX0001 then XXXXXX0001

Another part of this is I want the code to create a new record for each
serial number until the entered quantity is reached.

Sorry, forgot to add 1. It should have been:
=DMax("[SomeField]","[YourTable]") + 1

You should be able simply to format the text box. Set its Format
property
to:
0000

Or you can use the format function:
="XX" & Format([SomeField]),"0000")

It is getting closer... The code for DMAX () works but gives the last
one
used. When I tried just +1 it removed the zeros that were in place
So if the last issued number was 0006 it returns a 7


:

You wrote:
"User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004"

Is "XX" the prefix no matter what, or are you using XX to represent a
prefix
that could change? If the former, bind a text box to the incrementing
field
and set its Default Value to:
=DMax("[SomeField]","[YourTable]")
Since you are placing "XX" in front of every number, just concatenate
it
as
needed. In the same text box the Control Source could be:
="XX" & [SomeField]

I think this is a case where the text box needs to have a different
name
than the field.

If the latter, what is the source of the prefix? Is it a field in the
record, or what exactly?

1. Yes, the number will always start with the "XX" value. It is a
code
for
the item.
2. The numbers followi ng the item code is a four digit number that
can
be
0001 to 9999. But I do need the system to represent the zeros.
Also, I do the issued numbers to be in a table for later use. I am
time
stamping when they were issued. But that code works.
By the way thanks for the help....
this has been eating at me and I know it is simple I just can not
seem
to think of it.

:

First, two questions ......
1. Does your number always start with "XX"?
2. What happens when you have 1000? Are your numbers XX1000 to
XX0001?

You don't need to store all those numbers! You just need to store
how
many
you have at the current time (say 1000) then use a function to
generate
all
the numers XX1000 to XX0001 when you need the numbers.

Steve



OK here is the situation. I need to be able to generate a list of
numbers
based on a quantity and item number.
For example:
User says I have 2 of item ABC
the system should give out XX0001 and XX0002
next time 2 of item ABC
the system should give out XX0003 and XX0004
I want it to keep these issued numbers in a table.
I tried a while loop but I think there is something wrong...
If anyone can help I would muy appreciate it.

 

[Clifford Bass]

Hi Mari,

Since the XX is constant, you should not store it, as the others said.
Also, while Access allows it, Number is not a good name for a field. Rather
use something like ItemNumber. Number can get misinterpreted. Do a search
in your online help for "reserved words" to see the ones that are better to
avoid.

Anyway, in your code for adding new items, do something like this
(untested):

Dim intIndex As Integer
Dim intNewNumber As Integer

intNewNumber = Nz(DMax("[Number]", "[your item table]"), 0) + 1
DoCmd.SetWarnings False
For intIndex = 1 To [Quantity]
DoCmd.RunSQL -
"insert into [your item table] ([Number]) values (" & intNewNumber &
")"
intNewNumber = intNewNumber + 1
Next intIndex
DoCmd.SetWarnings True
Me![subformname]![Form].Requery

If you have other required fields in your item table, you will have to
supply those field names and some values for them also inside of the
parantheses, separated by commas.

Hope that helps,

Clifford Bass

The table is a simple sub table attached to the main table of items.
Main Table-information on the item
Sub Table - Has the list of numbers that have been issued
The "XX" in the beginning does not change because it is the item ID tag.

Hi Mari,

You will need to store the results somewhere. And then before
generating the numbers you will need to check to see what value was last
used. Added questions: What is the table where you are storing the new
values? If you have an item DEF, does it also get assigned the XXnnnn? Or
does it get assigned YYnnnn?

Clifford Bass

Dim stCount As String

stCount = [Quantity]

Do While stCount > 0
Me![Number] = [Number] + 1
stCount = stCount - 1
Loop
But one of the major problems with this is it does not hold the number from
a previous record. This would just start it over everytime.

 

[Mari]

I really do appreciate the help so they me be clear with what I want:
1. There is a main table that has a set of information, similiar to a
shipping log. Things like ship number, item number, date, quantity.
- this will be entered everytime they go to ship the parts
2. There is a sub-table attached. That assigns a new number to each part
being shipped. So if in the main they have Part XYZ and they are shipping 2,
I want the system to issue the next set of numbers for that specific part.
The numbers will repeat under different items
3. I want the system to generate these numbers automatically based on the
quantity given in the main table. (The numbers should be stored in the sub
table)

I am using forms for the user entry, and then a button for the generation of
the serial numbers. (Which are in a sub form)

Dim stCount As Variant
stCount = Me.[Quantity]
Do While stCount > 0
Me![Sub-Form]![SerialNumber] = DMax("[SerialNumber]", "[Sub Table, Where
the numbers will be stored]") + 1
Me![Serial Number sub Form]![SerialNumber] = Format([Serial Number Sub
Form]![SerialNumber], "0000")
stCount = stCount - 1
Loop
I put the identifiers for what each item is. I did not originally include
the sub form pretext but the system gave an error for not being able to find
the field.

 

[BruceM]

I refer you to another part of the thread, where Clifford suggested a way to
insert a series of records. It now seems that the prefix will vary
depending on the part number, so you will need a Where condition for the
DMax. Also, I have heard it said that something like this:
DBEngine(0)(0).Execute strSQL, dbFailOnError
where strSQL is the INSERT INTO statement. However, DoCmd.RunSQL works,
too, but is less desirable if for whatever reason the record is not created
(as I understand it).
The comments are based on Clifford's suggestion. Again, I suggest checking
out that part of this thread.

 

[Mari]

OK I tried this changing the related identifiers,

Dim intIndex As Integer
Dim intNewNumber As Integer

intNewNumber = Nz(DMax("[SerialNumber]", "[SerialNumbers-Sub]"), 0)
+ 1
DoCmd.SetWarnings False
For intIndex = 1 To [Quantity]
DoCmd.RunSQL -"insert into [SerialNumbers-Sub] ([SerialNumber])
values (" & intNewNumber & ")"
intNewNumber = intNewNumber
Next intIndex
DoCmd.SetWarnings True
Me![Label-Sub]![Form].Requery
Every time I get an error for type mismatching. I believe this is for the
fact that the serial number field is a text because of the "XX" ID in front
of the numbers.
Also, I question whether this would repeat until I reach the quantity
entered. Should I have left in the loop??

Hi Mari,

Since the XX is constant, you should not store it, as the others said.
Also, while Access allows it, Number is not a good name for a field. Rather
use something like ItemNumber. Number can get misinterpreted. Do a search
in your online help for "reserved words" to see the ones that are better to
avoid.

Anyway, in your code for adding new items, do something like this
(untested):

Dim intIndex As Integer
Dim intNewNumber As Integer

intNewNumber = Nz(DMax("[Number]", "[your item table]"), 0) + 1
DoCmd.SetWarnings False
For intIndex = 1 To [Quantity]
DoCmd.RunSQL -
"insert into [your item table] ([Number]) values (" & intNewNumber &
")"
intNewNumber = intNewNumber + 1
Next intIndex
DoCmd.SetWarnings True
Me![subformname]![Form].Requery

If you have other required fields in your item table, you will have to
supply those field names and some values for them also inside of the
parantheses, separated by commas.

Hope that helps,

Clifford Bass

The table is a simple sub table attached to the main table of items.
Main Table-information on the item
Sub Table - Has the list of numbers that have been issued
The "XX" in the beginning does not change because it is the item ID tag.

Hi Mari,

You will need to store the results somewhere. And then before
generating the numbers you will need to check to see what value was last
used. Added questions: What is the table where you are storing the new
values? If you have an item DEF, does it also get assigned the XXnnnn? Or
does it get assigned YYnnnn?

Clifford Bass

:

Dim stCount As String

stCount = [Quantity]

Do While stCount > 0
Me![Number] = [Number] + 1
stCount = stCount - 1
Loop
But one of the major problems with this is it does not hold the number from
a previous record. This would just start it over everytime.

 

[Clifford Bass]

Hi Mari,

The For-Next loop replaces your loop. Since you are storing the XX,
adjust it to this:

intNewNumber = Nz(DMax("Right([SerialNumber], 4)", "[SerialNumbers-Sub]"), 0)
+ 1

and

DoCmd.RunSQL "insert into [SerialNumbers-Sub] ([SerialNumber]) _
values (""XX" & Format$(intNewNumber, "0000") & """)"

Clifford Bass

 

[Mari]

OK- I am back from vacation, and this still is not working.
I changed the two lines and now I get a message of the system not being able
to find the form referred to ( last line of the code, the requery code line)
Then when I remove that line, the system does nothing when the code is ran.
I know the information would not appear but it should still be in the
underlying table shouldn't it.