Requery Popup form from another form (1 Viewer)

[chuckcoleman]

I'm sure this has been asked before, but I haven't been able to find the solution. I’m trying to get a popup form to requery after I enter an order on another form, (the order form). The requery will then show an updated number of pending orders. I seem to recall there are some challenges doing that on a popup form.

From the order form, (normal form), I’ve tried setting the focus on the popup form and on the popup form after it become active, (I think that happens when the order form sets the focus), the popup form does Me.Requery. It doesn’t work. By "doesn't work" I mean the popup form isn't requeried to show the updated number of orders.

Suggestions?

 

[theDBguy]

Hi. Have you tried something like?

Forms!PopupFormName.Recalc

 

[Pat Hartman]

Have you tried
Forms!someformname.Requery

If that doesn't work, You need to close the popup and reopen it. For example, if the pop up was opened with a where argument, the only option is to close and reopen. If the popup has a RecordSource query that includes criteria, then the .Requery method should work.

I don't recommend leaving multiple forms open unless the top one is modal and so is the only one you can interact with.

 

[chuckcoleman]

Hi. Have you tried something like?

Forms!PopupFormName.Recalc

I did. It didn't update the popup form. I tried the Recalc for the popup from the order form and also after the focus was set from the order form for the popup and in the On Activity event, I tried Me.Recalc. Still doesn't work.

 

[Pat Hartman]

Did you read the second part of my last post? HOW is the form being opened? Are you using a filter or where argument in the OpenForm method? If you are, you need to do what I told you to do, which is to close the form and reopen it. It is a quick operation, there shouldn't be any flicker.

Requery, Refresh, and Recalc do different things. It is good to know what each does so you know when to use which "re".

They are not interchangeable although Requery does everything the other two do so some people always use requery despite the bad side effects such as losing your current position in a subform.

Form.Requery method (Access)

Office VBA reference topic

docs.microsoft.com

Form.Refresh method (Access)

Office VBA reference topic

docs.microsoft.com

Form.Recalc method (Access)

Office VBA reference topic

docs.microsoft.com

 

[chuckcoleman]

Pat, thank you for your feedback. I've been able to get Forms!FormName.Requery to work. The update is almost instantaneous so it is working well. Thank you!

 

[Pat Hartman]

You're welcome. I still think it is a bad idea to have two active forms open at once so I never do it. When I open a Popup, it is model so you can see the form that called it but can't set focus back there.

 

[MajP]

IMO you should never hard wire code. Now you have tightly coupled the pop up to the calling form. A better approach is to have the calling form trap the popup forms events. In this design you are not tightly coupled. The popup can work with any form and not tied to the single calling form. See demo.
Or an even better solution is to open the popup in ACDIALOG windowmode. This stops code execution in the calling form. Then you can have the form requery itself once the code execution returns to the calling form on close of the popup.

 

[chuckcoleman]

Thanks Pat & MajP. Here is why the customer needs two forms open at once. Picture in the form on the right, the customer enters and order to schedule work for a certain day. In the form on the left, it currently shows, (in a continuous form sorted by date and city), the number of jobs that are pending. The instructions given by the owner to the data entry person is, "Don't exceed 14 jobs per day." Therefore, as the person is entering the order, she needs to look at the form on the left to make sure she doesn't exceed the 14 jobs. It works well now, however, I'll take a look at the Dialog mode, I just don't remember much about that. The problem with making the popup form modal is that you then can't enter a new order in the form on the right.

All helpful discussion. Thanks.

 

[MajP]

Any chance you can show a screen shot of these two forms at once. I would think there is a better user interface. At a minimum on the main form I would show a total jobs entered and then keep the user from entering any more plus give them a message saying all 14 jobs entered. Or can you have the continous form a subform for that day. As described does not seem that user friendly. I should have visible how many jobs exist for that day.

 

[Pat Hartman]

Therefore, as the person is entering the order, she needs to look at the form on the left to make sure she doesn't exceed the 14 jobs.

In the BeforeInsert event of the form, use a dCount() to count the number of existing jobs for the day. If the count exceeds 13 (I never use = in this situation), don't allow any more to be added OR allow for a one-job override to take care of a special case.

If dCount("*", "tblJobs", "JobDate = #" & Me.JobDate & "#") > 13 Then
    Msgbox "No more jobs may be added for this date",vbOKOnly
    Cancel = True
    Me.Undo
    Exit Sub
End If

 

[chuckcoleman]

Here is the screenshot. The order entry form is on the right and the popup form is on the left. On the right, the order entry person gets information about when the customer wants the job done. As she begins to enter the order on the right, she can look at the pending jobs on the left and determine if there are less than 14 jobs already pending for a town. If so, she will change the date. Once the "Add" button is clicked, an order is created in the system which will then show up on the technician's report of jobs to do on the Due Date. When the "Add" button is clicked, the system also sends and email to that customer letting them know their job has been scheduled for the date and time that was entered. Note, I initially had the Sub-Form at the bottom of the right form but the customer doesn't feel that is as convenient and easy to read as the new popup form. Finally, once the "Add" button is clicked, the number of jobs for that day for that Village/City is incremented by one.

It's actually working well.

 

[Pat Hartman]

So, you don't want to prevent the user from entering more than 14 jobs even though I showed you how to?

 

[chuckcoleman]

Sometimes there might be an exception. For instance, all of the jobs are in the same subdivision, and they can get more done. It serves the customer well.

Thank you for contributing.

 

[MajP]

IMO if I was the user I would not be happy with that user interface. First of all I would only want to see the total jobs pending for a town. That form would require the user to do mental gymnastics. You could easily show just that on the main form. As a user I would not want to have to search for that data on a second form. You have plenty of real estate on the entry form to show the details. I am with Pat. If your design requires two forms open at the same time to see relevant data, then it is a poor design.