Creating a unique ID (not autonumber)

[JJeffH]

I am transitioning an Excel database I made, into Access. It includes a table of volunteers and a table of patients. In Excel I came up with unique IDs for each group (VID and PID) which I use as primary keys in Excel. In Access I have autonumber primary keys, but the VIDs and PIDs are very useful for helping staff look up records.

The IDs are 4-character strings. The first two characters are the person’s first and last initials, followed by a two-digit number, consecutively added as new people are entered. That’s what I need help setting up in Access.

Here’s what I’ve done so far. In both the First and Last name fields of the data entry forms, I have VBA code on the BeforeUpdate event that checks two things: Is the VID (or PID) field empty; Are both the First and Last names entered. If those conditions are true, I put the initials in a variable “Initials”.

What I want to do is look up the initials in the ID field of the corresponding table, determine the highest number assigned, then assign the next number to this new person.

I have a query that will return all the existing instances matching the initials and sort it in descending order. I have a condition to prompt for initials which is: Like [Initials] & “*”.

I can call the query from VBA, but only with static criteria. I don’t know how to pass the Initials variable to the query, and I don’t know how to collect the results or determine the next consecutive number.

These first steps may be going in the wrong direction. I welcome any help with this or better suggestions. This is one of several steps in trying to better understand how to develop Access. I have a lot of trouble with Access syntax and procedures, but bit by bit I’ll get it.



I hope someone here can help with this.

Thanks,
- Jeff

[Bob Fitz]

Why not have some code in the form's Before Update event that gets the initials form the two name fields, use DMax() +1 to get the next number, and then enter it all into the VID/PID field.

EDIT:
Would need DCount() not DMax()

[Cottonshirt]

the easiest way to determine the next consecutive number is to have a table with one field where you store the value. after each new person is added you will need to UPDATE the table with the new value. in your scenario you will obviously need one number for patients and one for volunteers.


many thanks,


Cottonshirt

[JJeffH]

I tried testing it like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim x
Dim Initials

Initials = "JH"

x = DMax("[VID]", "qryGetInitialsV", "[VID] = Like " & [Initials] & " & '*'")


x = Left(x, 2) & Format(CLng(Right(x, 2) + 1), "00")

End Sub

But I get a syntax error: Missing operator in query expression '[VID] = Like JH & '*'

[Gicu]

Jeff,
Can you try
DMax("[VID]", "qryGetInitialsV", "[VID] = Like '" & Initials & "*'")

[JJeffH]

My previous reply was to Bob before I saw Cottonshirt's.

Cottonshirt, I'm not sure that addresses the problem. The consecutive numbering is based on the sets of initials. For example, there are 5 volunteers with the initials "JH" and their VIDs are JH01, JH02, etc. The next volunteer with those initials will be JH06.

The value of the system is that staff searches for records using just the initials in a combobox that drops down to display the VID, First Name, and Last Name sorted by the initials.

Have I misunderstood what you meant?

- Jeff

[JJeffH]

Hi again, Vlad. Unfortunately, no. As written the error cited: '[VID] = Like 'JH*' '

So I tried,

x = DMax("[VID]", "qryGetInitialsV", "[VID] = Like '" & Initials & " & *'")

and just got: '[VID] = Like 'JH & *' '

[JJeffH]

I'm just wondering if the reason DMax isn't working is because Initials is a string. I know DMax works alphabetically and numerically, but will it work on alphanumberic strings, like the sort order does?

[Bob Fitz]

I tried testing it like this:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim x
Dim Initials

Initials = "JH"

x = DMax("[VID]", "qryGetInitialsV", "[VID] = Like " & [Initials] & " & '*'")


x = Left(x, 2) & Format(CLng(Right(x, 2) + 1), "00")

End Sub

But I get a syntax error: Missing operator in query expression '[VID] = Like JH & '*'

Try:

Private Sub Form_BeforeUpdate(Cancel As Integer)    
If Len(Me.[FN]) > 0 And Len(Me.[LN]) > 0 Then
        Me.PID = Left(Me.[FN], 1) & Left(Me.[LN], 1) & DCount("ID", "[YourTableName​]") + 1
    Else
        Cancel = True
        Me.Undo
        MsgBox "Changes NOT saved"
    End If
End Sub

Replace FN and LN with the control names that you use, and replace YourTableName

[JJeffH]

Yes, Bob, this is getting results. But DCount is returning the number of volunteers in the table, not the number of volunteers with the specified initials. So I tried this on JH05 and it should have returned JH06, but it returns JH243 (since there are 242 volunteers).

I'm going to try DCount in the query scenario...

[Cottonshirt]

JJeffH said: Have I misunderstood what you meant?

no.

I misunderstood what you were trying to do.


apologies.


Cottonshirt

[JJeffH]

Bob, I actually tried to apply DCount to tblVolunteers, but ran into my usual problem with Access syntax. I just don't know the rules for referring to objects.

What I need to get is the highest number that occurs as character 3 and 4 in the VID, then add 1, and combine it with the initials as a 2-digit number.

I actually would prefer DMax because there are instances where a number may be missing in the ID for a given set of initials. E.g. if the record for JH01 was deleted, and we have JH02 and JH03, then the count would return 2 and the subsequent ID would be JH03, which is a conflict.

Here's what I tried (in order to get the number first, then add and format it):

y = DCount("VID", [tblvolunteers], Left([tblvolunteers.VID], 2) = Initials)

I also tried: ["tblVolunteers"]!["VID"], ["tblVolunteers"].["VID"], "tblVolunteers.VID", and, frankly, several others.

So as a sub-request in this thread, can you point me to a good summary guide to figuring out the various identification permutation?

[JJeffH]

BTW, I also tried this sequence:

y = Chr(34) & Initials & "*" & Chr(34)
x = DMax("[VID]", "qryGetInitialsV", "[VID] = Like " & y)
x = Left(x, 2) & Format(CLng(Right(x, 2) + 1), "00")

The error code cites the following as the problem: [VID] = Like “JH*”

But that is the exact criteria that works when I manually set the criteria in the query design mode.

[Gicu]

Jeff,
I would create two small totals that give you the next numbers by initials then use dlookup("Next_Num","qryNextVID","[V_Initials]='" & Initials &"'")
See attached for example.

Cheers,

[JJeffH]

BRILLIANT, Vlad!!

I combined this with Bob's, version of checking for First and Last names, added If IsNull(Me.VID), and it works beautifully!

Thanks to both of you for all your efforts on this.

By any chance, can you refer me to a good (efficient) resource for learning the various referencing conventions that Access uses? I do use the Access Object Model at Microsoft Docs, but it's really better for learning details about specific things I can look up.

- Jeff