Generate an AutoNumber for falling under two different categories

[brpathirana]

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

[ssanfu]

You can't have an "Autonumber" like you want. You can use an autonumber AND have a "Custom Number" field. In your case, you would need three fields: 1 for the Hospital Codes, 1 for the Equipment Codes and 1 for the sequence number (not autonumber).

This should help: http://www.baldyweb.com/CustomAutonumber.htm