Help with if statement to compare multiple fields

[usmcgrunt]

This is a modification to a previous post with a new issue......



I have a form that allows the user to add and modify records in my database. When the form opens, it is populated with all of the active records in the table.

The fields in question are:

ID: not primary key
Active: Yes/No control

When the user attempts to enters a new ID I want to first check and see if the ID is already being used by an active record in the table. If it is, then I want to prompt the user with a message that says that the ID is already being used by an active record so it cannot be used again. However, if the ID is assigned to an inactive record, I want to prompt the user with a yes/no message that says the ID is already being used by an inactive record, are you sure you want to use it again.

Below is the code that I currently have on the BefureUpdate of the text box. It does not allow me to determine whether or not the ID is associated with an active or inactive record, just that it is a duplicate. What code do I need to add to the 'if' statement to make this determination? I have a yes/no field in the table that I use to classify records as active or inactive.

 
Private Sub ID_BefireUpdate(Cancel As Integer)
 
    On Error GoTo ErrorHandler
 
        strMsg = "ID " & Me.ID & " is already in use!!!  " & _
            vbCrLf & vbCrLf & " Do you still want to use it?   " & _
            vbCrLf & vbCrLf & " If the record currently assigned to the ID " & Me.ID & " is Inactive    " & _
            vbCrLf & " click 'Yes' to proceed.  Otherwise click 'No' to cancel and enter another ID.    "
 
        If Not IsNUll(Me.ID) Then
 
            If DLookup("ID", tblData", "[ID] = '" & Me.ID & "'") > 0 Then
                If MsgBox(strMsg, vbQuestion + vbYesNo, "Duplicate ID!!!") = vbNo Then
                    Cancel = True
                    Call SendKeys("{Esc}")
                Else
                    Cancel = False
                End If
            End If
        End If
 
Exit_ErrorHandler:
    Exit Sub
 
ErrorHandler:
    Call ErrorLog(Err.Description, Err.Number, Me.Name)
    Resume Exit_ErrorHandler
 
End Sub

Thanks in advance for the help.

Sean

[TheShabz]

I would create a query that selects the ID and status where ID = the textbox entered. I would then use Dlookup on that query instead of the table itself. so:

If Dlookup(ID, qryName) = me.txtbox AND Dlookup(status, qryName) = 0 Then
'rest of your code.

[usmcgrunt]

TheShabz, that did the trick. Thanks for the help.

Sean