listbox of queries

[imdl]

Can someone advise on how to create a list box showing all the query names? Then, a button would be used to run a selected query.

imdl

 

[DCrake]

Firstly you need to go to tools and options and check the box to show system files.

Then create a query and pick the MSysObjects table and drag down the coloum that contains the object names and filter on type for queries (not known of hand) then save this query and use it for your combo.

However, the names you have chosen may not be as acceptable to the user. It may be more prudent to create a new table with the object name and user friendly name that can be offered in the query. But use the object name as you criteria.

David

 

[imdl]

thanks, david.

i was able to create the list box of query names.

further, i would like to run the query upon selection of the name in the list box, or there could be button to run the selected query. can you help again?


imdl

 

[Robbie]

I'm very new on this forum so I hope nobody minds me butting in but I was browsing the threads and thought I could help...

Assuming your combo box is called cmbQueries, try this in the AfterUpdate event for cmbQueries. The error catching is neccessary because you cannot execute a select query!

Private Sub cmbQueries_AfterUpdate()
On Error GoTo cmbQueries_AfterUpdate_End
 
    Dim qryDefs As QueryDefs
    Dim qryRun As QueryDef
 
    Set qryDefs = CurrentDb.QueryDefs
 
    Set qryRun = qryDefs(cmbQueries.Value)
 
    qryRun.Execute
 
    MsgBox "Query '" & Me.cmbQueries.Value & "' Executed"
 
cmbQueries_AfterUpdate_End:
 
    If Not qryRun Is Nothing Then
        qryRun.Close
        Set qryRun = Nothing
    End If
 
    Set qryDefs = Nothing
 
    If Err.Number <> 0 Then
        MsgBox Err.Description, vbCritical
    End If
End Sub

It will only work if the query you select is not a SELECT query.

 

[gemma-the-husky]

the other thing - generally this isnt going to be so useful at all - you can anyway, just open the database window, and run your queries from there!

but as you develop queries you will find that some are dependent on others, and some need parameters etc to run properly.

your interface should be forms/reports based on tables and queries, with buttons or code to run action queries.

 

[imdl]

thanks a lot for your replies.

i put the combo box with the query names (from David's message ). At the on click event of the command button, i put this code for each of the query names.

If Me.[comboboxname] = "queryname" Then
DoCmd.OpenQuery "queryname", acNormal, acEdit
End If