New Query Button 1

[jcg6]

Is there a way to put a button on a form that will enable the user to create a new query? In my appication, I want users to be able to create ad hoc queries. I need a button that will open up to a new select query. Does anyone know of a way that I can do this? I know that there is a new query button that can be placed on toolbars, but I would like to reside within a form. Please let me know as soon as possible. Thanks very much!!

 

[suziqueue]

Just create a blank query, save it, maybe call it qryUser.
On your form create a command button and exit out of the wizard. Right click in the command button in design view and go to properties. In the "On Click" event, use the drop down menu and chose event procedure. Click teh button beside it with the 3 dots (...) Between the 2 lines of code that it automatically puts there add this line of code

DoCmd.OpenQuery ("qryUser&quot, acViewDesign, acEdit

When your user clicks the button an empty query will pull up. Hope this helps

RLW

 

[mdthornton]

That's a good start but what happens if/when the user saves the query? Best Regards,
Mike

 

[suziqueue]

You have my curiosity up now Mike, I actually use my previous suggestion and my users know not to save the query using a different name (they each have their own front end). I've never had a problem (yet). If there is a cleaner, better way I would love to hear it. My VB knowledge is somewhere between slim and some so I'm willing to learn all I can and try to help when I can.

 

[mdthornton]

Apologies - I wasn't trying to play games - quite the opposite. I saw the original post earlier and was interested in whether anyone had any ideas. Your's was neat but I just wondered if/how you stopped users from saving, rather than saving as.

I'm going to have a play in my testdb, I'm curious now
Best Regards,
Mike

 

[suziqueue]

aha.. See what you started jcg6 - now you have three of us trying to learn something... What were you thinking?? <grin>

I've just been lucky I guess, my users actually listened to me when I told them &quot;NO&quot;. There's a scary thought - the users actually listening......

I suppose it's off to the brainstormin' board for the both of us, huh Mike? I'm curious to see what we come up with...

 

[mdthornton]

How about this, it seems to work.

Public Function adhoc_query()
Dim qdf As QueryDef
Dim name As String

On Error Resume Next
DoCmd.DeleteObject acQuery, &quot;qryUser&quot;
Set qdf = CurrentDb.CreateQueryDef(&quot;qryUser&quot
DoCmd.OpenQuery &quot;qryUser&quot;, acViewDesign, acEdit

End Function

I tried getting a handle on the existing qryUser to just reset the SQL but couldn't get that to work... let me know if you come up with anything. Best Regards,
Mike

 

[suziqueue]

Hey Mike great minds must think alike - I came up with basically the same thing. Only I just put this code in the on click event

Private Sub Command0_Click()
DoCmd.DeleteObject acQuery, &quot;QueryNull&quot;
CurrentDb.CreateQueryDef (&quot;QueryNull&quot
DoCmd.OpenQuery (&quot;QueryNull&quot, acViewDesign, acEdit
End Sub

The only problem I see with either of our codes is - What if they save the query as something else? How in the world would you handle that?

 

[mdthornton]

Does it matter? It's neat in a way because if they write a query and want to keep it they can do a save as. If not, it just gets trashed next time they write a new one. Best Regards,
Mike

 

[suziqueue]

The only bad thing about them saving it to something other than qryUser is that, unless they have access to the database window, they will no longer have access to that query. The button when clicked only brings up a new qryUser. Eventually your database could get clogged up with all of the queries that your users have saved to only they know what. You would almost have to give them a list box of some sort where they could either pick a query that they have already created or create a new one - you would have to limit the list to only their queries. And I'm sure there is a way to probably do this but I haven't a clue how to do it. I may play with it Saturday - hopefully our suggestions so far have gotten the originator of this thread over the hump.

 

[jcg6]

Wow, I just got in this morning and saw all of the replies to my original post. Thanks for all of the ideas! I will have to let my users know not to save their queries, since I am not giving them access to the database window. Again, thanks very much for your help. I really appreciate it!!