Design by Sue
Registered User.
- Local time
- Yesterday, 21:45
- Joined
- Jul 16, 2010
- Messages
- 816
Database has existing UPC codes.
A. Existing codes can start with several different 6 digit combinations, i.e. 123456, 234567, 345678, 456789.
B. The following 6 digits if the UPC code have not necessarily been assigned in numerical order, so therefore the existing UPC codes for 456789 could be 000001, 0000004, 000008 etc (these are obviously not the codes, just samples to try to describe what I need to do)
C. The last digit is the number that is created mathematically and as I understand it is a check number for the other digits.
D. These UPC codes are stored in 4 tables as there are 4 different types of objects the require UPC codes and they are maintained in these 4 tables.
Now what I have to do: When the user enters a new item into the database, I need to give them the option of creating a new UPC code for that item.
This new UPC code needs to start with 456789. Access will need to check the database for any UPC that starts with 456789, then find what is the next available number available (of the 2nd set of 6 digit number) across the 4 tables and assign that number to the new item. It will also have to create the 13th digit according to the mathmetical formula for doing so.
Any help on how to accomplish this will be greatly appreciated. The immediate questions that come to my mind are:
1. Can this 13 digit number be handled as one string or do I need to split the UPC into 6, 6 and 1? It seems to me that this would be the way to do this as I could find all existing UPC codes that have 456789 in the "First 6" column, then sort to find the next available number in the "Second 6" column, then do the formula to create the "Last 1".
2. If I do this split, I am thinking that to display this on the forms and reports, I would just format the number to look as one even though it was 3 fields. correct?
3. Any help on coding this will greatly be appreciated, especially on how to find the next available number, but at this point a confirmation of possility and the best way to do it will help.
Sue
A. Existing codes can start with several different 6 digit combinations, i.e. 123456, 234567, 345678, 456789.
B. The following 6 digits if the UPC code have not necessarily been assigned in numerical order, so therefore the existing UPC codes for 456789 could be 000001, 0000004, 000008 etc (these are obviously not the codes, just samples to try to describe what I need to do)
C. The last digit is the number that is created mathematically and as I understand it is a check number for the other digits.
D. These UPC codes are stored in 4 tables as there are 4 different types of objects the require UPC codes and they are maintained in these 4 tables.
Now what I have to do: When the user enters a new item into the database, I need to give them the option of creating a new UPC code for that item.
This new UPC code needs to start with 456789. Access will need to check the database for any UPC that starts with 456789, then find what is the next available number available (of the 2nd set of 6 digit number) across the 4 tables and assign that number to the new item. It will also have to create the 13th digit according to the mathmetical formula for doing so.
Any help on how to accomplish this will be greatly appreciated. The immediate questions that come to my mind are:
1. Can this 13 digit number be handled as one string or do I need to split the UPC into 6, 6 and 1? It seems to me that this would be the way to do this as I could find all existing UPC codes that have 456789 in the "First 6" column, then sort to find the next available number in the "Second 6" column, then do the formula to create the "Last 1".
2. If I do this split, I am thinking that to display this on the forms and reports, I would just format the number to look as one even though it was 3 fields. correct?
3. Any help on coding this will greatly be appreciated, especially on how to find the next available number, but at this point a confirmation of possility and the best way to do it will help.
Sue
