If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Check boxes in forms to call a query
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. |
#2
|
|||
|
|||
Check boxes in forms to call a query
What kind of queries are they? Select queries or Action queries?
-- Dave Hargis, Microsoft Access MVP "Senthil" wrote: 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. |
#3
|
|||
|
|||
Check boxes in forms to call a query
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 "Senthil" wrote in message ... 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. |
#4
|
|||
|
|||
Check boxes in forms to call a query
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. -- Dave Hargis, Microsoft Access MVP "Senthil" wrote: 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. |
#5
|
|||
|
|||
Check boxes in forms to call a query
On Mon, 24 Dec 2007 10:30:00 -0800, Senthil
wrote: 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] |
#6
|
|||
|
|||
Check boxes in forms to call a query
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. "tina" wrote: 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 "Senthil" wrote in message ... 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. |
#7
|
|||
|
|||
Check boxes in forms to call a query
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. "Klatuu" wrote: 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. -- Dave Hargis, Microsoft Access MVP "Senthil" wrote: 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. |
#8
|
|||
|
|||
Check boxes in forms to call a query
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" wrote: On Mon, 24 Dec 2007 10:30:00 -0800, Senthil wrote: 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] |
#9
|
|||
|
|||
Check boxes in forms to call a query
On Thu, 27 Dec 2007 09:03:01 -0800, Senthil
wrote: 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" wrote: On Mon, 24 Dec 2007 10:30:00 -0800, Senthil wrote: 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] John W. Vinson [MVP] |
Thread Tools | |
Display Modes | |
|
|