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.
I too had a need for a similar lookup - my app needed to number reports sequentially - I had a problem using the code but did manage to make it work - here is my version:
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("CORnum", 2)
rs.MoveFirst ' go to first record
[COR-ID] = rs![COR-ID] ' 4-dig code for this report
With rs
.Edit ' update the COR-ID
![COR-ID] = rs![COR-ID] + 1 ' add 1 to report #
.Update ' store the result for next report
End With
The table is "CORnum", the field is "COR-ID"
I have several other modules that need sequential numbers - inventory numbers, order numbers, etc. and will use this same method. The method I was using is too complex and might allow the possibilty of duplicates if another user did a similar action.
One question Peter, what is the '2' parameter in the openrecordset line - I couldn't find any documentation on it
I will call the table tbl_report_number. the fields are called report_ref data type autonumber. and report_number type Integer Long
THERE WILL BE ONLY ONE RECORD.
Each time AFTER you run a VALIDreport you update the report_number in the tbl_report_number using the following code or you create a query using the sql code
dim sqltext as string
sqltext= "UPDATE tbl_report_number SET tbl_report_number.report_number = [report_number]+1 " & _
WHERE (((tbl_report_number.report_ref)=1))"
docmd.runsql sqltext
You must not update the table if there is no report printed
I could post a sample database to the forum.
In my previous reply I forgot to add that you will need a field on your report to display the report number (rpt_number). In the print event of the report header add
rpt_number=dlookup("[report_number]","tbl_report_number","report_ref=1").
THen you update the table as per the previous reply.
Please post a sample db of it. The way I implemented the first sample was to only update if the report was printed. The report number is saved in the app for future reference, reprinting, etc. The next step in the report process is to save it and email it, hopefully as a pdf. That's today's project!