Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

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

Posted on 2013-12-05
8
Medium Priority
?
598 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 40

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
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
LVL 40

Accepted Solution

by:
PatHartman earned 2000 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 40

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 40

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

[Webinar] Database Backup and Recovery

Does your company store data on premises, off site, in the cloud, or a combination of these? If you answered “yes”, you need a data backup recovery plan that fits each and every platform. Watch now as as Percona teaches us how to build agile data backup recovery plan.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In a use case, a user needs to close an opened report by simply pressing the Escape (Esc) key. This can be done by adding macro code in Report_KeyPress or Report_KeyDown event.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

578 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