Creating Serial Numbers based on inserted qty

[Bryan]

Not quite sure how to word this, but what I am trying to accomplish is to
have the user fill in a few fields, then Quantity. Once Qty is added I need
to have a list of serial numbers displayed in my subform. My serial numbers
are created from a DateCode module and then combined with a 4 digit serial.
I am creating them one at a time right now using the "on lost focus"event
with the serial number resident in my main form. Works great, but if I need
25 serial numbers for one product, I need more automation. Fyi, bigger
picture is to then print the labels, (Print Labels? checkbox) and print word
docs containing the same serial numbers. Though I've written a number of
DBs, I am drawing a complete blank on where to begin here. Thanks in advance
for your help!!

 

[UpRider]

Bryan, conceptually, I would use a list box to hold the serial numbers.
After the quantity is updated, you would call a routine that handles
populating the list box and that routiine calls your existing DateCode
module the proper number of times.
Then your labels and Word reports work from the listbox contents.

HTH, UpRider

 

[Bryan]

Hey Uprider!
Thanks for the prompt reply. I had not thought of using a listbox here as
all of the Serial Numbers created for that product would be printed on
labels. That is why I was initially thinking of using a subform and
referencing that. Any ideas on how to write the routine that would populate
the listbox?

:

Bryan, conceptually, I would use a list box to hold the serial numbers.
After the quantity is updated, you would call a routine that handles
populating the list box and that routine calls your existing DateCode
module the proper number of times. Then your labels and Word reports work
from
the listbox contents.

HTH, UpRider

Not quite sure how to word this, but what I am trying to accomplish is to
have the user fill in a few fields, then Quantity. Once Qty is added I need
to have a list of serial numbers displayed in my subform. My serial
numbers are created from a DateCode module and then combined with a 4 digit
serial. I am creating them one at a time right now using the "on lost
focus"
event with the serial number resident in my main form. Works great, but
if I
need 25 serial numbers for one product, I need more automation. Fyi, bigger
picture is to then print the labels, (Print Labels? checkbox) and print
word docs containing the same serial numbers. Though I've written a
number of
DBs, I am drawing a complete blank on where to begin here. Thanks in
advance for your help!!

 

[UpRider]

Bryan, I envisioned the listbox as temporary storage to hold your serial
numbers until you printed the labels and created your Word docs. When you
close the form, the listbox would lose its contents. If you need more
permanent storage, a table would be better, of course.
You say you're creating the serial numbers now with the 'lost focus'. Do
you have 25 textboxes you have to type something into, and these are the
source of the 'lost focus'? I'm having a hard time visualizing your form
and what you are doing with it.

UpRider

 

[Bryan]

I do need more permanent storage. I am creating these serial numbers for
each product, ie, the customer only orders one Catalog number ABCD and I
issue serial Number 11111111. Another customer orders two QWERT and I issue
serial numbers 11111112 and 11111113. The serial number are a permanent
entity and will be used later for adding tested info to the product and for
reporting and tracking. I am upgrading from a spreadsheet that now has
around 25000 sequential serial numbers. As for the "lost Focus", I was just
stating how I was updating the serials now. I am just now writing this DB
and was creating one at a time which is not very productive.
What I am having trouble doing is using the qty to create the whole required
list of Serial numbers as they are issued, whether 1 or 100.

 

[UpRider]

OK. I gather that the 25000 from the spreadsheet are OK as is. Your concern
is having the database take over from now on when you continue with it,
abandoning the spreadsheet.
After you generate a series of serial numbers for a product, would you need
to be able go in later and extend the list of serial numbers?
If so, how do you know what number is next, or do you want the program to
automatically determine that for you by starting one higher than the
existing number?
Also, are the first 'n' numbers for a product serial number always the same?
Are all serial numbers the same number of digits?
Are serial numbers always numeric?
Can you list for me the structure of the table that holds the serial
numbers?

Nitty Gritty, UpRider

 

[Bryan]

Yes to the first paragraph.
The last four digits continually increment, after 9999, then back to 0000.
The first four digits are a four digit DateCode, ie, 0733, the first two the
year and the second two the week. This is generated by VBA in a module and
sits in an invisible field on the form along with the four digit
SerialNumber. I then combine them:
SN = Format([DateCode], "0000") & Format([SerialNumber], "0000")
That means that today it could be 07330001 and tomorrow, 07340002 as Monday
is the start of the new week. Serial Numbers then are never repeated for 100
years! Serial numbers are issued almost daily for a variety of Catalog
numbers with varying quantities.
Sample:
SN Product
07330001 12345
07330002 12345
07330003 67890
07330004 ZXCVB
07330005 ZXCVB
07330006 ZXCVB
07330007 ZXCVB
07340008 12345

At present the SerialNumber is an AutoNumber data type, but I plan to change
that to incrementing, (Last record plus 1).
SerialNumber AutoNumber
DateCode Text
Product Text (ComboBox)
Description Text
Qty Number
SN Text
PL Text (ComboBox)
SN_Requestor Text (ComboBox)
PrintLabel Yes/No
PrintTestProc Yes/No

Thanks for your help!

:

OK. I gather that the 25000 from the spreadsheet are OK as is. Your concern
is having the database take over from now on when you continue with it,
abandoning the spreadsheet.
After you generate a series of serial numbers for a product, would you need
to be able go in later and extend the list of serial numbers?
If so, how do you know what number is next, or do you want the program to
automatically determine that for you by starting one higher than the
existing number?
Also, are the first 'n' numbers for a product serial number always the same?
Are all serial numbers the same number of digits?
Are serial numbers always numeric?
Can you list for me the structure of the table that holds the serial
numbers?

Nitty Gritty, UpRider

"Bryan" <[email protected] wrote in message
I do need more permanent storage. I am creating these serial numbers for
each product, ie, the customer only orders one Catalog number ABCD and I
issue serial Number 11111111. Another customer orders two QWERT and I
issue
serial numbers 11111112 and 11111113. The serial number are a permanent
entity and will be used later for adding tested info to the product and
for
reporting and tracking. I am upgrading from a spreadsheet that now has
around 25000 sequential serial numbers. As for the "lost Focus", I was
just
stating how I was updating the serials now. I am just now writing this DB
and was creating one at a time which is not very productive.
What I am having trouble doing is using the qty to create the whole
required
list of Serial numbers as they are issued, whether 1 or 100.

:

Bryan, I envisioned the listbox as temporary storage to hold your serial
numbers until you printed the labels and created your Word docs. When
you
close the form, the listbox would lose its contents. If you need more
permanent storage, a table would be better, of course.
You say you're creating the serial numbers now with the 'lost focus'. Do
you have 25 textboxes you have to type something into, and these are the
source of the 'lost focus'? I'm having a hard time visualizing your form
and what you are doing with it.

UpRider

"Bryan" <[email protected] wrote in message
Hey Uprider!
Thanks for the prompt reply. I had not thought of using a listbox here
as
all of the Serial Numbers created for that product would be printed on
labels. That is why I was initially thinking of using a subform and
referencing that. Any ideas on how to write the routine that would
populate
the listbox?

:

Bryan, conceptually, I would use a list box to hold the serial numbers.
After the quantity is updated, you would call a routine that handles
populating the list box and that routine calls your existing DateCode
module the proper number of times. Then your labels and Word reports
work
from
the listbox contents.

HTH, UpRider

"Bryan" <[email protected] wrote in message
Not quite sure how to word this, but what I am trying to accomplish is
to
have the user fill in a few fields, then Quantity. Once Qty is added
I
need
to have a list of serial numbers displayed in my subform. My serial
numbers are created from a DateCode module and then combined with a 4
digit
serial. I am creating them one at a time right now using the "on lost
focus"
event with the serial number resident in my main form. Works great,
but
if I
need 25 serial numbers for one product, I need more automation. Fyi,
bigger
picture is to then print the labels, (Print Labels? checkbox) and
print
word docs containing the same serial numbers. Though I've written a
number of
DBs, I am drawing a complete blank on where to begin here. Thanks in
advance for your help!!

 

[UpRider]

OK. If we go with a Mainform/subform design, how do you want it to work?
How many products can you put on the main form at one time (One I hope)?
Then the mainform would be based on the PRODUCTS table or perhaps an ORDERS
table.
Then the subform would be based on the table below. (Both forms cannot be
based on the same table)
When you create a new ORDER for the PRODUCT, the subform is empty until the
QTY is filled in.
Then the subform populates with the same number of records in QTY. The
serial numbers start with the next higher number existing for this week, or
if none this week, last week.

What is the name of the below table?

UpRider

 

[UpRider]

Bryan, download http://www.dbtc.org/zipmdb/serialdemo.zip
It's a form/subform setup that demos what you want to see your db do.

UpRider

OK. If we go with a Mainform/subform design, how do you want it to work?
How many products can you put on the main form at one time (One I hope)?
Then the mainform would be based on the PRODUCTS table or perhaps an
ORDERS table.
Then the subform would be based on the table below. (Both forms cannot be
based on the same table)
When you create a new ORDER for the PRODUCT, the subform is empty until
the QTY is filled in.
Then the subform populates with the same number of records in QTY. The
serial numbers start with the next higher number existing for this week,
or if none this week, last week.

What is the name of the below table?

UpRider

Yes to the first paragraph.
The last four digits continually increment, after 9999, then back to
0000.
The first four digits are a four digit DateCode, ie, 0733, the first two
the
year and the second two the week. This is generated by VBA in a module
and
sits in an invisible field on the form along with the four digit
SerialNumber. I then combine them:
SN = Format([DateCode], "0000") & Format([SerialNumber], "0000")
That means that today it could be 07330001 and tomorrow, 07340002 as
Monday
is the start of the new week. Serial Numbers then are never repeated for
100
years! Serial numbers are issued almost daily for a variety of Catalog
numbers with varying quantities.
Sample:
SN Product
07330001 12345
07330002 12345
07330003 67890
07330004 ZXCVB
07330005 ZXCVB
07330006 ZXCVB
07330007 ZXCVB
07340008 12345

At present the SerialNumber is an AutoNumber data type, but I plan to
change
that to incrementing, (Last record plus 1).
SerialNumber AutoNumber
DateCode Text
Product Text (ComboBox)
Description Text
Qty Number
SN Text
PL Text (ComboBox)
SN_Requestor Text (ComboBox)
PrintLabel Yes/No
PrintTestProc Yes/No

Thanks for your help!

 

[Bryan]

Looks cool when I open the form, however it has some problems I don't know
how to overcome.
1. It is using 3 tables just to create the serial number and then it
displays it in two fields. The amount of steps and code it took just to get
the Serial Number is like going around the block just to get next door! I
had already created the serial number and combined it into one eight digit
number without having to manually enter the DateCode or ever see it or the
four digit serial.
2. I don't know how to make use of the serial Numbers once I have them as
they are isolated from the rest of my data and don't seem to tie to anything.
3. Because the SNs are isolated, they just keep on sequencing irrespective
of the Product into one long list and I cannot separate them so that I might
print labels or documents based on the group. If I enter a product with a
five piece order, then that is what I need to print. If next I enter another
product with a three piece order, then I want to print those three. Plus
there are several other fields associated with that serial number that will
be used and related at a later time.
I hope I am making sense here as I know you are trying hard to answer my
question.

Bryan, download http://www.dbtc.org/zipmdb/serialdemo.zip
It's a form/subform setup that demos what you want to see your db do.

UpRider

OK. If we go with a Mainform/subform design, how do you want it to work?
How many products can you put on the main form at one time (One I hope)?
Then the mainform would be based on the PRODUCTS table or perhaps an
ORDERS table.
Then the subform would be based on the table below. (Both forms cannot be
based on the same table)
When you create a new ORDER for the PRODUCT, the subform is empty until
the QTY is filled in.
Then the subform populates with the same number of records in QTY. The
serial numbers start with the next higher number existing for this week,
or if none this week, last week.

What is the name of the below table?

UpRider

Yes to the first paragraph.
The last four digits continually increment, after 9999, then back to
0000.
The first four digits are a four digit DateCode, ie, 0733, the first two
the
year and the second two the week. This is generated by VBA in a module
and
sits in an invisible field on the form along with the four digit
SerialNumber. I then combine them:
SN = Format([DateCode], "0000") & Format([SerialNumber], "0000")
That means that today it could be 07330001 and tomorrow, 07340002 as
Monday
is the start of the new week. Serial Numbers then are never repeated for
100
years! Serial numbers are issued almost daily for a variety of Catalog
numbers with varying quantities.
Sample:
SN Product
07330001 12345
07330002 12345
07330003 67890
07330004 ZXCVB
07330005 ZXCVB
07330006 ZXCVB
07330007 ZXCVB
07340008 12345

At present the SerialNumber is an AutoNumber data type, but I plan to
change
that to incrementing, (Last record plus 1).
SerialNumber AutoNumber
DateCode Text
Product Text (ComboBox)
Description Text
Qty Number
SN Text
PL Text (ComboBox)
SN_Requestor Text (ComboBox)
PrintLabel Yes/No
PrintTestProc Yes/No

Thanks for your help!

 

[UpRider]

Bryan, your original post stated:

but what I am trying to accomplish is to
have the user fill in a few fields, then Quantity. Once Qty is added I
need
to have a list of serial numbers displayed in my subform.

and that is what my solution does.

It was not intended to serve as the completed code for your project, but to
show how to accomplish that specific task.
"The perils of requirements creep lurk at every project meeting".

UpRider

Looks cool when I open the form, however it has some problems I don't know
how to overcome.
1. It is using 3 tables just to create the serial number and then it
displays it in two fields. The amount of steps and code it took just to
get
the Serial Number is like going around the block just to get next door! I
had already created the serial number and combined it into one eight digit
number without having to manually enter the DateCode or ever see it or the
four digit serial.
2. I don't know how to make use of the serial Numbers once I have them as
they are isolated from the rest of my data and don't seem to tie to
anything.
3. Because the SNs are isolated, they just keep on sequencing irrespective
of the Product into one long list and I cannot separate them so that I
might
print labels or documents based on the group. If I enter a product with a
five piece order, then that is what I need to print. If next I enter
another
product with a three piece order, then I want to print those three. Plus
there are several other fields associated with that serial number that
will
be used and related at a later time.
I hope I am making sense here as I know you are trying hard to answer my
question.

Bryan, download http://www.dbtc.org/zipmdb/serialdemo.zip
It's a form/subform setup that demos what you want to see your db do.

UpRider

OK. If we go with a Mainform/subform design, how do you want it to
work?
How many products can you put on the main form at one time (One I
hope)?
Then the mainform would be based on the PRODUCTS table or perhaps an
ORDERS table.
Then the subform would be based on the table below. (Both forms cannot
be
based on the same table)
When you create a new ORDER for the PRODUCT, the subform is empty until
the QTY is filled in.
Then the subform populates with the same number of records in QTY. The
serial numbers start with the next higher number existing for this
week,
or if none this week, last week.

What is the name of the below table?

UpRider

Yes to the first paragraph.
The last four digits continually increment, after 9999, then back to
0000.
The first four digits are a four digit DateCode, ie, 0733, the first
two
the
year and the second two the week. This is generated by VBA in a
module
and
sits in an invisible field on the form along with the four digit
SerialNumber. I then combine them:
SN = Format([DateCode], "0000") & Format([SerialNumber], "0000")
That means that today it could be 07330001 and tomorrow, 07340002 as
Monday
is the start of the new week. Serial Numbers then are never repeated
for
100
years! Serial numbers are issued almost daily for a variety of
Catalog
numbers with varying quantities.
Sample:
SN Product
07330001 12345
07330002 12345
07330003 67890
07330004 ZXCVB
07330005 ZXCVB
07330006 ZXCVB
07330007 ZXCVB
07340008 12345

At present the SerialNumber is an AutoNumber data type, but I plan to
change
that to incrementing, (Last record plus 1).
SerialNumber AutoNumber
DateCode Text
Product Text (ComboBox)
Description Text
Qty Number
SN Text
PL Text (ComboBox)
SN_Requestor Text (ComboBox)
PrintLabel Yes/No
PrintTestProc Yes/No

Thanks for your help!

 

[Bryan]

Hey UpRider!
I have been wrestling with your solution for days and keep running into
roadblocks. I have added several fields to the subform as I need these for
tracking and for entering information later. They will need to be associated
with the created serial numbers, so the only way I knew to do that was to
place them in the subform. However, in order to not have to retype them with
each serial number, I have them being entered in frmMain in unbound fields
then being loaded into subfrmSerial by an After Update event. (I understand
the table, not form thing, I'm just explaining the visual!) I am not sure
quite how to explain it anymore, so can I ask you to take a look at it? I
have redone it from scratch several times and would pitch it if it wasn't
expected to be done. After a few dozen hours it is very small; a handful of
fields and the code you directed me to and my DateCode module. It'll be
short my lookup tables and such for simplicity. I think once you see it, it
will make sense. TIA

Bryan, your original post stated:

but what I am trying to accomplish is to
have the user fill in a few fields, then Quantity. Once Qty is added I
need
to have a list of serial numbers displayed in my subform.

and that is what my solution does.

It was not intended to serve as the completed code for your project, but to
show how to accomplish that specific task.
"The perils of requirements creep lurk at every project meeting".

UpRider

Looks cool when I open the form, however it has some problems I don't know
how to overcome.
1. It is using 3 tables just to create the serial number and then it
displays it in two fields. The amount of steps and code it took just to
get
the Serial Number is like going around the block just to get next door! I
had already created the serial number and combined it into one eight digit
number without having to manually enter the DateCode or ever see it or the
four digit serial.
2. I don't know how to make use of the serial Numbers once I have them as
they are isolated from the rest of my data and don't seem to tie to
anything.
3. Because the SNs are isolated, they just keep on sequencing irrespective
of the Product into one long list and I cannot separate them so that I
might
print labels or documents based on the group. If I enter a product with a
five piece order, then that is what I need to print. If next I enter
another
product with a three piece order, then I want to print those three. Plus
there are several other fields associated with that serial number that
will
be used and related at a later time.
I hope I am making sense here as I know you are trying hard to answer my
question.

Bryan, download http://www.dbtc.org/zipmdb/serialdemo.zip
It's a form/subform setup that demos what you want to see your db do.

UpRider

OK. If we go with a Mainform/subform design, how do you want it to
work?
How many products can you put on the main form at one time (One I
hope)?
Then the mainform would be based on the PRODUCTS table or perhaps an
ORDERS table.
Then the subform would be based on the table below. (Both forms cannot
be
based on the same table)
When you create a new ORDER for the PRODUCT, the subform is empty until
the QTY is filled in.
Then the subform populates with the same number of records in QTY. The
serial numbers start with the next higher number existing for this
week,
or if none this week, last week.

What is the name of the below table?

UpRider

Yes to the first paragraph.
The last four digits continually increment, after 9999, then back to
0000.
The first four digits are a four digit DateCode, ie, 0733, the first
two
the
year and the second two the week. This is generated by VBA in a
module
and
sits in an invisible field on the form along with the four digit
SerialNumber. I then combine them:
SN = Format([DateCode], "0000") & Format([SerialNumber], "0000")
That means that today it could be 07330001 and tomorrow, 07340002 as
Monday
is the start of the new week. Serial Numbers then are never repeated
for
100
years! Serial numbers are issued almost daily for a variety of
Catalog
numbers with varying quantities.
Sample:
SN Product
07330001 12345
07330002 12345
07330003 67890
07330004 ZXCVB
07330005 ZXCVB
07330006 ZXCVB
07330007 ZXCVB
07340008 12345

At present the SerialNumber is an AutoNumber data type, but I plan to
change
that to incrementing, (Last record plus 1).
SerialNumber AutoNumber
DateCode Text
Product Text (ComboBox)
Description Text
Qty Number
SN Text
PL Text (ComboBox)
SN_Requestor Text (ComboBox)
PrintLabel Yes/No
PrintTestProc Yes/No

Thanks for your help!