Don't know if this is possible

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..

I appreciate your time!
KT

Yes, it is possible.

Thanks MAK!
Would you elaborate please?

KT

KT,

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.

Hope this helps,
Vic

Thanks Vic!
I'll give this a go on Monday and see how well I fair...

KT

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

Peter,

I tried this code and I got the following error:
dim db as database, myCode as string
"User defined type not identified"

Also:

now do something like :
CompleteCatCode = left(txtBreed,3) & left(sex,1) etc etc & add MyCode at the end

Where would this go??

Thanks!
Ken

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).

you can email me at sbsmerseyside@onetel.net if you'd like some direct help

all the best for the cat project, I took a stray in myself last week but sadly it was too ill to make it.

Peter Hall

Ken,

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.

Peter Hall

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.

Thank you Peter

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

Thanks again,
Cyber-guy

Here is another option

Create a table with one record and two fields

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.

Allan
allanmurphy@unwired.com.au

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.

Sorry for the omission.

Allan
allanmurphy@unwired.com.au

Alan, another slick solution!

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!

Cyber-guy

So you want to print a new report using the next report number and also store the details of the report e.g. report number and report details for future reprinting, emailing etc. ?

This opens up another option where you could save the report details as above in a table then use the DMAX function to determine the last number of the report printed to determine the next number.

Could you please send me a copy of your database and I will use it as guide to post an example on the forum so that other users can follow the steps etc.?

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.

Cyber-guy

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.

Hope this helps

cyber-guy

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?

Allan

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

Cyber Guy

Thank you. Let me know if require any more assistance.

Allan