Results 1 to 4 of 4
  1. #1
    brpathirana is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    4

    Generate an AutoNumber for falling under two different categories

    I am creating a database relates to hospital medical equipment using Access 2003. Using VBA code, I want to generate an AutoNumber for each Equipment falling under different Equipment and different Hospital.

    Ex: DGG-ECG-001
    DGG-ECG-002
    DGG-XRY-001
    WBH-ECG-001
    WBH-XRY-001


    WBH-XRY-002 and so on…..

    Where DGG & WBH are Hospital Codes (PK in tblHospitalCodes) ; ECG & XRY are Equipment Codes (PK in tblEquipmentCodes) ; last 3 digit denotes the AutoNumber.
    Both the Hospital & the Equipment select using combo box then generates the AutoNumber Using command Button.

    I am new to Access and VBA and much appreciate if someone can help me.

    Thanks
    Buddhika

  2. #2
    alansidman's Avatar
    alansidman is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Apr 2010
    Location
    Steamboat Springs
    Posts
    2,529

  3. #3
    orange's Avatar
    orange is offline Moderator
    Windows XP Access 2003
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    I suggest you read/reread the attachment to http://forums.aspfree.com/microsoft-...es-208217.html

    One fact, one field.
    What will you do when you add another Hospital or another equipment code or have more than 999 items at a Hospital in a specific Equipment code?
    What are the purposes of the "-" characters? What happens if euqipment is transferred between Hospitals?
    Why not an autonumber for equipment and separate fields identifying Hospital and equipment type?
    Just curious.

  4. #4
    brpathirana is offline Novice
    Windows XP Access 2003
    Join Date
    Jun 2012
    Posts
    4
    Thanks for your reply.

    I work at Medical Equipment repairing centre and receive the Equipment for repairing form various hospitals. For distinguishing the equipment, I sticked a label on the Equipment as in above format.
    So now the label format cannot be changed.
    Otherwise there are no more than 999 same Equipment in one Hospital.



    Another Hospital or Equipment and specific code could be added to another Sub form created from both tables tblHospitalCodes or tblEquipmentCodes.

    Here are my tables
    Table1: tblEquipment

    Field Name/Type/Size/Required/Indexed
    HospitalID (FK) /Number/Long Integer/No/Yes (Duplicate OK)
    EquipmentCodeID (FK) /Number/Long Integer/No/Yes (Duplicate OK)
    Hospital (PK) /Text/50/Yes/Yes (Duplicate OK)
    Equipment / Text/50/Yes/No
    EquipmentID(PK) /Text/11/Yes/ Yes (NO Duplicates)
    Make, Model and so on…. some text fields

    Table2: tblEquipmentCodes

    EquipmentCodeID (PK) /AutoNumber/Long Integer/ Yes /Yes (NO Duplicates)
    EquipmentCode /Text/50/ Yes /Yes (NO Duplicates)
    Equipment / Text/50/Yes/ Yes (NO Duplicates)

    Table2: tblHospitalCodes

    HospitalID (PK) /AutoNumber/Long Integer/ Yes /Yes (NO Duplicates)
    HospitalCode /Text/50/ Yes /Yes (NO Duplicates)
    Hospital / Text/50/Yes/ Yes (NO Duplicates)

    There are few many tables such as tblMaintenance, tblSupplierDetails, tblAccessories,…. link with the Table; tblEquipment; EquipmentID(Field); One To Many Relationship.

    Main Form Details:
    Name:MedicalEquipment, Record Source: tblEquipment, Data Entry : No,
    Unique ID Number Field Name: EquipmentID(Control Source= EquipmentID)

    Hospital name displays in large Text box;Hospital(Control Source= EquipmentID, Locked: Yes) in form header and selected by a unbound combo; cboHospital.
    The purpose was created this combo is to filter records with other unbound combo; cboEquipmentID.

    Task:
    For a new record, First select cboHospital and then cboEquipment and generate unique ID using command button.


Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 05-01-2012, 03:52 AM
  2. Holidays falling between dates
    By Alex Motilal in forum Queries
    Replies: 1
    Last Post: 11-15-2010, 11:59 PM
  3. Can't Right Click Categories
    By netchie in forum Access
    Replies: 2
    Last Post: 10-26-2010, 10:48 AM
  4. Replies: 3
    Last Post: 08-29-2010, 06:34 AM
  5. Combo Box sub categories
    By workindan in forum Access
    Replies: 1
    Last Post: 06-17-2010, 09:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums