Pausing Code to Wait for a Form to Close

[GKJR]

I've been trying to figure this one out for a while now and have finally relented to posting in here. I am developing an application for a manufacturing company and I'm working on an order processing routine. Their orders specify a product which is based on multiple processes. For each of those processes, I need to have user input on certain field values in order to process the order. I have developed a routine that creates the child records called Order Components based on the product. These are stored in a table with referential integrity enforced to the Orders table. Now I'm trying to prompt the user to fill out the additional fields needed for each component so that the order can be processed. I'm using a recordset to step through each component and then using DoCmd.OpenForm… and sending the primary key for the Order Component to open a pop-up form to get these additional values. The procedure works to an extent, but my code doesn't pause when the form gets opened to allow the user to fill the values in. It basically keeps opening the form (even though it's already open) and then stops at the last component and the procedure is finished. I've tried setting the Modal property to yes so that the pop-up form opens in modal mode, but this didn't work. I also tried programming in the pop-up form's load and open events to see if my code execution switches away from the calling procedure, but it doesn't. The code in those events only gets called for the last instance of the form opening. My next thought that I'm going to try is to use the timer interval to check the Forms collection and keep on checking until that form isn't there, but I don't like this approach at all. It seems like a dirty work around to a problem that shouldn't be this hard to figure out.
Any suggestions would be much appreciated. I hope I explained this well enough for you to follow along. Thanks in advance.

[PhilOfWalton]

Perhaps, if you could send an image of your relationship pane (with all the fields in the tables showing), and images of your forms, it would give us a better chance of helping you

Phil

[GKJR]

Here are some screen shots of the items you requested. Also here is some of the code I'm working on...

Private Sub btnChooseOperationLines_Click()
 
    Dim db As Database
        Set db = CurrentDb
    Dim rstOrderComponents As Recordset
    Dim OrderComponentsSQL As String
 
    OrderComponentsSQL = _
        "SELECT * " & _
        "FROM tblOrdersComponents " & _
        "WHERE OrderFK = " & Me.txtOrderPK & " " & _
        "ORDER BY OrderComponentPK;"
 
    Set rstOrderComponents = db.OpenRecordset(OrderComponentsSQL, dbOpenDynaset)
 
    With rstOrderComponents
        .MoveFirst
        Do While Not .EOF
            DoCmd.OpenForm "frmOrdersComponentsOperationLineEntry", acNormal, , "[OrderComponentPK] = " & !OrderComponentPK
            .MoveNext
        Loop
    End With
 
End Sub

[GKJR]

I want an easy way for the user to enter the Operation Line instead of entering it in the sub-form on the Order Entry form. The [Total Cost / 1,000 ft] and the [Costing String] fields are calculated based on the Operation Line. I need to process this entire order at the push of 1 button, instead of splitting it up into multiple buttons.

[twinnyfo]

GKJR,

Why would you open multiple copies of the same form—one for each component? That just doesn’t make sense.

WHy not use a sub form (in continuous mode) that lists all the components associated with the order, and the user then updates the required information for each of the components on that sub form? That would be a more traditional and reliable method.

[GKJR]

My order entry form does exactly that, but I want the subform to get opened during the routine as those records are being created so that the rest of the order can be processed in the same routine. These order component records are automatically generated based on the first 3 fields on the order entry form. That is what the first button on the bottom of the form does. I want the rest of the order processing to all be in the same routine, so I don't want the user to have to choose those fields in the subform and then push another button. It's how I am choosing to do my user interface, it may not make sense to you but I think it's pretty important.
By the way twinnyfo, when you put the pictures in the post you showed the relationship map 3 times instead of putting the other 2 pictures in it.

[twinnyfo]

OK - fixed the images.

FIrst, We usually try to never twist people’s arms into doing things differently “just because”. It appears you may have good reason to do what you’re doing, but here is an alternate recommendation.

In lines 8-12 of your code, what you are essentially doing is creating the filter for your data entry form, but you cycle through those items using your code, and opening a separate form for each “filter”. Why not rather try to build the record source for that pop up form—as a whole—then, open the form using that record source, and the user can cycle through all the records (and even go backwards and forwards easily) and thus, all the components can be viewed from the same form you have already built?

I hope my description makes sense. However, I think that this would be a much more straightforward approach to what you are doing, rather than opening multiple instances of the same form, which will simply get in the way of each other.

Hope this hepps.

[GKJR]

Thanks twinnyfo I will consider what you're recommending and get back to you.

Just to be clear though, my intention is not to open multiple instances of the same form, but rather open the form, wait for the user to make their selection, and then when the form closes the loop continues and opens the form again with the next record. The code on the button in the pop up form simply closes the form.

[twinnyfo]

I understood your intention from the original post. If that is the direction you absolutely need to go, I think there are some ways we can address that. I just don’t think that is the best way to approach this issue. Let’s see how you come along with using the pop up form with a Recordset first.

[Rabbit]

That is what the first button on the bottom of the form does. I want the rest of the order processing to all be in the same routine, so I don't want the user to have to choose those fields in the subform and then push another button.

How are you setting up your parent form and subform? Because no buttons are required in a properly set up subform. The parent form would be tied to the order table and the subform would be tied to the components table. They would be set up so they are linked. A new entry in the subform would automatically create a linked record. No need to introduce any buttons or code into this setup to ensure correct entries into the tables.

[GKJR]

Hi Rabbit, my parent and child form are set up properly. The reason I need to use a button to create these Order Component records is because they are actually snapshots of the current costs associated with the raw materials used. A product is chosen in the order, and then my procedure steps through all of the processes associated with it and pulls out the raw material costs to create a permanent record of them at the time the order was taken.

And twinnyfo, I have been working more on this and have made some progress by setting the Window Mode to acDialog when I open the form. I made the change to show all of the records in one continuous view, but I still prefer my first method opening it for one record at a time and I'm going to see if I can figure it out.

Thanks for the input!

[twinnyfo]

OK - So, if you still prefer to open the form sequentially, just one idea that may work is to have an unbound control on the form you open (you could use a check box chkContinue with a default value of False). Then in the parent form, open the first instance of the form. Within your loop for opening the forms, you can add:

Do While Not Forms!NewForm.Form.chkContinue
Loop

This will get the main form into an infinite loop. Then, when the user is done entering data on the new form, they click a Complete button, which changes the value of chkContinue to True. I would then use the Main Form to close the pop up form and open the next one.

Make sense?

There may be other ways to do this, but this would accomplish the intent of what you want to do.

[Rabbit]

My preference, like TwinnyFo's, is to see all related subrecords in a list and be able to quickly go down the list to input the required piece of data.

As an alternate to twinny's workaround, my idea is to implement a series of functions that call each other with a module level or global level variable to track progress. For example, the shell could look something like this:

Form_Parent

Option Explicit
 
Dim lastIndex As Integer
 
Sub ButtonStartChildProcessing_Click()
   lastIndex = set to last index of child record that needs processing
   Call NextChildProcessing(1)
End Sub
 
Sub NextChildProcessing(i As Integer)
   If i <= lastIndex Then
      ' Open Child Form, passing i
   End If
End Sub

Form_Child

Option Explicit
Sub FinishedChild_Click()
   ' Close Form
   ' Call Form_Parent's NextChildProcessing(i + 1)
End Sub

[ADezii]

Sorry for coming in so late but to accomplish what you are requesting, should you insist on this approach, you would need to set the WindowMode Argument of the OpenForm() Method to acDialog. Now, for each !OrderComponentPK contained within the Recordset, you can Open your Form filtered for that PK and make any necessary changes without looping through the entire Recordset.

DoCmd.OpenForm "frmOrdersComponentsOperationLineEntry", acNormal, , "[OrderComponentPK] = " & !OrderComponentPK, acFormEdit, acDialog

[GKJR]

Hey everyone, I know it's been a little while since this thread was posted. I want to stress how much I really appreciate all the input and how grateful I am to have a community of pros and power users willing to help out with an issue like this.
My needs on this project changed and I actually used a work around where I no longer needed to grab that information at the time the code was creating the records. However, ADezii I came to the exact same conclusion that you did if I were to pursue this route.
I've nearly finished this project by now and the company is slowly getting their massive amount of data into it so we can really finish fine tuning it. I ended up going with the first method mentioned about using a subform to edit the data and then using another button to finish the procedure, but this only worked because I made an adjustment to the data structure and I no longer needed to grab a certain piece of information at the time I was creating my records for the Order Components. This form is one of the most complex procedures I've worked on and it works perfectly for them so I wanted to post it here for you all to see. I also included a couple of other images of my forms that are in the finished product.
I chose an interesting route for this application in which I knowingly violated data integrity rules in order to treat the multiple operations that are performed during the manufacturing process as the same type of record in Order Components. The Order Components are created with VBA, and after they're created the user has to choose which Operation Line the product is going to run on before the reports are printed. Any fields that were created by code are locked in all of the forms. The reason I did this was to give them a permanent record of their costing information, as well as a unique Lot # that they can refer back to. I wrote functions for each type of operation that puts all of the costing information into a string so that they can see where there costs are coming from. Granted this is probably over some of your heads since they are in a very niche industry, but I'm curious to hear what some of the experts think about this approach. I don't see any other way to have this type of permanent costing record with a normal relationship because these costs are routinely updated with market fluctuations, so if you run a normal report you are getting the live information and the historical data is lost.
I realize they could have issues down the road if they ever change the names of their operations, but I stressed to them that they can never do this. Even if they did, it's not the end of the world, they just won't be able to open those reports from records that were previously created. They probably won't ever need to see those reports again though.
One of the images I posted is a Flexible Browser Form that I learned from Allen Browne. He put his work out there for everyone to use and I've made dozens of these types of forms in all of my different applications. In fact, that was the first type of form that I learned to program when I started learning VBA about 6 years ago. I still give him a shout out in all of my code. This is another example of the type of cooperation and collaboration that I think we have all leaned on to develop our skills.