Solved Run function if all three controls have data (1 Viewer)

[oxicottin]

Thank you all for your suggestions/examples, I posted and got pounded with a lot of extra work so I'm going to put on hold for a few days, Ill post back what worked!

Thanks All,

 

[The_Doc_Man]

It is because Events return a value and subroutines don't.

Actually, if I may offer a GUESS:

When you declare event handlers using the "=mumblefratz" syntax, that "=" sign condones/implies/requires a value to be returned, whereas if you use [event procedure] for that option, you don't require a value to be returned so can get away with a SUB declaration.

It should be noted that you CAN activate a function in a way that looks like a sub and just ignore any returned value. But you can't activate a sub in a way consistent with a function because the compiler cannot determine the data type of the return value... because there isn't one.

 

[MajP]

I found the reason. Functions written in the property sheet are executed as expressions and not as code.

Notes About the Function Call​

• When called from the Property Sheet, functions are evaluated as expressions rather than pure code, which can lead to some important differences
• You MUST include the leading equal sign, otherwise Access treats the value as the name of a macro
• You MUST include values for all optional parameters, even if you want to use their defaults (otherwise you get a "wrong number of arguments" error)
• You SHOULD include the trailing parentheses, even if the function takes no parameters, otherwise you could see unexpected behavior (e.g., removing the parentheses in the example above leads to Access auto-correcting the value to =[MyButtonKeyPressed]; the more concerning issue is that the expression gets evaluated three times per event without the parentheses [BAD] versus only once per event when the parentheses were included [GOOD])
• The called function CAN be a Private Function, if it is located within the code behind of the form or report where it is referenced
• The called function MUST be a Public Function, if it is located within a Standard Module
• The called function CANNOT be in a Class Module
• The called function CANNOT receive event arguments (for example, there is NO way to access the value of the key the user pressed if you call a Function from the Property Sheet's KeyPress event; instead, use [Event Procedure] and the standard Sub event handler if you need access to the values of the event arguments, such as Private Sub btnMyButton_KeyPress(KeyAscii As Integer))

Expressions are basically code statements that are evaluated outside of the VBA IDE, such as within queries, in form/report/control properties, or via the Access-specific Eval() function. These expressions are evaluated via the Microsoft Access database engine Expression Service (ACEES.DLL).

 

[oxicottin]

Try This:-

@Uncle Gizmo I tried and it didn't work like your video showed. I noticed in your video you typed text into the combo boxes and mine I select an option from the combo box. Maybe that's why mine don't work and your example did?

 

[oxicottin]

Add three GotFocus routines, like this:

@The_Doc_Man I tried your example and it didnt do anything?

Private Sub cboEmployee_LostFocus()
    CtDtFlag = nz(cboEmployee, "") <> ""
End Sub

Private Sub txtCountDate_LostFocus()
    CtDtFlag = nz(txtCountDate, "") <> ""
End Sub

Private Sub cboShift_LostFocus()
    CtDtFlag = nz(cboShift, "") <> ""
End Sub

Public Function CreateLabelList()
    Dim strSQL As String

    If IsNull(txtCountDate) Or (txtCountDate) = "" _
                   Or IsNull(cboShift) Or (cboShift) = "" _
                                Or IsNull(cboEmployee) Or (cboEmployee) = "" Then
        MsgBox " >>> " & "Do nothing"
    Else
    
    If (CtDtFlag And CShft And CEmpl) Then
        MsgBox " >>> " & "Do Something"

     End If
    End If
End Function

 

[oxicottin]

Yes, in the case of the Change event, we need to access the .Text property of the control for a real-timey feel, as shown in the edit of post 3. That post demonstrates an option to trigger the change event inspecting if any of the three controls had data. If all of them must have the data, then something like this could be applied for the Change event:

@Edgar_ Your example works some what. I tried your example, and it checks every time and gives a message BUT when all the controls have data it runs the code BUT it runs it 3 times? How do I get it to only run one time?

Dim arr(1 To 3) As Variant

Private Sub cboEmployee_Change()
    arr(1) = Me.cboEmployee.Text
    CreateLabelList
End Sub

Private Sub cboShift_Change()
    arr(2) = Me.cboShift.Text
    CreateLabelList
End Sub

Private Sub txtCountDate_Change()
    arr(3) = Me.txtCountDate.Text
    CreateLabelList
End Sub

Private Sub CreateLabelList()
    Dim item As Variant
    For Each item In arr
        If Len(item) = 0 Then
            MsgBox " >>> " & "Do nothing"
            Exit Sub
        Else
            MsgBox " >>> " & "Do Something"
        End If
    Next item
End Sub

 

[oxicottin]

You could simplify the code a tad by placing calls to the function within the after update 'Event Handler' textbox.

@Uncle Gizmo Your suggestion works Thanks!

@MajP I used your shortening examples, Thanks!

 

[Edgar_]

@Edgar_ Your example works some what. I tried your example, and it checks every time and gives a message BUT when all the controls have data it runs the code BUT it runs it 3 times? How do I get it to only run one time?

Made the mistake of leaving the insert in the Else instruction. The insert should come after the validation of the array. It was doing this:
"For each item in the array, if its length is 0, exit. Oh, but if it's not 0, insert that sh*t"

This should do it:

Private Sub CreateLabelList()
    Dim item As Variant
    For Each item In arr
        If Len(item) = 0 Then
            Debug.Print "nothing"
            Exit Sub
        End If
    Next item
    'Insert
    'Requery subform
End Sub

Sorry about that, should have tested.