If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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!! |
#2
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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" 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!! |
#3
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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? "UpRider" wrote: 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" 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!! |
#4
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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" 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? "UpRider" wrote: 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" 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!! |
#5
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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" wrote: 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" 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? "UpRider" wrote: 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" 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!! |
#6
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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" 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. "UpRider" wrote: 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" 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? "UpRider" wrote: 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" 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 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!! |
#7
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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" wrote: 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" 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. "UpRider" wrote: 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" 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? "UpRider" wrote: 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" 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 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!! |
#8
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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 "Bryan" wrote in message ... 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! |
#9
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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 "UpRider" wrote in message ... 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 "Bryan" wrote in message ... 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! |
#10
|
|||
|
|||
Creating Serial Numbers based on inserted qty
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. "UpRider" wrote: 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 "UpRider" wrote in message ... 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 "Bryan" wrote in message ... 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! |
|
Thread Tools | |
Display Modes | |
|
|