Exit Do loop

[Poco_90]

Hi,
Looking for some guidance as I am not really sure how to handle this. I have an event on my form with a loop, and I nearly have everything perfect (well in my eyes J ). I am not sure how to refresh/reload my form from within my code.
The code below is what I would deem a successful event.

' Display a message indicating that the update is complete
MsgBox "Status of Order With Master ASN of: " & Me.txtMasterASN & " updated to 'Shipped'

This code is triggered from Form1(I know bad name). Ideally when we get to this the MsgBox (which I will eventually disable). I am looking for some sort of pop up message that will auto close after x seconds, then my main form Form1 would auto refresh with the focus on cboMasterASN of the main form, to allow the user to start working on the next order.

I have tried timers, procedures etc but seem to be getting all sorts of errors. I think because I am trying to execute these commands from within my loop/statement. Refresh and setfoucs don’t seem to work either while in the code. Can I do some sort of GoTo event to a procedure etc?

The full code is below. Thanks in advance.

Private Sub Form_Current()

    Dim displayInputBox As Boolean
    Dim valuesMatched As Boolean ' Declare and initialize to False

    ' Set displayInputBox to True at the beginning
    displayInputBox = True

    ' Check if either value is null
    If IsNull(Me.txtCount) Or IsNull(Me.txtQtyTray) Then
        ' Ignore if either value is null
        ' MsgBox "One or both values are null. Cannot perform the comparison."
    ElseIf (Me.txtCount) = (Me.txtQtyTray) Then
        ' They are the Same
        ' Check if MasterASNValue and MasterSerialValue match tstScannedASN and txtScannedSerial
        Dim MasterASNValue As Variant
        Dim MasterSerialValue As Variant

Do While Not valuesMatched
    Dim originalSerialValue As Variant ' To store the original value of txtMasterSerial

    If displayInputBox Then
        ' Store the original value of txtMasterSerial
        originalSerialValue = Me.txtMasterSerial

        MasterASNValue = InputBox("On the Master Label Please Scan ADVICE NOTE Number(N):")
        MasterSerialValue = InputBox("On the Master Label Please Scan SERIAL Number(M):")
    End If

    ' Check if both MasterASNValue and MasterSerialValue are not null and then compare them
    If Not IsNull(MasterASNValue) And Not IsNull(MasterSerialValue) Then
        If MasterASNValue = Me.txtMasterASN And MasterSerialValue = originalSerialValue Then
            ' They match
            valuesMatched = True ' Set the flag to exit the loop
            Dim strSQL As String
            strSQL = "UPDATE tblMasterASN SET Status='Shipped', [ScanMasterASN]='" & MasterASNValue & "', [ScanMasterSerial]='" & MasterSerialValue & "' WHERE MasterASN = '" & Me.txtMasterASN & "'"

            ' 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 & " updated to 'Shipped'"
           
            ' Open a form here
            DoCmd.OpenForm "frmBlank", , , , , acDialog
           
            ' Exit the loop
            Exit Do
        Else
            ' They don't match
            Dim response As VbMsgBoxResult
            response = MsgBox("ADVICE NOTE Number(N) and SERIAL Number(M) do not match the scanned values. Do you want to re-enter the data?" & vbCrLf & _
                            "Scanned ADVICE NOTE Number(N) is: " & MasterASNValue & vbCrLf & "Scanned SERIAL Number(M) is: " & MasterSerialValue, vbYesNo)

            If response = vbNo Then
                Exit Do ' Exit the loop if the user chooses not to re-enter data
            End If
            ' Set the flag to display InputBox again
            displayInputBox = True
        End If
       
    ElseIf Not IsNull(MasterASNValue) Or Not IsNull(MasterSerialValue) Then
        ' If either MasterASNValue or MasterSerialValue is not null but the other is null, display a message
        MsgBox "Both ADVICE NOTE Number(N) and SERIAL Number(M) must be entered. Please re-enter both values."
        displayInputBox = True ' Set the flag to display InputBox again
    End If
   
Loop

        ' Use On Error Resume Next to suppress errors during focus setting
        On Error Resume Next
        Me.cboMasterASN.SetFocus
        On Error GoTo 0 ' Reset error handling

        ' Alert if too many items are packed.
    ElseIf (Me.txtCount) > (Me.txtQtyTray) Then
        MsgBox "Alert: Quantity loaded on the pallet is greater than the Order Quantity"
       
    Else
        ' They are Different
        ' MsgBox "They are Different"
        'Debug.Print "Last Else"
    End If
    'Debug.Print "Last End IF"
End Sub

 

[bastanu]

I use the msgbox replacement included in the attached compressed archive file.

 

[The_Doc_Man]

First, just as a decluttering comment:

You have a bunch of in-line DIM statements that probably should be defined at the top of the procedure, immediately after the SUB declaration. A DIM is a compile-only statement. (Note that REDIM, however, IS executable.) So to avoid the visual clutter, move the DIM statements up.

Now, there IS such a thing as just using a CALL invocation for a procedure, or using the procedure name as the first thing on the line. NOTE that the syntax for arguments differs for the explicit CALL and implicit CALL.

The most important part is that you would need to tell us a bit more explicitly what happens (or doesn't) and what you expected to happen.

 

[Josef P.]

Please note:
MasterASNValue = InputBox("On the Master Label Please Scan ADVICE NOTE Number(N):")
=> MasterASNValue is never Null. VBA.InputBox returns a string.

Function InputBox(Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context]) As String

I tried a little refactoring:

Private Sub Form_Current()

    ' Check if either value is null
    If IsNull(Me.txtCount) Or IsNull(Me.txtQtyTray) Then
        ' Ignore if either value is null
        ' MsgBox "One or both values are null. Cannot perform the comparison."
        exit sub
    end if
  
    ' Alert if too many items are packed.
    If Me.txtCount > Me.txtQtyTray Then
        MsgBox "Alert: Quantity loaded on the pallet is greater than the Order Quantity"
        exit sub
    end if
      
    If Me.txtCount < Me.txtQtyTray Then
        exit sub
    end if
      
      
    ' Me.txtCount = Me.txtQtyTray
    ' They are the Same
    ' Check if MasterASNValue and MasterSerialValue match tstScannedASN and txtScannedSerial
    Dim MasterASNValue As Variant
    Dim MasterSerialValue As Variant
    Dim originalSerialValue As Variant ' To store the original value of txtMasterSerial

    Do

        ' Store the original value of txtMasterSerial
        originalSerialValue = Me.txtMasterSerial  ' Is this necessary for every loop run?

        MasterASNValue = InputBox("On the Master Label Please Scan ADVICE NOTE Number(N):")
        MasterSerialValue = InputBox("On the Master Label Please Scan SERIAL Number(M):")

        ' MasterASNValue and MasterSerialValue are not Null!  (InputBox returns not null)
        if len(MasterASNValue) = 0 then MasterASNValue = null
        if len(MasterSerialValue) = 0 then MasterSerialValue = null

    Loop Until CheckAndCompleteMasterASNandSerialValue(MasterASNValue, MasterSerialValue, originalSerialValue)

    ' Use On Error Resume Next to suppress errors during focus setting
    On Error Resume Next
    Me.cboMasterASN.SetFocus

End Sub

private function CheckAndCompleteMasterASNandSerialValue(byval MasterASNValue as Variant, _
                                                         byval MasterSerialValue as Variant, _
                                                         byval OriginalSerialValue as Variant) as Boolean

    dim ReturnValue as Boolean

    If Not IsNull(MasterASNValue) And Not IsNull(MasterSerialValue) Then '
        If MasterASNValue = Me.txtMasterASN And MasterSerialValue = originalSerialValue Then
            ' They match
          
            UpdateMasterASN MasterASNValue, MasterSerialValue
            
            ' Display a message indicating that the update is complete
            MsgBox "Status of Order With Master ASN of: " & Me.txtMasterASN & " updated to 'Shipped'"
        
            ' Open a form here
            DoCmd.OpenForm "frmBlank", , , , , acDialog
        
            ' Exit the loop
            ReturnValue = True
        Else
            ' They don't match
            If MsgBox("ADVICE NOTE Number(N) and SERIAL Number(M) do not match the scanned values. Do you want to re-enter the data?" & vbCrLf & _
                            "Scanned ADVICE NOTE Number(N) is: " & MasterASNValue & vbCrLf & "Scanned SERIAL Number(M) is: " & MasterSerialValue, vbYesNo) = vbNo Then
                ' Exit the loop if the user chooses not to re-enter data
                ReturnValue = True
            Else 
                ' Set the flag to display InputBox again
                ReturnValue = False
            End If
        End If
    
    ElseIf Not IsNull(MasterASNValue) Or Not IsNull(MasterSerialValue) Then
        ' If either MasterASNValue or MasterSerialValue is not null but the other is null, display a message
        MsgBox "Both ADVICE NOTE Number(N) and SERIAL Number(M) must be entered. Please re-enter both values."
        ReturnValue = False ' Set the flag to display InputBox again
    End If
  
    CheckAndCompleteMasterASNandSerialValue = ReturnValue

end function

private sub UpdateMasterASN(byval MasterASNValue as Variant, byval MasterSerialValue as Variant)
    Dim strSQL As String
    strSQL = "UPDATE tblMasterASN SET Status='Shipped', [ScanMasterASN]='" & MasterASNValue & "', [ScanMasterSerial]='" & MasterSerialValue & "' WHERE MasterASN = '" & Me.txtMasterASN & "'"

    ' Execute the SQL statement to update the Status field
    CurrentDb.Execute strSQL
end Sub

 

[Pat Hartman]

You are making this much harder than it needs to be. A simpler solution would be to create a query that selects only unshipped orders. Then you bind the form to that query and the form only ever shows unshipped orders. NO CODE IS REQUIRED.

Then the user can scroll through the records and mark the orders that were shipped. If he ships 3 out of 10 and reopens the form, it will show 7 records unless a new order was added meantime.

An even better solution is to mark the orders shipped in the shipping department but if you don't have computers there, that would be awkward.

 

[Poco_90]

Thanks for all the responses. I probably have made a bit of an abomination of it.

I am trying to have all the data entry by a barcode scanner. My aim was when the order was set to shipped, the person doing the packing would have the labels for the next order at hand and all they would have only to scan the MasterASN barcode from the new label to start the next order. This is why I was trying to get the focus on the cboMasterASN combo box.

Just to explain my process
My form is below. The person packing/shipping the pallet picks/scans the MasterASN number from the drop down and all the order information is displayed in the green area. I do have a query behind the combo box NOT to display “Shipped” orders, (@ Pat Hartman is this ok?)

In the Scanning & Palleting area(blue), the packer, then has to scan a serial number on the box (Our BoxID Serial) and Master ASN and Box Serial from a label the customer supplies. I do some checks when these 3 barcodes are scanned.

As the parts are being successfully scanned they are displayed in the subform, and the area in yellow displays what is packed and what the oldest week number of stock is available to the operator to pack. They can’t pack newer stock.

My on current code to summarise is looking at txtCount and txtQtyTray (highlighted in green). My code has probably morphed due to errors I was getting when these fields were null. Once these fields are equal I try to do the following.

Get the user to scan MasterASNValue MasterASNSerial from the label to verify it matches what is on the customer order area(green). If they don’t Match, ask user to scan again.

Once these match I record the 2 scanned values into the tblMasterASN table, and set the status of the order from ToBePacked to Shipped.

I also have a warning if txtCount exceeds txtQtyTray. I hope my explanation doesn’t confuse people.

@The_Doc_Man I hope above explains in more detail. I have tried to clean the code below.
@bastanu I like the message box replacement, but I am in the same boat when the message box closes, setting focus on my combo box.
@ Josef P. I tried your code but not sure about how to use Function InputBox(Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context]) As String

Thanks again for all the replies.

 

[Josef P.]

... but not sure about how to use Function InputBox(Prompt, [Title], [Default], [XPos], [YPos], [HelpFile], [Context]) As String

This is the interface of VBA.InputBox. I just wanted to show that it never returns Null but always a string.
Because of:

    If displayInputBox Then
        ' Store the original value of txtMasterSerial
        originalSerialValue = Me.txtMasterSerial

        MasterASNValue = InputBox("On the Master Label Please Scan ADVICE NOTE Number(N):")
        MasterSerialValue = InputBox("On the Master Label Please Scan SERIAL Number(M):")
    End If

    ' Check if both MasterASNValue and MasterSerialValue are not null and then compare them
    ' !! --> MasterASNValue and MasterSerialValue filled by InputBox => they are never Null
    If Not IsNull(MasterASNValue) And Not IsNull(MasterSerialValue) Then
             ^                              ^

 

[Poco_90]

I think I may be good. Found a rogue setFocus event in my code which I think caused my initial problem.

 

[The_Doc_Man]

I think I may be good. Found a rogue setFocus event in my code which I think caused my initial problem.

Just to amplify that for the newbies here... a .SetFocus brings attention to something on the display and makes primary input go to that item if it is capable of input functions. HOWEVER, there is a side effect. Many of the DoCmd.xxxx functions (and a few others) apply their effect to whatever has focus at the time. So @Poco_90 has found a rogue .SetFocus that might have accidentally affected focus for some command that ASSUMED the right thing was in focus at the moment - but it wasn't.

Good luck and hope that fixes your issue, Poco_90.

 

[gemma-the-husky]

@Poco_90

To have a timed message box open, I would do this

In my main form

docmd.openform "TimedMessage"

'maybe add arguments for the message, title, time to remain open etc
'also add a close button, so the user can dismiss it
'put your timer in the "TimedMessage" form, not in the calling application

'now you need code to wait until the "TimedMessage" form closes, as just below
'the doevents enables you to do other stuff, open other forms etc, which this process is paused.
'much better than freezing your app completely.

while isopen(acform,"Timed Message")
  doevents
wend

'now after the message form closes, you can carry on.


'Now you just need code for the isopen function
'I use this all the time, to show forms, queries etc
'acform by default, but actable, acquery etc for any other object

Public Function IsOpen(strForm As String, Optional doctype As Long = acForm) As Boolean
    IsOpen = (SysCmd(acSysCmdGetObjectState, doctype, strForm) <> 0)
End Function