Check boxes in forms to call a query

[Senthil]

I have around 50 queries. These queries are run on a requirement basis.
For example, i have query1,query2,query3,query4,query5

If i select query1,query3 through a check box, the selected query should run.

How can i create a form and call these queries by selecting through check
boxes?

Pls help.

 

[Klatuu]

What kind of queries are they? Select queries or Action queries?

 

[tina]

rather than write a ton of code to handle all those query names, i'd suggest
creating a table to list them, as

tblQueries
QueryName (pk)
QueryRun (Yes/No field)

create a form bound to tblQueries - i'll call it sfrmQueries; setting it to
DatasheetView should do fine. create another form, unbound - i'll call it
frmMain; add a command button called cmdRunQueries. add a subform control to
frmMain, and set the control's SourceObject property to sfrmQueries. add
code to cmdRunQueries' Click event procedure to loop through the records in
tblQueries and run the queries where the Yes/No field = Yes, as

Private Sub cmdRunQueries_Click()

Dim rst As DAO.Recordset

Set rst = CurrentDb.OpenRecordset("tblQueries", dbOpenDynaset)

rst.MoveLast
DoCmd.SetWarnings False
Do
If rst("QueryRun") = True Then
DoCmd.OpenQuery rst("QueryName")
End If
Loop Until rst.EOF
DoCmd.SetWarnings True

End Sub

in the Close event procedure of frmMain, you can remove the checks from the
Yes/No field in tblQueries, as

Private Sub Form_Close()

CurrentDb.Execute "UPDATE tblQueries SET " _
& "QueryRun = False", dbFailOnError

End Sub

hth

 

[Klatuu]

I would not suggest check boxes for this use. The problem is, if new queries
are added, you will have to revise your form and redistribute the
application. I would consider a dynamic design that would require no changes
regardless of queries being added, deleted, or renamed.

First, I would suggest a multi select List Box to display your query names.
You can create a row source query for the list box using the System table
msysObjects. To get a list of all store queries:

SELECT Name FROM msysObjects WHERE Type = 5 AND Left(Name,1) <> "~";

Eliminating queries that start with "~" is because that is used for internal
queries, like row sources for combo boxes and list boxes.

Now each time you open the form, all the queries in you mdb will show in the
list box. Select the ones you want to run. Then use a command button to
actually run them. In this procedure, you loop throught the ItemsSelected
collection and execute the query.

Dim varItm As Variant
Dim strQryName As String

For Each varItm in Me.MyListBox.ItemsSelected
strQryName = Me.MyListBox.ItemData(varItm)
CurrentDb.Execute strQryName, dbFailOnError
Next varItm

Now, you have 4 lines of code instead of the 150 you would need if you used
check boxes.

 

[John W. Vinson]

I have around 50 queries. These queries are run on a requirement basis.
For example, i have query1,query2,query3,query4,query5

If i select query1,query3 through a check box, the selected query should run.

How can i create a form and call these queries by selecting through check
boxes?

Pls help.

What *are* these queries?

I've *rarely* but occasionally had to have an application run multiple append
or update queries. What I've done is to create a Table, tblQueries, with
fields QueryName (Text, Primary Key), ToBeRun (yes/no), and Sequence (number,
specifying the order in which they are to be run). You can have a Form to open
tblQueries, check the checkboxes, and then have a command button running code
like

Private Sub cmdRunQueries_Click()
Dim db As DAO.Database
Dim qd As DAO.Querydef
Dim rs As DAO.Recordset
Dim strSQL As String
On Error GoTo Proc_Error
Set db = CurrentDb
strSQL = "SELECT tblQueries.QueryName FROM tblQueries WHERE ToBeRun=True" _
& " ORDER BY Sequence;"
Set rs = db.OpenRecordset(strSQL, dbOpenSnapshot)
If rs.RecordCount = 0 Then
MsgBox "No queries to run!
GoTo Proc_Exit
End If
Do Until rs.EOF
Set qd = db.Querydefs(rs!QueryName)
qd.Execute dbFailOnError
rs.MoveNext
Loop
Proc_Exit:
Exit Sub
Proc_Error:
<handle the error condition with appropriate messages>
Resume Proc_Exit
End Sub



John W. Vinson [MVP]

 

[Senthil]

Hi tina,

I am just a beginner in access and absolutely not familiar with coding. Can
you guide me by giving me the complete code so that i can copy/paste and
execute the coding and learn.

 

[Senthil]

Hi Klatuu,

I am a begineer and do not know much on coding. Can you help me out in
detail on how to create a multiselect list box and run the requirement.

 

[Senthil]

Hi John,

I am a begineer and do not know much on coding. Can you help me out in
detail on how to do it.

 

[John W. Vinson]

Hi John,

I am a begineer and do not know much on coding. Can you help me out in
detail on how to do it.

ummmm...

I did.

Copy and paste the code into the Click event of the command button on your
form.
Select Debug... Compile <my database> from the menu.
Fix any compile errors (post back if you need help).
Create a Table named tblQueries with fields named QueryName (Text), ToBeRun
(Yes/No), and Sequence (number).
Fill tblQueries with the names of the queries that you want to run. Set
ToBeRun to True for each query that you want run, False for those queries that
you don't want to run right away.
Fill Sequence with numbers 10, 20, 30, 40 etc. (so you can insert other
queries into the sort order later if needed) to specify the order in which
they are to be run.
Click the button.

John W. Vinson [MVP]