I have a form with a command button that launches a series of code and then opens a query. The form is set up as a popup, so it remains in front of the resulting query displayed. I tried minimizing the form, but it is difficult for the user to find as it gets very tiny and is easily overlooked.

What I would like to do, is have the form go to the back of the query and when the user closes the query window, have the form return to the front full sized. I tried the visible - not visible option but not sure how to make it visible dependent on the user closing the query window...

I am stumped. Any help provided is greatly appreciated!

Here is the code that sits on the on-click event of my command button on the form:

Private Sub Command58_Click()
'First step runs the query to append rows to the voucher table for those providers that are showing as on file in the AP system and if so adds them to the table
DoCmd.SetWarnings False
DoCmd.GoToRecord , , acNext
DoCmd.GoToRecord , , acPrevious
DoCmd.OpenQuery "QRY_APND_PROV_FIELDS_VOUCHER"
DoCmd.SetWarnings True


' This step Checks to see if the minimum number of vendors on file with AP has been met

Dim CNT_Tax_ID As Variant
CNT_Tax_ID = CurrentDb.QueryDefs("QRY_COUNT_VOUCHER_LOAD").OpenRecordset.Fields("TAX_ID")
If CNT_Tax_ID < 7 Then
MsgBox "you currently have " & CNT_Tax_ID & " vendors existing in the AP system. You must have a minimum of 7 existing vendors for a voucher bulk load submission to AP, please submit a manual check request for these vendors._
The spreadsheet listing the details needed will display when you click on OK"
Me.CMD_VCHR_LOAD.Enabled = False 'disables the button to create the voucher load because minimum number of vendors existing in AP has NOT been met

DoCmd.OpenQuery "QRY_NEW_CK_RQST_LOAD"
'**this is the query that displays - here is where I want the form to come back to the front when the user closes the query window***'
Else
MsgBox "you have the minimum of 7 providers not previously submitted to AP, go to the voucher load form to create the spreadsheet to be uploaded to AP"
Me.CMD_VCHR_LOAD.Enabled = True 'enables the bulk load button because minimum number of vendors existing in AP has been met
End If
End Sub

Hi,

What version of Access are you using? Please remember to select the version number when posting questions in case it becomes relevant to the discussion.

We highly recommend not exposing the tables or queries to the users. It is better to use a form for user interactions.

Just my 2 cents...

And once you set up a Form, as theDBguy suggested, using your Query as its RecordSource, you can set it up as a Popup Form, as well, and you will get the behavior that you want; the Query-based Form will open in front of the original Form, and when it closes the original Form will still be there.

Linq ;0)>

Thanks for the quick responses. So what you are saying is set up a form built off the query as a popup and it will behave as needed, correct? I will give this a try to see if it will work with all the functionality I need for the end user.

That's correct!

You all are brilliant! Thanks so much.

Here is the modified line of code that works like a dream after creating a form based on the query:

DoCmd.OpenForm "FRM_DATA_NEEDED_FOR_NEW_CK_RQST", acFormDS

The property for the form is set to popup on the other tab and it opens in datasheet view as needed by the user. When I close the datasheet, the original form returns to the fore.

I LOVE THIS FORUM

Don't want to rain on the parade. Can the original form be seen when the new form is open? Can the user click on it if so? Does the new form completely disappear?

Just trying to avoid the panicky user phone call. help !!!! I was updating in the form and it disappeared and I can't find it anywhere !!

Hi DayStar,

Glad to hear you got it to work. In case Bob's question is an issue for your users, try using this code:

DoCmd.OpenForm "FRM_DATA_NEEDED_FOR_NEW_CK_RQST", acFormDS, , , , acDialog

It will prevent the users from going anywhere until they actually close the popup form.

Just my 2 cents...

Thanks for the additional tips about avoiding user panic. Implemented and working beautifully. Thanks again

Hi,



We are all happy to help. Good luck with your project.