Solved

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

Posted on 2013-12-05
8
535 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 36

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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
LVL 36

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 36

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 36

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

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!

Question has a verified solution.

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

Suggested Solutions

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 …
Did you know that more than 4 billion data records have been recorded as lost or stolen since 2013? It was a staggering number brought to our attention during last week’s ManageEngine webinar, where attendees received a comprehensive look at the ma…
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…
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…

713 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