Solved

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

Posted on 2013-12-05
8
490 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
  • 4
  • 3
8 Comments
 
LVL 34

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
 
LVL 34

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
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

 

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 34

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 34

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

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

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

Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

919 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now