Solved

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

Posted on 2013-12-05
8
472 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
Comment Utility
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
Comment Utility
Great advice. But we want to be able to end up with an Excel file.
0
 
LVL 15

Expert Comment

by:JimFive
Comment Utility
Try the on Activate event of the form instead of On Got Focus
0
 
LVL 34

Accepted Solution

by:
PatHartman earned 500 total points
Comment Utility
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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 

Author Comment

by:SteveL13
Comment Utility
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
Comment Utility
Then, add the export button to the form as I suggested.
0
 

Author Comment

by:SteveL13
Comment Utility
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
Comment Utility
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

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
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 stored procedures 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 Micr…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

762 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

12 Experts available now in Live!

Get 1:1 Help Now