URGENT - VBA Code to wait until Form Closes

[Luke Bailey]

I'm trying to complete some changes to a database that I have created and I need to finish it by tomorrow morning!

Here's the situation:

I have a form where people can order a number of products. They can set this form to varying statuses so that their supervisors can review the orders before they get placed. When the user (call them technicians) sets the order form to "review" status, the code goes through each individual item that they have ordered and checks to see if it has a serial number (thus making it a serialized item) because technicians need to enter additional information for all serialized equipment.

When it comes to a serialized item, the code opens up another form and goes to that specific items record so that the technician can edit the item-specific information.

Problem: I want the order form to cycle through these ordered items, but I need it to wait until they have closed that form that opens up for additional information before it continues on to the next item because sometimes there are multiple serialized items on one order.

Currently, it successfully goes through all of the items and identifies whether or not they are serialized items, but it goes through them all and opens up the same form for each serialized item before the user gets to put in data so it ends up with them only being able to enter data for the last serialized item.

Here is my current code:

Private Sub StatusID_AfterUpdate()
If StatusID = 5 Then
    lbSerials.Requery
    For x = 0 To lbSerials.ListCount - 1
        If lbSerials.Column(2, x) <> "" Then
            MsgBox "You have selected a Serialized item that requires Production Information.", vbInformation, "Production Information Required"
            DoCmd.OpenForm "Production", , , "[SerialNumber]= " & "'" & lbSerials.Column(2, x) & "'"
 
'<Need something here to make it wait?>
 
 
        End If
    Next x
End If
 
End Sub

I use lbSerials as a listbox that pulls in the serial numbers for all of the products in the order form. Then the code checks the correct column (2) of the listbox for all serialnumbers.

Any help is much appreciated!

~Luke

[missinglinq]

I believe when you open the second form, if you set the WindowMode parameter (I think it's the 6th argument) of the OpenForm command to acDialog Access will halt execution of the code until the second form is closed. acDialog sets Modal and Popup Properties to Yes and a form set to Modal, I believe, requires action and closing before other code continues.

Linq ;0)>

[Luke Bailey]

Perfect! Works like a charm. Thanks!

[missinglinq]

Glad we could help!

Linq ;0)>

[ucobear]

Thumb Up! Thank you. It helps me as well!