By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,345 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Pausing Code to Wait for a Form to Close

P: 101
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.
Feb 15 '19 #1
Share this Question
Share on Google+
13 Replies

Expert 100+
P: 1,430
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

Feb 15 '19 #2

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

Expand|Select|Wrap|Line Numbers
  1. Private Sub btnChooseOperationLines_Click()
  3.     Dim db As Database
  4.         Set db = CurrentDb
  5.     Dim rstOrderComponents As Recordset
  6.     Dim OrderComponentsSQL As String
  8.     OrderComponentsSQL = _
  9.         "SELECT * " & _
  10.         "FROM tblOrdersComponents " & _
  11.         "WHERE OrderFK = " & Me.txtOrderPK & " " & _
  12.         "ORDER BY OrderComponentPK;"
  14.     Set rstOrderComponents = db.OpenRecordset(OrderComponentsSQL, dbOpenDynaset)
  16.     With rstOrderComponents
  17.         .MoveFirst
  18.         Do While Not .EOF
  19.             DoCmd.OpenForm "frmOrdersComponentsOperationLineEntry", acNormal, , "[OrderComponentPK] = " & !OrderComponentPK
  20.             .MoveNext
  21.         Loop
  22.     End With
  24. End Sub

Attached Images
File Type: jpg Screen Shot of Relationship Map.jpg (155.8 KB, 185 views)
File Type: jpg Screen Shot of Order Entry Form.jpg (103.5 KB, 178 views)
File Type: jpg Screen Shot of Component Line Entry.jpg (88.7 KB, 177 views)
Feb 15 '19 #3

P: 101
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.
Feb 15 '19 #4

Expert Mod 2.5K+
P: 3,055

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.
Feb 16 '19 #5

P: 101
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.
Feb 16 '19 #6

Expert Mod 2.5K+
P: 3,055
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.
Feb 16 '19 #7

P: 101
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.
Feb 16 '19 #8

Expert Mod 2.5K+
P: 3,055
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.
Feb 17 '19 #9

Expert Mod 10K+
P: 12,315
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.
4 Weeks Ago #10

P: 101
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!
4 Weeks Ago #11

Expert Mod 2.5K+
P: 3,055
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:

Expand|Select|Wrap|Line Numbers
  1. Do While Not Forms!NewForm.Form.chkContinue
  2. 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.
4 Weeks Ago #12

Expert Mod 10K+
P: 12,315
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:

Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  3. Dim lastIndex As Integer
  5. Sub ButtonStartChildProcessing_Click()
  6.    lastIndex = set to last index of child record that needs processing
  7.    Call NextChildProcessing(1)
  8. End Sub
  10. Sub NextChildProcessing(i As Integer)
  11.    If i <= lastIndex Then
  12.       ' Open Child Form, passing i
  13.    End If
  14. End Sub
Expand|Select|Wrap|Line Numbers
  1. Option Explicit
  2. Sub FinishedChild_Click()
  3.    ' Close Form
  4.    ' Call Form_Parent's NextChildProcessing(i + 1)
  5. End Sub
4 Weeks Ago #13

Expert 5K+
P: 8,597
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.
Expand|Select|Wrap|Line Numbers
  1. DoCmd.OpenForm "frmOrdersComponentsOperationLineEntry", acNormal, , "[OrderComponentPK] = " & !OrderComponentPK, acFormEdit, acDialog
3 Weeks Ago #14

Post your reply

Sign in to post your reply or Sign up for a free account.