Open a Form after the close of a Query

[gblack]

OK I don't even know how to look this up online or even how to phrase it without spelling it all out.

What I want to do is have access open a form; after the user views a given query, then closes it.

Right now:
I have a form that gives the user choices, then it creates the SQL (querydef) based on the choices and pulls up said query for them to view/use.

I had problems at first because the query would always pull up behind the form, thusly the user would never be able to see the query, unless s/he knew that you had to minmize the form. Then a second form became necessary as a requested pop-up, when more drilldown capability was needed. So I just made the code close the forms when said query opens.

The issue is that the user may wish to use the form again and look at the data differently... so is there any way to get a form to open back up after the user is finished with a specific query?

Thanks much,
Gary

 

[pbaldy]

Most of us only let users view forms and reports. One reason is that queries have no events associated with them, so I don't think you can tell when they close it. I would present the query results in a form. In it's close event, you can open the other form.

 

[gblack]

Yeah I know I could show the results in another form... but most of the users know how to manipulate/utilize querys to some extent and they want to be able to change the query accordingly.

This data isn't something they can really mess up too badly (or maybe a better way to say this is: it doesn't matter if the data gets messed up), because part of the code builds a table that's designed specifically for them. I use a passthrough query to pull the larger dataset from the big SQL Server database, then create a make table query that is basically a temp table... that they have access to. So they can fiddle with that all they want... I don't really care, because the next person who goes in will pull a new dataset based on the dates and group they wish to look at. Or if they know they screwed up, they can always reset the data.

And Yes, I know I could create another form (or in this case several other forms), but that would be much more work on my part and more restrictive for those who do know how to use the query section... and for those who don't, they can still get the data they need through the query results and the forms I have already created. My thought was to make maximize flexibility and minimize my part... because I'm not even supposed to be working on this thing... I got kind of bamboozled into it by my own stupidity.

I understand that there's 8 million ways to build functionality... I didn't really want a "you could do it this way instead" answer if at all possible. I do appreciate the help, as such.

Honestly, I don't care if it is bad practice, or non-standard, or even if it makes you uber-VBA-Geniuses cringe... I just want to know IF there is a way to capture the event of a query closing to trigger an OpenForm event? (hmm... I think I'll search the web on that question.)

If not... then I will obviously have to go another route and all suggestions as such will be appreciated... but I have found that there's often some brainiac out there who knows everything there is to know about whatever it is you want to know and they can tell you how you can do it... no matter how painful the answer/code might be. So... If yes: How?

And if someone (in the know) can say definitively that there is no way to do this... that will be very helpful as well... then I'll look at other ways to accomplish this.

Plus, I get so sick of not being able to do something the way I am thinking of doing it and always having to accomplish the task in some round about way, that sometimes you just want to do it the way you want to do it, regardless... know what I mean?

Thanks for the input...
G

 

[gblack]

OK I looked it up right after I posted that last response and the WEB said Queries don't have events... I suppose I will run with that....

http://www.experts-exchange.com/Mic...23889188-ON-Close-of-a-query-open-a-form.html

If anyone has read this and now has a better idea of what I am attempting to do... I am open for suggestions...

 

[pbaldy]

I already mentioned that queries don't have events, which is why this will be tricky. Will any form be open? One option might be to use the activate event of that form to catch when the query closes and focus goes to that form. Another might be a hidden form with a timer event running that looked to see if the query was open (search on IsLoaded). That said, I just looked and it doesn't look like IsLoaded will test for queries.

I also suspect you have your form's popup/modal settings set to keep them on top. If you change that, the query should come up on top of them and you don't have to close them.

 

[ByteMyzer]

You can always use this function to tell you if a query is open:

[COLOR="Navy"]Public Function[/COLOR] IsQueryLoaded([COLOR="navy"]ByVal[/COLOR] sQryName [COLOR="navy"]As String[/COLOR]) [COLOR="navy"]As Boolean[/COLOR]

    IsQueryLoaded = (SysCmd(acSysCmdGetObjectState, acQuery, sQryName) <> 0)

[COLOR="navy"]End Function[/COLOR]

It won't tell you, however, whether the query is open in Datasheet, Design or SQL view.

 

[jojo]

This is to pbaldy, who replied on 12/15/08!! to someone who was trying to open a query, but then retreive the calling form when the user closed the query. I am trying to do the same thing now!! Is the same true for a table? There are a few limited events regarding updating data for a table. for whatever dumb reason one of the users must have a button to open the table, but when she closes it, then what. I made the form be a popup to position it on the screen, so therefore, it is on top o;f the query when it opens, but if i make it NOT a popup it opens taking up the whole screen despite autocenter set to yes. Any way to get it to open centered with set dimensions if form is a popup, bec then she could deal with query/table, then get back to the screen? Without isqueryloaded function??
Thanks for any help!
Jojo

 

[pbaldy]

Yes, the same applies to a table (though ByteMyzer's solution may have a table equivalent). I don't believe there are any events available to tell you when the person closes it. I'd use forms. You should be able to size a form as desired. If you have 2007/2010, check the setting of

Office Button/Access Options/Current Database/Document Window Options

 

[jojo]

Yes, the same applies to a table (though ByteMyzer's solution may have a table equivalent). I don't believe there are any events available to tell you when the person closes it. I'd use forms. You should be able to size a form as desired. If you have 2007/2010, check the setting of


Office Button/Access Options/Current Database/Document Window Options

Thank you PBaldy!!!
That did the trick! All this time (2 months) that i have been working on this system with forms calling other forms and reports, and things opening up looking decent on the screen, it was this access 2010 frill that gave me such a hard time! thank you for your post of 12/15/2008, and for your quick reply today. I have gotten answers 4 times since beginning of july from helpful people on this forum, and I don't know how many times google sent me here to read old posts that were exactly what i needed. the query now opens on top of the form, and when she finishes doing her thing, she closes it and the from is right there waiting for her to click on reports. (I don't know why she can't use the form with several fields being automatically updated based on info she enters in some fields).
JOJO

 

[pbaldy]

Glad it worked for you. I would never let a user in a table or query. You have no control over what they might do there, unless I suppose if the query were read-only.

 

[jojo]

I agree with you totally, but the one thats paying the bill wants this button, and knows the probs with it, so i follow orders! Thank you again, it's such a relief having it look so seamless now. The forms look normal sized, and come forward and go backward as they should. this forum with you smart responders has been helpful beyond!

 

[pbaldy]

Happy to help, and at the end of the day we do have to keep the person signing the paychecks happy!