Page 1 of 2 12 LastLast
Results 1 to 15 of 21

Thread: Don't know if this is possible

  1. #1
    Join Date
    Mar 2007
    Location
    CT
    Posts
    4

    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
    Last edited by KTaylor; 03-29-2007 at 03:20 PM.

  2. #2
    Join Date
    Sep 2002
    Location
    Fantasy
    Posts
    4,254

  3. #3
    Join Date
    Mar 2007
    Location
    CT
    Posts
    4
    Thanks MAK!
    Would you elaborate please?

    KT

  4. #4
    Join Date
    May 2006
    Posts
    407
    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

  5. #5
    Join Date
    Mar 2007
    Location
    CT
    Posts
    4
    Thanks Vic!
    I'll give this a go on Monday and see how well I fair...

    KT

  6. #6
    Join Date
    Apr 2007
    Posts
    4

    and for the 4-digit code . . .

    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

  7. #7
    Join Date
    Mar 2007
    Location
    CT
    Posts
    4
    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

  8. #8
    Join Date
    Apr 2007
    Posts
    4
    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

  9. #9
    Join Date
    Apr 2007
    Posts
    4

    You need a table of individual cats

    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

  10. #10
    Join Date
    Apr 2007
    Posts
    4

    and here he was

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

  11. #11
    Join Date
    Oct 2005
    Location
    Washington state
    Posts
    17

    Error - user type not defined

    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.

  12. #12
    Join Date
    Apr 2007
    Posts
    22

    Thumbs up sequential number problem

    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

  13. #13
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275

    Sequential Report Number

    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

  14. #14
    Join Date
    Oct 2006
    Location
    Maitland NSW Australia
    Posts
    275

    Report number

    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

  15. #15
    Join Date
    Apr 2007
    Posts
    22

    sequential numbers

    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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •