Solved Run function if all three controls have data

[oxicottin]

I have a main form with a sub form.... The main form has 3 controls (1 text box, 2 combo boxes) and when I enter data I need to run a Public function that if the controls are null then do nothing but if they have data then run SQL and update the subform.

I used On Change, Before Update, After Update, lost focus and has focus for the three controls to update the subform and it will not do it. If I close the form and reopen it and resect anything within the 3 controls, then it will run the function and update the subform.

What in the code below can I change in order to accomplish this?

Private Sub cboEmployee_Change()
    CreateLabelList
End Sub

Private Sub txtCountDate_Change()
    CreateLabelList
End Sub

Private Sub cboShift_Change()
    CreateLabelList
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
'Do nothing
    Else
 
        strSQL = "Insert into tbl_InventoryDetails(ProductDataID,InventoryOverviewID) Select ProductDataID, " & InventoryOverviewID & " AS OID from tbl_ProductData WHERE IsInactive = False"
        
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
        
        Me.sfrm_InventoryDetails.Form.Requery
    End If
End Function

 

[The_Doc_Man]

Add three GotFocus routines, like this:

Dim CtDtFlag As Boolean, CShft As Boolean, CEmpl as Boolean
...
Private Sub txtCountDate_LostFocus()
    (whatever else you need to do, if anything)
    CtDtFlag = NZ( txtCountDate, "" ) <> ""
End Sub

Private Sub cboShift_LostFocus()
    ....
    CShft = NZ( cboShift, "" ) <> ""
End Sub

Private Sub cboEmployee_LostFocus()
    ....
    CEmpl = NZ( cboEmployee )
End Sub
....

Public Function CreateLabelList()
...
    IF ( CtDtFlag AND CShft AND CEmpl ) THEN ... {execute your query}

End Function

Note, however, that you do not return a value for CreateLabelList, so it doesn't need to be declared as a function, it could equally be a Sub.

ALSO, instead of the clear and set warnings, just use

    CurrentDB.Execute strSQL, dbFailOnError

The .Execute suppresses warnings UNLESS something goes wrong. But you get no warnings for something that might do an update or delete or insert operation. It is totally quiet if everything works.

This assumes you are in a form-class module, which is a good assumption because you are using Me. as a prefix after the SQL.

 

[Edgar_]

Also maybe use the AfterUpdate event on all three controls

Private Sub cboEmployee_AfterUpdate()
    CreateLabelList
End Sub

Private Sub cboShift_AfterUpdate()
    CreateLabelList
End Sub

Private Sub txtCountDate_AfterUpdate()
    CreateLabelList
End Sub

Private Sub CreateLabelList()
    If IsNull(Me.cboEmployee) _
    And IsNull(Me.cboShift) _
    And IsNull(Me.txtCountDate) Then
        Debug.Print "nothing"
    Else
        ' your routine
        Debug.Print "update sf"
    End If
End Sub

If you want this check to be performed with any change on these controls without losing focus, then another approach should be followed.

EDIT: This could be an option for the Change event, it's not pretty but I hope you get the idea:

Private fullCheck As String

Private Sub cboEmployee_Change()
    fullCheck = Me.txtCountDate _
    & Me.cboEmployee.Text _
    & Me.cboShift
    CreateLabelList
End Sub

Private Sub cboShift_Change()
    fullCheck = Me.txtCountDate _
    & Me.cboEmployee _
    & Me.cboShift.Text
    CreateLabelList
End Sub

Private Sub txtCountDate_Change()
    fullCheck = Me.txtCountDate.Text _
    & Me.cboEmployee _
    & Me.cboShift
    CreateLabelList
End Sub

Private Sub CreateLabelList()
    If Len(fullCheck) = 0 Then
        Debug.Print "nothing"
    Else
        Debug.Print "update sf"
    End If
End Sub

 

[Josef P.]

I like to use Exit for that.

Private Sub CreateLabelList()

' 1 if for each control ... because VBA always evaluates all conditions and this is not necessary if the 1st control is already empty.
' This also gives the possibility to set the focus to the next control to be filled
' If there are multiple controls, I would loop through them
    If IsNull(Me.cboEmployee) then
       exit sub
    elseif IsNull(Me.cboShift)  then
       exit sub
    elseif IsNull(Me.txtCountDate) Then
        exit sub
    End If

' all required controls filled ...

End Sub

Example with Loop:

Private Sub CreateLabelList()
    
    Dim ControlsToCheck() As Variant
    Dim ctl As Variant

    ControlsToCheck = Array(Me.cboEmployee, Me.cboShift, Me.txtCountDate)
 
    For Each ctl In ControlsToCheck
    ' type of ctl = Control
        If IsNull(ctl.Value) Then
            Exit Sub
        End If
    Next

' all required controls filled ...

End Sub

@Edgar_: #3 = Replace(#3, "AND", "OR")

 

[Edgar_]

@Edgar_: #3 = Replace(#3, "AND", "OR")

I'm not sure about that. OP said:

I need to run a Public function that if the controls are null then do nothing but if they have data then run SQL and update the subform

I understand this as "if any of the three have data, then perform operation, if all three are empty, do nothing", so it's an AND operation in that part. Also I edited the reply to add an option should the Change event be used.

EDIT:
But if it's as the title says and all three must have data then, OR, as Josef pointed out, for the approach with AfterUpdate. And I'd need clarification for the approach with the Change event, does the user type on the combobox controls? does the user only choose? there's likely a different set of events for that kind of behavior, so I'd need to know.

 

[Minty]

The thread title is "Run function if all three controls have data" ?

 

[Josef P.]

@Edgar_: I assume that your hint to AfterUpdate should be sufficient to solve the problem of #1.
At Change event the value is not yet available via Value property.

 

[Edgar_]

@Edgar_: I assume that your hint to AfterUpdate should be sufficient to solve the problem of #1.
At Change event the value is not yet available via Value property.

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:

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
            Debug.Print "nothing"
            Exit Sub
        End If
    Next item
    Debug.Print "update sf"
End Sub

Just to follow the patterns of our solutions so far. And just to complete the post, with the AfterUpdate, if all three must have data to update the subform, then just OR instead of AND, like:

Private Sub cboEmployee_AfterUpdate()
    CreateLabelList
End Sub

Private Sub cboShift_AfterUpdate()
    CreateLabelList
End Sub

Private Sub txtCountDate_AfterUpdate()
    CreateLabelList
End Sub

Private Sub CreateLabelList()
    If IsNull(Me.cboEmployee) _
    Or IsNull(Me.cboShift) _
    Or IsNull(Me.txtCountDate) Then
        Debug.Print "nothing"
    Else
        ' your routine
        Debug.Print "update sf"
    End If
End Sub

EDITED, had wrong code.

 

[The_Doc_Man]

If IsNull(Me.cboEmployee) Or IsNull(Me.cboShift) Or IsNull(Me.txtCountDate) Then

This sequence would not do the right thing if any of those had a zero-length string, which originally worried you in your first post. Your code DID test for empty strings, too.

 

[Edgar_]

This sequence would not do the right thing if any of those had a zero-length string, which originally worried you in your first post. Your code DID test for empty strings, too.

Please elaborate. When losing focus with the after update event, the empty control becomes null on my tests, it's only an empty string when it has focus.

 

[Uncle Gizmo]

Try This:-

Option Compare Database
Option Explicit

'Put this in a Module
'Public Declare PtrSafe Sub Sleep Lib "kernel32" (ByVal dwMilliseconds As LongPtr)

Private Sub cboEmployee_AfterUpdate()
    CreateLabelList
End Sub

Private Sub txtCountDate_AfterUpdate()
    CreateLabelList
End Sub

Private Sub cboShift_AfterUpdate()
    CreateLabelList
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
        'strSQL = "Insert into tbl_InventoryDetails(ProductDataID,InventoryOverviewID) Select ProductDataID, " & InventoryOverviewID & " AS OID from tbl_ProductData WHERE IsInactive = False"
        MsgBox " >>> " & "strSQL = Insert into tbl_Invent"
        DoCmd.SetWarnings False
        'DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True

        Sleep 100 ' Sleep for 100 milliseconds
        'Me.sfrm_InventoryDetails.Form.Requery
    End If
End Function

YouTube of it Working

 

[The_Doc_Man]

Uncle G beat me to it. Thanks, U.G.

 

[Uncle Gizmo]

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

See the video for more information....

Call Function From Event Pointer - Nifty Access​

 

[Pat Hartman]

You would never use the Change event for something like this because the Change event runs once for each character typed into a control.
Since the values in three fields affect the insert, the best place to run the code is the Form's AfterUpdate event. You would not run the code in the control events. Code in a control event should worry about THAT control ONLY. It shouldn't care what values are in other controls because unless you have total control over mouse movement, you cannot control which form control gets filled in which order. When you need to consider the values in multiple controls, use the Form's BeforeUpdate event if you are validating. This doesn't seem to be validation so I chose the Form's AfterUpdate rather than the Form's BeforeUpdate event since it doesn't seem like it matters whether a record gets saved with some fields empty. All you care about is the contents of the three fields together so we can wait until AFTER the record is saved.

Private Sub Form_AfterUpdate()
    Dim strSQL As String 
    If Me.txtCountDate & "" = "" _
                   Or Me.cboShift & "" = "" _
                   Or Me.cboEmployee & "" = "" Then
'Do nothing
    Else
        strSQL = "Insert into tbl_InventoryDetails(ProductDataID,InventoryOverviewID) Select ProductDataID, " & InventoryOverviewID & " AS OID from tbl_ProductData WHERE IsInactive = False"
      
        DoCmd.SetWarnings False
        DoCmd.RunSQL strSQL
        DoCmd.SetWarnings True
      
        Me.sfrm_InventoryDetails.Form.Requery
    End If
End Sub

Understanding the purpose of each event and when it runs is necessary in order for you to get control over your forms. You might be interested in these two videos and the sample database that is used in the videos. The videos concentrate on the Form's BeforeUpdate event but also show the proper usage of other events and show how improper validation can lead to bad data being saved.

Bad Data is Bad for Business #1 and #2

I just added #2 --- that link is further down. It is much shorter than #1. I also added a copy of the database I used for the video. Enjoy:) I created a database to help people to understand how and why to use the Form's BeforeUpdate event to validate data. @Uncle Gizmo and I made a...

www.access-programmers.co.uk

Also, I would use the DAO .Execute command rather than the .RunSQL to run the query but that's neither here nor there regarding the problem at hand.

 

[MajP]

@oxicottin
If you have multiple controls calling the same procedure from the same event, then this can be done without multiple event procedures

Private Sub cboEmployee_SomeEvent()
    CreateLabelList
End Sub

Private Sub txtCountDate_SomeEvent()
    CreateLabelList
End Sub

Private Sub cboShift_SomeEvent()
    CreateLabelList
End Sub

That can be replaced by selecting all the controls and in the onChange property
onChange: =CreateLabelList()
instead of the word [Event Procedure]
However CreateLabelList must be declared as a function and not Sub routine. I have no idea why that is, but it is the rule.

also

If IsNull(txtCountDate) Or (txtCountDate) = ""

can be shortened to

If (txtCountDate & "") = ""

Which covers both null or "" since NULL & "" = ""
However a better check in this case is

if isDate(txtCountDate)

because not only do you need a value but the value needs to be a date.

 

[Uncle Gizmo]

must be declared as a function and not Sub routine. I have no idea why that is, but it is the rule.

It seems odd that you can't use subroutines in Access's 'Event Handler' textbox, as subroutines are usually used for tasks that don't need to return a value. I would speculate that this rule could be there to prevent recursion, since the events linked to controls are listed as subroutines in the module and it is possible someone might unintentionally try to call them directly from the 'Event Handler' textbox."

 

[Pat Hartman]

@MajP I don't think you want to use the on Change event for this. It will end up adding multiple rows to the table - one for each character typed into the third field.

Also, not sure why you would want to run the code in control level events where it must run three times rather than running ONCE if you use the Form's BeforeUpdate or AfterUpdate event.

I suggested the Form's AfterUPdate event so the new record would not be created unless the current record was successfully saved. Inserting the record in the control events, could end up in adding a row to the table even if the record you are working on doesn't get saved.

 

[Pat Hartman]

It seems odd that you can't use subroutines in Access's 'Event Handler' textbox

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

 

[Uncle Gizmo]

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

But that would suggest that the 'Event Handler' textbox can utilize the value returned by the function placed within it. I'm not aware of any situations where this actually occurs. Could you provide an example?

 

[MajP]

@MajP I don't think you want to use the on Change event for this. It will end up adding multiple rows to the table - one for each character typed into the third field.

Also, not sure why you would want to run the code in control level events where it must run three times rather than running ONCE if you use the Form's BeforeUpdate or AfterUpdate event.

I suggested the Form's AfterUPdate event so the new record would not be created unless the current record was successfully saved. Inserting the record in the control events, could end up in adding a row to the table even if the record you are working on doesn't get saved

Actually if you read my post, I am not making suggestions on how the OP should solve the problem. I am simply providing a generic example of how a single function can serve as an event handler for multiple control events. Thus "SomeEvent".

I would speculate that this rule could be there to prevent recursion, since the events linked to controls are listed as subroutines in the module and it is possible someone might unintentionally try to call them directly from the 'Event Handler' textbox."

That is actually a pretty good conjecture. Seems logical.

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

I assume you mean "functions" can return values since as written makes no sense. How that is relevant (or if it is), is what is not understood. I think it is not and more likely what @Uncle Gizmo suggests is more likely why MS went with that construct.