By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,390 Members | 2,050 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,390 IT Pros & Developers. It's quick & easy.

Generate an AutoNumber for falling under two different categories

P: 2
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
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.

Jun 9 '12 #1
Share this Question
Share on Google+
3 Replies

Expert Mod 5K+
P: 5,397
It would be helpfull to have more information about "tblHospitalCodes", "tblEquipmentCodes", and how they are related... third table? I am reluctant to suggest any method without this information and I suspect others will be too as there are several ways to create the information you are after and, in fact, you may already have the solution sitting in one of the tables.

Might I sugest that when you post the table's information you do something along these lines:
[Field_Name];type (ie: text(50) or numeric(long)...); if primary key then "PK" or forgein key "FK" and the relationship info if FK, if required, nulls (not)allowed.
(note: PK would imply that the field is required, no nulls, no duplicates, is indexed, etc...)

so for:
Expand|Select|Wrap|Line Numbers
  1. "tblHospitalCodes"
  2. [hospital_id],text(3),PK;
  3. [next_field_name],numeric(long),fk-1:m-tblhospitaldetails;etc....
Also of help would be a basic description of the form you've created... did you create it from scratch or let Access do this for you. What I'm after is information regarding the bound state of the form and the controls.

On a seperate note:

You've mentioned that you're new to Access and VBA; before you get too deep into the mess, one of the most common issues is the level of normalization in the database (I run into this issue occationally myself... less so now that I use a check list to create the database). Respectfully, without knowing your database, might I suggest a quick read at If you're not aware of this concept then the link is a nice explanation; otherwise, I found it be a good refresher.

Jun 9 '12 #2

P: 2
I much appreciate for your reply.

I kindly mention that Im not an Access or any other programmer in profession. I am a technical person related to medical equipment maintenance in various hospitals. But I committed to myself to make an equipment database to make easy my job.

Here are the 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)
Equ / 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.

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.

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

I kindly hope your reply.
Jun 9 '12 #3

Expert 2.5K+
P: 3,072
brpathirana has a good point in referring to normalization.
I would use three fields to store the code like:
Now you can determine the max SequenceNo by using a DMAX() function like:
Expand|Select|Wrap|Line Numbers
  1. intMax = DMAX("SequenceNo","tblEquipment","HospitalID='" & strHospitalID & "' and EquipmentID='" & strEquipmentID & "'"
Now incrementing intMax with 1 will give the value needed for the INSERT on the new equipment row.

One warning, using 3 digits will fail when >999 is reached. So I would make the SequenceNo an integer field and when showing it I would use e.g.:
Expand|Select|Wrap|Line Numbers
  1. RIGHT("000"&SequenceNo,3)
to get the needed string. When 999 is reached you'll only have to add a "0"and incrment the 3 to 4.

Jun 10 '12 #4

Post your reply

Sign in to post your reply or Sign up for a free account.