Generate an AutoNumber for falling under two different categories (1 Viewer)

[brp]

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.

Here are the table details;

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)

New Hospitals, Equipment or Codes could be added to a sub form created form tblEquipmentCodes and tblEquipmentCodes.

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 in Main form;MedicalEquipment.

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

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

Thanks
Buddhika

 

[John Big Booty]

Welcome to the forum.

You should be able to use the Dmax() function plus one to increment your equipment number.

You can then construct the full equipment code as required by pulling together all of the elements you require.

The attached database demonstrates how this might be done.

 

[brp]

Thank you very much for your reply.
This is exactly what I wanted.

According to your example the "unique ID Number" is stored in an unbound text field. But I need to store these ID numbers in a table.

So please advice me how to do it? or According to your example, how to change unbound "unique ID Number" text field into bound field to store data?

 

[John Big Booty]

No, the raw autonumber is stored at table level, and is directly related to that record. The Unique code (also directly related to the record), is only pulled together when there is a need to display that code. As the unique code is a construct of a number of different components that are already stored, it is considered to be a calculated field and as such it is (generally considered) bad form to store such values.