Solved

How make screen restore after docmd.openquery that is set to maximize

Posted on 2013-12-05
8
542 Views
Last Modified: 2013-12-11
I have a command button on a form which opens a query.  Here is the code:

    DoCmd.OpenQuery "qryMemberLists", acViewNormal, acEdit
    DoCmd.Maximize

But when the user close the query I want the form that launched it to be restored to it's original size.  I tried docmd.restore on the form's on got focus event but that doesn't work.

Can this be done?
0
Comment
Question by:SteveL13
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
8 Comments
 
LVL 37

Expert Comment

by:PatHartman
ID: 39699325
Users should NEVER be given a raw query.  Change to display a form based on that query instead.  Queries do not provide any event processing so you can't validate data or prevent updates if the query itself produces an updateable recordset.  With a form, you have absolute control.  If the user wants to see something that looks like a query, use a form in datasheet view.  Using the correct technique will eliminate the problem because you will be able to open the popup form as modal and so control will return to the next line of code in the procedure that opened it when the form closes.
0
 

Author Comment

by:SteveL13
ID: 39699345
Great advice. But we want to be able to end up with an Excel file.
0
 
LVL 15

Expert Comment

by:JimFive
ID: 39699591
Try the on Activate event of the form instead of On Got Focus
0
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 37

Accepted Solution

by:
PatHartman earned 500 total points
ID: 39701000
If you want to end up with an excel file, you can use TransferSpreadsheet to export it rather than opening a query.  Or, if you want folks to be able to look at it first, add a button to the form to export it.

Now that you need to add a button to the form, it gets a little more complicated.  Forms in datasheet view don't display headers so there is no place to put a button.  My solution is to always make forms that are datasheets, subforms rather than main forms.  That way you can put the button on the main form.
0
 

Author Comment

by:SteveL13
ID: 39701065
We want to be able to view the query as a spreadsheet and then be able to either save it (somewhere on our computer) or close it.

We do not want it to export it without viewing it first.
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39701176
Then, add the export button to the form as I suggested.
0
 

Author Comment

by:SteveL13
ID: 39701205
Ok.  But I must have the following line of code wrong.

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "qryNonMemberList", "Non Member List", True

????
0
 
LVL 37

Expert Comment

by:PatHartman
ID: 39701276
Why do you think it is wrong?

Personally, I would specify the path along with the file name so I could find it later but if you know where it is being saved, you'll be able to find it.

DoCmd.TransferSpreadsheet , acSpreadsheetTypeExcel9, "qryNonMemberList", "C:\SomeFolder\Non Member List.xls", True

Of course, hard-coding the export name presents problems itself since you will keep overlaying the same file so my code would actually be a little fancier.
0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question