Medical Record number

[Guest]

I am struggling for many days without a solution. Can you guys help me ?
How do I get access to generate a unique ID number for my patients database
which is unique and not as negative ID number?

I will have multiple tables in the database like :
1. Patients demographic data table
2. Patients admission table
3. Doctors table
4. charges table
5. Diagnosis table
etc.
Each will have a primary ID as an autonumber field which will increment .
But I want the software to generate two IDs for me.
The Patients......demo....table is linked to the admission table via one -
many relationship. as one patient can get admitted many times.

But each patient should have permanently only one patient ID which is unique
and each admission should have one admission ID which is also unique. I
don;t want these numbers to be like 1, 2, 3 etc an autoincrement field which
will increment.
It should be like many other commercial softwares like
AKLM/0123/8976-546

Can you help Me ?

Thanks for your time

Dr Alok Modi MD

 

[Guest]

This is a common request and not that hard to do. Not knowing the logic
behind constructing a patient number, I can't give exact code here, but
hopefully, this will get you far enough along to complete this task.

To get a new number, first use the DMAX function which will return the
highest current number:

lngNxtNbr = DMAX("[PatientID]","PatientDemoGraphics")

Now check to see if this is the very first patient number:
then you need to increment the number based on your numbering rules.
If it is numeric:

If IsNull(lngNxtNbr) Then
lngNxtNbr = 1
Else
lngNxtNbr = lngNxtNbr + 1
End If

If it is text, then you have to be sure you format it correctly.

The only other issue would only be a problem if you are in a multi user
environment. That is because you have looked up the high number and created
the next number. If two users did this at the same time, one would overwrite
the others changes or would get an error depending on your locking scheme.

The easiest way to address that is to verify the number is still available
before you update the record:

If IsNull(DLookup("[lngNxtNbr]","PatientDemographics")) Then
'OK to update database
Else
'The number has been used
'Create a new number or show an error or whatever you want
End If

 

[Guest]

Thanks Klatuu
I am new to access and not much hangs with VB.
So help me here :
1. Wher do I enter this expression . Should it be at the table level or
should it be at the control source at form level or is it amdule that I have
to create?

2. I don't want only number as my patient ID. I also want text letters
generated. Se the example in my question above?
SO how do I generate a mix of text and number ?
So how do i go about doing that?

Thanks

Dr Alok Modi MD

 

[Guest]

Where you put the code depends on how your form operates and whether it is a
bound or unbound form, so it is hard to say without your database in front of
me.

I think, probably in the Current event of your form, you will have to
determine if it is a new record and if it is, create the next number.

This will also take some fairly advanced VBA to get it to work correctly.
For example, when you need to increment letters, you have to convert them to
a numeric value, add 1 and convert int back to a string:

Let's assume strLtr = "A"
strLtr = chr(asc(strLtr)+1)
Now it is "B"

With all that said, I don't know how I can help without your database to
work with.

 

[Guest]

Hi Klatuu

Is it possible for me to upload my database for you to help me. I am not
conversant much with Vb so I if I can get some help from you?

Thanks
Dr Alok Modi MD

 

[Guest]

How large is your database? Could it be zipped using Winzip and E-mailed?
Post back with an E-mail address, and we can work this out.

 

[Guest]

My database is 988 KB. and after zipping it is 185 kb.
My e mail address is

(e-mail address removed) the same with which I log on to these discussion groups.

Thanks

Dr Alok Modi MD