create an updatable form with all the queries 1

[Ciro]

I'm interested in knowing if it possible to create a form and have always diplayed the queries created. This means that if I create a new query this will be found in my query form in a combo box or list box or what else. The Access version I use is Access 97
Thanks Ciro

 

[tunsarod]

Yes you can display a list of queries.

1. Create a form that contains a Combo box. Lets call it:
ComboBox1

2. In the Row Source property for the combo box type:
ListQueries

3. In the Database window select Modules and click New
or open an existing module.

4. In the module's code window paste the following code:


Function ListQueries(fld As Control, ID As Variant, row As Variant, col As Variant, code As Variant) As Variant
Dim dbs As Database, Qdf As QueryDef, ReturnVal As Variant
Static QdfArray(100) As String, qdfEntries As Integer

Select Case code
Case acLBInitialize ' Initialize.

Set dbs = CurrentDb

ReturnVal = Null
qdfEntries = 0
' fill the data array with query names
For Each Qdf In dbs.QueryDefs
QdfArray(qdfEntries) = Qdf.Name
qdfEntries = qdfEntries + 1
Next Qdf
' set the return value to the number of entries
ReturnVal = qdfEntries

Case acLBOpen ' Open.
ReturnVal = Timer ' Generate unique ID for control.

Case acLBGetRowCount ' Get number of rows.
ReturnVal = qdfEntries ' Equals number of queries

Case acLBGetColumnCount ' Get number of columns.
ReturnVal = 1 ' Only one column required

Case acLBGetColumnWidth ' Column width.
ReturnVal = -1 ' -1 forces use of default width.

Case acLBGetValue ' Get data.
ReturnVal = QdfArray(row)

Case acLBEnd ' End.
Erase QdfArray
End Select
ListQueries = ReturnVal
End Function


Save the changed module and open the form with ComboBox1 on it.

Click on ComboBox1 which should open with a list of all the queries in the database.



OPTIONAL ENHANCEMENT

Add a command button to the form but make sure that wizards are activated on the ToolBox menubar. Select the Miscelleneous category and the Run Query action from the two option lists.

Select any available Query to run from the button and save the button calling it cmdRunQuery.

Now open the button's code module and find the cmdRunQuery_Click event code. Change it to the following:


Private Sub cmdRunQuery_Click()
On Error GoTo Err_cmdRunQuery_Click

Dim stDocName As String

stDocName = Me.ComboBox1

If IsNull(ComboBox1) then
Exit Sub
Else
DoCmd.OpenQuery stDocName, acNormal, acEdit
End If

Exit_cmdRunQuery_Click:
Exit Sub

Err_cmdRunQuery_Click:
MsgBox Err.Description
Resume Exit_cmdRunQuery_Click

End Sub



Hopefully you should now be able to select any query from the drop list and click the button to run it.

Regards
Rod