Reattack help with IF statement to compare mutiple fields

[usmcgrunt]

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.

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

[ajetrumpet]

the first problem here is DLOOKUP(). you don't need it at all. use dcount() for all of your validation. drop the lookup completely. for two reasons:

DL returns an error if no value is found, and it is SLOW.

DC always returns a value, either 0 or greater than 0. No errors to deal with.

also, Sendkeys should be dropped. Why the 3 escapes? Not following...

[usmcgrunt]

the first problem here is DLOOKUP(). you don't need it at all. use dcount() for all of your validation. drop the lookup completely. for two reasons:

DL returns an error if no value is found, and it is SLOW.

DC always returns a value, either 0 or greater than 0. No errors to deal with.

Got it. I will give DC a try and see what happens.

also, Sendkeys should be dropped. Why the 3 escapes? Not following...

I am using the 3 x sendkeys to return the ID field to its previous state; i.e. an empty textbox ready to receive another ID. It was the only code I could come up with that would clear the textbox out and make it ready for a new ID try.

[ajetrumpet]

I am using the 3 x sendkeys to return the ID field to its previous state; i.e. an empty textbox ready to receive another ID. It was the only code I could come up with that would clear the textbox out and make it ready for a new ID try.

you don't need it. Sendkeys is risky, for reasons not worthy to mention. you can do it other ways. some are:

control.oldvalue (be careful with this, it does not respond to NULLs or zero-lengths)

or:

control.value = NULL
control.setfocus

etc, etc...

by the way, I love the username! ha ha...so appropriate for the military. what branch are you in? I've heard, for instance, that the Navy uses Access excusively in some of their technology projects.

My father used to be in the National Guard, 30 years ago. He said that every 'learning session' he went to, everyone had laptops open and they were all looking at Access. That was not that long ago of course. That happened sometime later, but I think it was in the 90's. That's unbelievable. Is it still used that much?

[usmcgrunt]

Using DCount did the trick. Thanks. However, the substitution for the SendKeys is not owrking. See below:

you don't need it. Sendkeys is risky, for reasons not worthy to mention. you can do it other ways. some are:

control.oldvalue (be careful with this, it does not respond to NULLs or zero-lengths)

With this way I get a compile error: Invalid use of property in my vba.

or:

control.value = NULL
control.setfocus

With this way I get error 2115: The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing myDatabase from saving the data in the field. The field is not set to required in the underlying table and there is no validation rule set for the field.

by the way, I love the username! ha ha...so appropriate for the military. what branch are you in? I've heard, for instance, that the Navy uses Access excusively in some of their technology projects.
My father used to be in the National Guard, 30 years ago. He said that every 'learning session' he went to, everyone had laptops open and they were all looking at Access. That was not that long ago of course. That happened sometime later, but I think it was in the 90's. That's unbelievable. Is it still used that much?

I am in the Marine Corps. Honestly, MS Access is pretty much non-existent because we lack the permanent personal, i.e. civilian brainiacs, to administer and maintain the databases. I am fortunate that I have a pretty good knowledge of it and I am stationed at an educational institution within the Marine Corps that can affort to have a civilian that has basic knowledge of Access. I just need to make sure that I make the database as error proof as possible so that all they have to do is maintain it. Just a hobby for me; I am actually an Infantry Officer. I like to kill things, break things, and make things...Ha Ha Ha!!!

[ajetrumpet]

With this way I get error 2115: The macro or function set to the BeforeUpdate or ValidationRule property for this field is preventing myDatabase from saving the data in the field. The field is not set to required in the underlying table and there is no validation rule set for the field.

there is no way to give advice about the sendkeys change, because other parts of your code may be interfering with the change.

the message doesn't mean much when just looking at it on the forum. If you seriously just took my example and pasted it verbatim, then the syntax is wrong. you have to write a full reference to the control object. e.g. - NOT this:

control.value

but rather THIS:

me.control.value

if you're behind the actual form, and if you're in a remote location, like an actual VBA module, then more of a reference extension is needed:

forms("formname").controls("control").value

[usmcgrunt]

Adam, thanks for the response. I should have been more clear in my response to your suggestion. I did translate your general suggestion into the correct format . Below is the entirety of my code for the BeforeUpdate event for the txtbox 'ID'

As mentioned in a previous post, Your DCount suggestion works perfectly. However if the user is forced to enter a new ID I cannot automate the process to return to the ID txtBox and clear out the old ID without getting the error message described in the previous post.

I have checked the format for the field in the underlying table and there is no validation rule. I have even set its Required property to 'No' and Allow Zero Length to 'Yes'. Additionally, there is no validation rule for this txtBox in the form field property.

There is no other code other than code that I have on a save button that verifies the existence of an ID and prompts if no ID exists.

I will continue to troubleshoot this issue but I welcome your continued suggestions.

 
Private Sub ID_BeforeUpdate(Cancel As Integer)
 
    On Error GoTo ErrorHandler
 
    Dim IDValue As Integer
    Dim IAValue As Integer
 
        IDValue = DCount("[ID]", "[qryData]", "[ID]" = '" & [ID] & "'")
 
        IAValue = DCount("[ID]", "[qryInactiveData]", "[ID] = '" & [ID] & "'")
 
        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) Then
            If IDValue > 0 Then 'ID exist in qryData
                If IAValue > 0 Then 'The above ID is Inactive
                    If MsgBox(strMsg, vbQuestion + vbYesNo, "Duplicate ID!!!") = vbNo Then
                        Cancel = True
                        Me.ID.Value = Null
                        Me.ID.SetFocus
                    Else
                        Cancel = False
                    End If
                Else 'The above ID is Active
                    MsgBox strMsg1, vbCritical, "Duplicate ID Not Allowed!!!"
                    Cancel = True
                    Me.ID.Value = Null
                    Me.ID.SetFocus
                End If
            Else ' The above ID does not exist
                Cancel = False
            End If
        End If
 
Exit_ErrorHandler:
    Exit Sub
 
ErrorHandler:
    Case Select Err.Number
        Case Else
            Call ErrorLog(Err.Description, Err.Number, Me.Name)
    End Select
    Resume Exit_ErrorHandler
 
End Sub

[ajetrumpet]

More than likely, you're getting it because you're trying to manipulate the same control in which the event is taking place. as a general rule, you can't do that.

e.g. - you can't update the thing if the program is in the process of 'updating' it and checking the update. make sense?

I'm not sure of a workaround, but one might be to use another control or something to force an ID selection, check it, then if it validates with the count of 0, throw the input into the ID textbox and disable it. does that make any sense?

I've done that a few times, mostly to give the user confidence that they can go ahead and fill the rest of the information in.