Hello,
I'm developing a program for a small non profit association.
I have this issue that I need it to do, but 1) I have no idea if it can be done 2) no idea how to do it (sort of).
This database deals with cats.
I have a table of Breeds and abreiviations
a Table of colors and abreiviations
a Table of Patterns and abreiviations
and a Table of Sexes with a corresponding number.
what I would like it to do:
The user inputs the information above about a cat through a series of combo boxes on a form, (I know how to display 1 value and store another), thats fairly straight forward.
Here's the tricky part.
As the user selects from the boxes I need it to automatically fill in another Field with the corresponding stored values, in order (this becomes part of the registration number).
Then I need it to assign a four digit number on the end to complete a unique reg number for the cat.
Example:
Breed: Persian 1st part of reg #: PER
Sex: Male 2nd part of reg #: 01
Color: Black 3rd part of reg #: 05
Pattern: Solid 4th part of reg #: SOL
So by user input we have:
PER0105SOL-
Since there can be Many Solid black male persians
each one needs it's own 4 digit unique number attached at the end, such as 2001.
Once that happens the whole number PER0105SOL-2001
will be stored with the cats name and such i another table called simply Cats.
Ideally, the 4 digit number should start with a 2 as they already use a program written about 13 years ago in Dbase that uses a 0 or a 1.
I know it's alot, Im not asking anyone to do it for me, just for some help, if this is even possible..
In one of my applications, I build the PO number is a simular fashion as you want to build this cat registration string. Here isthe code I used for the PO number.
Code:
'This function will create the PO number from the
' lot number, job number, manufacturer, and the
' initials of the street name.
CreatePONumber = Left(frm!Manuf, 1) & frm!ProjNum & _
frm!LotNumber & StreetInitials(frm!JobAddress)
"frm" is a form object passed into the function from which this code was copied.
hold a table in your database which stores a 4-digit code (the one you'd like to start with, eg 2001). This will be the only entry in the that table. Then read this off to complete your cat number, add 1 to it ready for the next moggy and your application will be purr-fect.
Say the table is CodeDigits and the field is Code. with a value of 2001. Put a command button on your form. In the code module for the form add this code for the click event of the command button:
dim db as database, myCode as string
dim rs as recordset
set db = currentdb
set rs = db.openrecordset("Codedigits",2)
rs.movefirst
myCode = rs!Code ' 4-dig code for this cat
rscode.edit ' now we'll update the code
rs!Code = rsCode +1 ' add 1 to the code
rscode.update ' store the codeready for next cat
now do something like :
CompleteCatCode = left(txtBreed,3) & left(sex,1) etc etc & add MyCode at the end
Now save the CompleteCatCode to your database. no problem
hi Ken
===============
I tried this code and I got the following error:
dim db as database, myCode as string
"User defined type not identified"
===============
I don't know what's happening here - if you are programming in Access then it automatically recognises 'database' as an object type (you need to set a reference to Microsoft DAO if you're in eg VB6). Which version of Access are you on? Try separating the declaration lines :
dim db as database
dim myCode as string
and tell me how you get along
================
now do something like :
CompleteCatCode = left(txtBreed,3) & left(sex,1) etc etc & add MyCode at the end
Where would this go??
================
wherever you want to store your Cat Code, presumably in your table of Cats, and once you have added the 4-digit code you can make it the primary key (good plan, prevents dupliCATs, ha ha).
I was thinking about your question a bit more deeply, you might not realise that you need a table of individual cats. In the end, despite being a puss of a colour and a type etc, it has to be an individual cat
I can give you a much better design than codes.
email me, I'll sort it for you, in memoriam of my short-lived ginger visitor of last week.
I never knew his name, no-one came forward for him despite adverts, and he turned out to have kidney failure, the vet said. My wife, dog and cat all let him in against their natural feelings, so his last week was warm and well-fed. I miss him.
KTaylor - Try adding the characters DAO. in front of your database and recordset dim statements:
dim db as DAO.database, myCode as string
dim rs as DAO.recordset
You also may need to add a reference to the DAO objects. Open up a VBA code window, click TOOLS / REFERENCES and look for "Microsoft DAO Object Library". Put a checkmark in it's box and see if that helps.
PETER - Sorry for the loss of your pet. They do become family members don't they.
Allan - Each individual client transaction requires a number of steps, most are internal actions which are simply saved as time and date stamps, some require client and/or managerial notifications. These are to be saved as pdf's and emailed accordingly. Rather than save the date, I number the report and save the report number which also coincides with the pdf file name. Report numbering is also a customer requirement as well as an ISO requirement. On a monthly basis, a collection of all completed transactions will be emailed to the client along with a summary report as well as a quarterly and annual report, the quarterly and annual reports adding detailed financial info. Everything is keyed to an internal transaction number so that historical lookups will be based on that number. The app is currently planned to be used for a specific client, however much of the info generated is applicable to other clients so I've designed things so that with a little reconfiguration the app will allow the user to select the client and based on client info stored in a table, allow variations in workflow(looking forward) The numbering solution you gave works quite well and will serve us in several areas such as generating the original work order #, etc. Thanks to you and others in this forum, the app is getting closer to completion.
I will send you a copy of the db later in the week after I've smoothed out a few rough spots. Its larger than the forum allows so I'll email it to you. I will post a copy of the part that pertains to this forum also.
Sorry about getting off-track on the original posting about serializing Cats but I think the original question is a basic problem that applies to all sorts of applications, including my own.
Here is a sample db that remembers the last work order number (any number of digits) and updates it by 1 each time a new work order (or cat serial number) is added. Put your starting number in the WorkID field of the NextWorkNum Table. The resulting number can be concatenated with the rest of the cat's identification features to get a unique cat ID.
I'm using this method to generate work order #'s and report #'s in my app. What's nice is that if you cancel out from updating etc., you don't have to reset the number to the last #, it only updates to the next available # after you've done whatever.
Here is another version of your WorkNUm form. I have created a form frm_work_number that prompts the user to Save or abort entry. I tend to use unbound fields when adding records then use the Insert method to append to the relevant tables.
If the user selects Yes then it validates the entry, stores the entry details and then updates the next record number.
If the user selects No then the form closes without updating etc.
Just one question, why is the WoDate store as Now() this will cause problems when selecting reports by a date range?
In this particular example, Date() would make more sense - who cares what time the work order was issued, in my application some of the numbered reports refer to when a truck is unloaded or loaded. Having the time logged gives a reference that allows management to be able to review video logs if they suspect someone is playing 'games' with inventory. It also allows management to calculate how long it takes to unload or load a truck (start time is logged when the work order is selected to be processed) The work order is the primary key to the entire process and each step on the way to completion is logged and reported. The initial work order number will have Date() and the final completion report will have Date(), everything in between will be Now()
Nice sample db - I like the way you addressed error conditions
Reply With Quote
4 digit sequential serial update
