Solved Unable to trigger event when 2 fields are equal. (1 Viewer)

[Poco_90]

Hi

I am hoping someone can help as I seem to be stuck. I have the database 90% of the way to where I want it but I am stuck on some VBA that I want to trigger when 2 controls on the form are equal (outlined in red in the attached image). When these 2 controls are equal I want to change a field in a table from ToBePacked to Shipped. This is my way to close the order being currently packed and ensure the operator doesn’t over pack the pallet.

I am designing a database where the user selects a customer’s order(green area) and the operator scans in 3 barcodes (blue area) to load that box on a pallet. I have various checks running on the barcodes when they are scanned and these are working. At the moment I have the event triggering on the “Our BOXID Serial” field (control name txtBoxID). I haven’t quiet decided where I will eventually trigger this event but have it on this field for the moment.

In the event when the 2 fields are equal nothing happens. The fields are numeric (been caught by this before). I tried to trigger the event in a couple of places but cant seem to trigger it. I have attached the DB, excuse it is a bit rough and ready and I have tried to comment out my code.
Thanks in advance.

 

[topdesk123]

Good morning!
I would trigger the code on the after-update event of the form. HTH.

 

[Poco_90]

Morning,
Thanks for getting back to me. Tied that but it didn't work. Even changed the code to the following, but noting happens.

Private Sub Form_AfterUpdate()
    MsgBox "Test message"
    Debug.Print "Packed Count: " & Me.txtCount.Value
    Debug.Print "Order Total: " & Me.txtQtyTray.Value
End Sub

 

[CJ_London]

Probably the wrong event. Not enough info to suggest which event but I would guess one of those controls afterupdate events

 

[Gasman]

They are both expression fields, so no AfterUpdate is performed. That is when a user interacts with the form and updates it in someway.
Could try the Current event of the form.

 

[Poco_90]

Thanks CJ and Gasman for the responses. I think I am good. I have the following code which seems to be working. Never normally put an event of a form so wouldn't have thought about it. I will update the post later. Thanks again.

Private Sub Form_Current()
    'MsgBox Me.txtCount.Value
    'MsgBox Me.txtQtyTray.Value
    Debug.Print "Packed Count: " & Me.txtCount.Value
    Debug.Print "Order Total: " & Me.txtQtyTray.Value
   
If IsNull(Me.txtCount.Value) Or IsNull(Me.txtQtyTray.Value) Then
    ' Ignore if either value is null
    ' MsgBox "One or both values are null. Cannot perform the comparison."
ElseIf CDbl(Me.txtCount.Value) = CDbl(Me.txtQtyTray.Value) Then
    ' They are the Same
    Dim strSQL As String
    strSQL = "UPDATE tblMasterASN SET Status='Shipped' WHERE MasterASN = '" & Me.txtMasterASN.Value & "'"
   
    ' Execute the SQL statement to update the Status field
    CurrentDb.Execute strSQL

    ' Display a message indicating that the update is complete
    MsgBox "Status of Order With Master ASN of: " & Me.txtMasterASN.Value & "updated to 'Shipped'"
    Me.Refresh
ElseIf CDbl(Me.txtCount.Value) > CDbl(Me.txtQtyTray.Value) Then
    ' Me.txtCount.Value is greater than Me.txtQtyTray.Value
    MsgBox "Alert: Count is greater than Quantity in Tray"
Else
    ' They are Different
    MsgBox "They are Different"
End If

 

[The_Doc_Man]

Check it twice (or more). Once in Form_Current. Once in the control_LostFocus or control_AfterUpdate event for the control that you can enter data into that would change the count and thus change the comparison. Note that this control DOES NOT HAVE TO BE EITHER of the red-lined boxes. It is any control that is manually altered and that causes the change that leads to the comparison.

 

[Gasman]

I am not sure you need the use of Cdbl() ?

 

[The_Doc_Man]

Now that you mention it, @Gasman, you are right. Use of CDBL() is questionable here. It adds nothing (except to the time it takes) to the computation.

@Poco_90 - I'll add a suggestion for convenience, and it should NOT be construed as a statement of error on your part.

Use of Me.txtCount.value is adding to your typing effort. Access allows you to omit a property if it happens to be the default property for the object. In the case of ANY form control that HAS a value, .Value is the default and can be omitted. If a control does NOT have the value property, the reference to .Value would fail at compile time so you would know pretty quickly. Note that we are talking about "structurally having a value property", NOT about not having a .Value because it is null.

Further, the .Value property is valid anytime after Form_Open is complete. After the _Open event, a control .Value CAN be referenced even though it might not yet have a value. (The controls "appear" during Form_Load and gain values during Form_Current.) This is in contrast with the .Text property, which for bound controls is only valid when the control is in focus.

 

[moke123]

I would put the code in a procedure of its own in case you need to call it from more than one place. Without knowing your workflow it's hard to say where.
Select case may be a little cleaner to follow than If...Else...

Private Sub UpdateStatus()

    Dim strSQL As String

             If DLookup("Status", "tblMasterASN", "MasterASN = '" & Me.txtMasterASN & "'") = "Shipped" Then Exit Sub

    Select Case Me.txtCount

        Case Is = Me.txtQtyTray
       
            strSQL = "UPDATE tblMasterASN SET Status='Shipped' WHERE MasterASN = '" & Me.txtMasterASN.Value & "'"
 
            CurrentDb.Execute strSQL

            MsgBox "Status of Order With Master ASN of: " & Me.txtMasterASN.Value & "updated to 'Shipped'"
           
            Me.Refresh
   
        Case Is < Me.txtQtyTray
       
            MsgBox "Alert: Count is less than Quantity in Tray"

        Case Is > Me.txtQtyTray
       
            MsgBox "Alert: Count is greater than Quantity in Tray"

        Case Else
       
            'Do Nothing?

    End Select

End Sub

 

[Pat Hartman]

The on current event runs when the form navigates to a new record. You would NEVER put validation code in this event. Either this is a new record and no data has yet been entered into any control or it is an existing record and the data has been saved -right or wrong if you have no validation code in the correct event.

The BeforeUpdate event of a control is NOT triggered by VBA. Therefore, you cannot use that event for validation if the data is being populated using VBA. If the BeforeUpdate event of a control isn't triggered, then neither will the AfterUpdate event be triggered.

That leaves us with the Form's BeforeUpdate event. You should most likely be using this event. And there is no reason to run an update query after the fact.

I agree, the cdbl() function is noise and should be removed.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.txtCount & "" = "" Or Me.txtQtyTray & "" = "" Then
    ' Ignore if either value is null or ZLS
    ' MsgBox "One or both values are null. Cannot perform the comparison."
Else
    If Me.txtCount = Me.txtQtyTray Then
    ' They are the Same
        Me.Status='Shipped'
        MsgBox "Status of Order With Master ASN of: " & Me.txtMasterASN  & "updated to 'Shipped'"

    Else
        If Me.txtCount > Me.txtQtyTray Then
        ' Me.txtCount.Value is greater than Me.txtQtyTray.Value
            MsgBox "Alert: Count is greater than Quantity in Tray"
        End If
Else
    ' They are Different
    MsgBox "They are Different"
End If
End Sub

 

[Poco_90]

Thank you all for the replies and enlightenment on how to code more productively. I will take your suggestions on board.