ACCESS 2003: Check to see if an entry exists BEFORE UPDATING, then display the message THEN OPEN existing entry
Having been working on this for a while and I am getting runtime error 2501
I am trying to pull a record after the message box pops up stating that a duplicate was found and after clicking OK, pull the record.
I've tinkered with the DoCmd.FindRecord, DoCmd.GoToRecord and DoCmd.OpenForm methods to no avail.
Any help would be greatly appreciated.
Thanks in advance.
Private Sub MRN_BeforeUpdate(Cancel As Integer)
Dim Answer As Variant
Answer = DLookup("[MRN]", "Requests Processed", "[MRN] = '" & Me.MRN & "'")
If Not IsNull(Answer) Then
MsgBox "Existing MRN found" & ": " & MRN.Text & vbCrLf & vbCrLf & "Please SEARCH and EDIT on EXISTING Record.", vbCritical + vbOKOnly + vbDefaultButton1, "EXISTING MRN FOUND"
Cancel = True
Me.MRN.Undo
Else:
End If
DoCmd.OpenForm "Requests Processed", , , Answer
End Sub
source to share
In the Help topic, OpenForm
WhereCondition is "A string expression in which the SQL WHERE clause without the word WHERE is in effect."
Try OpenForm
this way ...
DoCmd.OpenForm "Requests Processed", , , "[MRN] = '" & Answer & "'"
I think it might work. However, it looks like you can use the same string for your DLookup
Criteria parameter and your OpenForm
WhereCondition. If true, create one string variable (like strWhere) and use it in both places.
source to share