I'm not sure where to post this, maybe VBA or Forms would be better, but I think it's fundamentally an index problem, so I'm
posting here. I've searched both the net and here, but most of the questions are about preventing duplicate records.
I want to prevent duplicate records but save changes to existing records.
I have a form based on a table that has a 3-field-unique-index (but not the primary index, which is autonumber). I've
written a function to check for existing records with the given combination of the three fields, as long as all have data (it
bails if any are null). I'm calling the function from BeforeUpdate for each of the textbox controls. I was also calling it
from the form's BeforeUpdate, but commented it out as it seemed redundant. I have other code there verifying that the user
wants to keep the changes.
I'm trying to save the users from getting all the way to the end before discovering there's a problem. To test it, I added
then deleted one character from one of the three relevant fields in an existing record, and bing, up pops my alert that I'm
violating the index. Then I can't leave the field, I'm stuck there because I can't save the record.
I want it set up so that other fields in the record can be changed/updated AND so that a new record for the same person can't
be entered (a dupe).
Oh, and I'm running into all kinds of problems with acCmdSaveRecord not being available. I feel like an idiot that I haven't
been able to make sense of the posts about that. I can just comment it out and let Access save the record when it closes the
form. But my bosses really want the users prompted about saving changes.
Any thoughts or suggestions or insights greatly appreciated. I really tried to search this out, so any tips for better
searching are also appreciated.
Here's the code for the function:
Public Function CheckForClientDupes()
Dim response As Variant
Dim strFamID, strLName, strFName As String
Dim strFilter As String
If IsNull(Me!FamilyIDNo) Or IsNull(Me!strLastName) Or IsNull(Me!strFirstName) Then
strFilter = "[strFamilyIDNo] = """ & Me!FamilyIDNo & """ And " & "[strLastName] = """ & Me!strLastName & """ And " &
"[strFirstName] = """ & Me!strFirstName & """"
If DCount("*", "tblClients", strFilter) > 0 Then
response = MsgBox("There is already a client with this combination of FamilyID, Last and First names in this database.
Would you like to Continue Anyway (Yes) or cancel data entry and Erase Your Changes (No)?", vbYesNo, "Duplicate Client
If response = vbYes Then
' DoCmd.RunCommand acCmdSaveRecord
response = MsgBox("You have chosen to cancel your changes. Your changes will be erased.", vbOKCancel +
vbQuestion, "Data Entry Cancelled")
If response = vbOK Then