OK, this is a follow up thread to a previous thread that I thought I had solved. What I am trying to do here is check the ID that the user enters to verify the following:
1. Does the ID already exist and if so is it assigned to an INACTIVE Record, or
2. Does the ID already exist and if so is it assigned to an ACTIVE Record, or
3. Does the ID not exist.
fields involved:
ID: not primary key; duplicates OK
Inactive: yes/no control
If (1) is true than strMsg is executed; if (2) is true than strMsg1 is executed; if (3) is true than no message is executed and the user continues entering information pertaining to the new ID.
Here is the problem....
If I type in an ID that does exist and it is INACTIVE, I only get strMsg for the first INACTIVE ID in qryData. Every other INACTIVE ID gets strMsg1. As an example, IDs 3, 9, 12, and 21 are all INACTIVE. If I click the button on my form to add a new record and I enter 3 as the record ID I get strMsg but if I enter 9, 12, or 21 I get strMsg1.
Code:
Private Sub ID_BeforeUpdate(Cancel As Integer)
On Error GoTo ErrorHandler
strMsg = " ID " & Me.ID & " is already in use by an Inactive Record!!! " & _
vbCRLf & vbCrLf & " Do you still want to use it?" & _
vbCrLf & " Click 'Yes' to proceed. Otherwise click 'No' to cancel and enter another ID. "
strMsg1 = " ID " & Me.ID & " is already in use by an Active Lesson!!! " & _
vbCrLf & " You cannot use this ID. Click 'OK' and try again. "
If Not IsNull(Me.ID.Text) Then
If DLookup("ID", "qryData") = Me.ID And DLookup("Inactive", "qryData") = -1 Then
If MsgBox(strMsg, vbQuestion + vbYesNo, "Duplicate ID!!!") = vbNo Then
Cancel = True
Call SendKeys("{Esc}")
Call SendKeys("{Esc}")
Call SendKeys("{Esc}")
Else
Cancel = False
End If
Else
If DCount("ID", "qryData", "[ID] = '" & Me.ID & "'") > 0 Then
MsgBox strMsg1, vbCritical, "Duplicate ID Not Allowed!!!"
Cancel = True
Call SendKeys("{Esc}")
Call SendKeys("{Esc}")
Call SendKeys("{Esc}")
Else
Cancel = False
End If
End If
End If
Exit_ErrorHandler:
Exit Sub
ErrorHandler:
Select Case Err.Number
Case Else
Call ErrorLog(Err.Description, Err.Number, Me.Name)
End Select
Resume Exit_ErrorHandler
Exit Sub
Thanks in advance for any/all help