How to generate a text string for use as primary key?

[JenniferM]

Using Access 2007

My table (TblPtDemographics) contains a primary key of PtID, which is a numeric field.

Linked to this table is TblKneeSurgeryInfo, which contains a primary of SxKneeID, which is a text field. It also has a field SxKneeSide, which designates "Right" or "Left."

The format of SxKneeID should be as follows:

R111111S1

1. Where "R" indicates that the right knee was operated on (as found in SxKneeSide)
2. "111111" is the value in PtID
3. "S" is a constant indicating surgery
4. "1" indicates the first surgery the person has had on the right knee.

My main question is: How can I set up the code that would automatically generate a string to put in the SxKneeID field? I need to trim the "Right" and "Left" values down to the first letter and have access sort of autonumber the last portion of the string (for instance, if "R111111S1" already exists, use "R111111S2" when creating a new record).
I know how to get the PtID field and "S" into the string, but as for the other part--- I'm not sure how to set this up using VBA. Any help would be appreciated!

[NeoPa]

Show us what you've got Jennifer. If we see your code we know you've already looked at it for yourself first, and we can see how much you do and don't understand - how much you need.

PS. Is the numeric value (111111 in your example) a fixed number of digits, or does it depend on the number? I strongly suggest you decide to use a fixed number of digits going forward even if you hadn't considered it before now.

[JenniferM]

PtID is a numeric value that will vary between 5-7 digits in length (I wish they were all one length, but these are the chart numbers that I have no control over--- dang it!).

Anyways, my initial approach to this problem was to perform a count of all records where PtID and SxAffectedSide (Right/Left) matched--- then add 1.

Here's my code (I realize there's probably a cleaner way of doing this, but you get the general idea):

Dim db As DAO.Database
Set db = CurrentDb
 
Dim strIDPrefix As String
Dim strSide As String
Dim strSxNumber As String
Dim strSxKneeID As String
 
Dim strSQL As String
 
If [SxAffectedKnee] = "Right" Then
    strSide = "R"
    Else
    strSide = "L"
    End If
 
strIDPrefix = strSide & [Forms]![FrmPatientDemographics]![PtID] & "S"
 
strNumber = DCount("*", "TblSurgeryForm", "[TblSurgeryForm].[PtID] = " & _
[Forms]![FrmPatientDemographics]![PtID] & _
" AND [TblSurgeryForm].[SxAffectedKnee] = " _
& Chr(34) & Me.SxAffectedKnee & Chr(34) & _
" AND [TblSurgeryForm].[SxID] <> " & Me.SxID) + 1
 
If strNumber = "1" Then
    strSxKneeID = Left(strIDPrefix + strNumber, Len(strIDPrefix + strNumber) - 1)
    Else
    strSxKneeID = strIDPrefix + strNumber
    End If
 
strSQL = "UPDATE [TblSurgeryForm] " _
       & "SET [SxKneeID] = " & Chr(34) & strSxKneeID & Chr(34) _
       & " WHERE ([SxID] = " & Me.SxID & ")"         
 
Call db.Execute(strSQL)

Now, this works fine assuming no surgery records are deleted. There will be a problem with my approach if, say for instance:

A patient (PtID = 55555) has three surgeries on the right knee which will be:
R55555S
R55555S2
R55555S3

If I delete the record for either of the first two, my DCount function approach will generate the next string to be R55555S3, which already exists. I don't know if I'm explaining this adequately, so let me know where more clarification is needed..... Thanks!!

[ADezii]

Assuming existing Records are in the correct sequence:

1. Create an [ID] Field {AUTONUMBER} in tblKneeSurgeryInfo. We'll use this Field to create the proper Sort Order so that the highest [SxKneeID] Value for a given Patient will always be the last Record in the Recordset. Sorting on [SxKneeID] itself will not work since it is a Text Field.
2. Copy-N-Paste the following Code to wherever appropriate.
3. Make any Table/Field/Form Field Name changes to conform to your specific needs.
   

   Dim MyDB As DAO.Database
   Dim rstKnee As DAO.Recordset
   Dim strNewKneeID As String
   Dim strSQL
    
   strSQL = "SELECT * FROM tblKneeSurgeryInfo WHERE [tblKneeSurgeryInfo].[PtID] = " & _
             [Forms]![FrmPatientDemographics]![PtID] & _
            "AND [tblKneeSurgeryInfo].[SxKneeSide] = '" & _
             [Forms]![FrmPatientDemographics]![SxAffectedKnee] & _
            "' ORDER BY [tblKneeSurgeryInfo].[ID];"
    
   Set MyDB = CurrentDb
   Set rstKnee = MyDB.OpenRecordset(strSQL, dbOpenSnapshot)
    
   With rstKnee
     If Not .BOF And Not .EOF Then       'Yep, we have Records!
       .MoveLast       'Last Value for [SxKneeID]
         strNewKneeID = Left$(![SxKneeSide], 1) & ![PtID] & "S" & _
                        Val(Mid$(![SxKneeID], InStr(![SxKneeID], "S") + 1)) + 1
     Else
       'No Records matching Patient and Knee
       strNewKneeID = Left$(![SxKneeSide], 1) & ![PtID] & "S"
     End If
   End With
    
   rstKnee.Close
   Set rstKnee = Nothing
    
   MsgBox "Next [SxKneeID] = " & strNewKneeID

[NeoPa]

JenniferM:
Anyways, my initial approach to this problem was to perform a count of all records where PtID and SxAffectedSide (Right/Left) matched--- then add 1.

That's almost right Jennifer.

Instead of finding a count of the matching PKs though, find the Max of them and add one to that. This will ensure none is ever reused and any new ones added are greater than all existing ones.

Let me know if this is enough for you or if you need the DMax() code illustrated for you. The code for this should fit on a single line and can be done from within SQL too if necessary.

[ADezii]

Hello NeoPa. As usual, I'm probably missing something but won't DMax() give erroneous results under certain conditions, such as?

DMax("[SxKneeID]","tblKneeSurgeryInfo", "[PtID]=55555 AND [SxKneeSide] = 'Right'")

will return

R55555S9

when there exists

R55555S10

Thanks in advance...

[JenniferM]

Thanks for the replies. After ADezii's first post, I created a numeric field within the table called SxSurgeryNumber. I ended up using SQL to get the max from this field using this code:

Dim rst As DAO.Recordset
 
Set rst = CurrentDb.OpenRecordset("SELECT _
       MAX(SxSurgeryNumber) FROM [TblSurgeryForm] _
       WHERE [PtID] = " & _
       [Forms]![FrmPatientDemographics]![PtID] & _
       " AND [SxAffectedKnee] = " & _
       Chr(34) & Me.SxAffectedKnee & Chr(34))
            If Not (rst.EOF And rst.BOF) Then
            lngSxSurgeryNumber = Nz(rst.Fields(0), 0) + 1
            End If
        rst.Close
        Set rst = Nothing

I then updated the SxSurgeryNumber field with the value I got here and created my string in a similar manner to my first post.... And it will work now, because all new numbers are based on the max of the field! Thanks for all the assistance, guys!!

[ADezii]

Nice job, JenniferM. How are you handling New Patients, manual entry?

[JenniferM]

Yes, all new patients are stored in TblPatientDemographics, with their primary key PtID.
I gather you're asking about how I handle numbering new ones--- in this case, I'm going off their chart number, which is just a random number anywhere from 5-7 digits long. I wish there was some rhyme or reason to how we get these, but there just isn't.... Ergo, I have to manually input their PtID.

[ADezii]

It won't be long until you are advising us!

[NeoPa]

ADezii:
Hello NeoPa. As usual, I'm probably missing something but won't DMax() give erroneous results under certain conditions, such as?

It's a good point my friend (which frankly I hadn't gone into that much detail on without needing to). In most cases (this is an exception as the size of none of the subfields within the value is reliable so, although Left(), Right() & Mid() would work, InStr() doesn't (except in VBA), so this is not practicable within pure SQL), this could be handled by setting the Expr parameter to return a Val() of the resulting subfield rather than the simple field reference itself.

As I said, because the start position, and even the length, of this subfield are both non-predictable in the current circumstances, this couldn't be done in simple SQL. It can still work if done within VBA ok of course.