You do not necessarily need to empty the query results table. The rest I think would be fair to consider doing each time the button is pushed.
As far as dynamically reading the controls and building the query, in my mind that would mean you would at least need to refresh the SQL each time the button is pushed as someone could have change the state of the query builder form, correct?
Oh, and you do not need to create the table each time either. Leaving an empty table in the DB file is of no problem at all.
Excellent! Now we're getting somewhere! Now I just need to sort out how to make all that magic happen! I'll get to work on it and report back if/when I encounter further issues.
As far as dynamically reading the controls and building the query, in my mind that would mean you would at least need to refresh the SQL each time the button is pushed as someone could have change the state of the query builder form, correct?
Correct. But it's already doing that. Since the SQL is built inside the Click event and the controls are checked each time the Click event executes, the resulting query will always be based on the most recent form state.
I see no need for a table here at all. If all you want to do is build a SQL statement on the fly and then open the query to view the results all you need is QueryDefs. Just create one saved query. Call it whatever you want and select whatever fields from the table. It won't matter how you build it initially because it will be getting modified regularly anyway. Then your code is simply;
Code:
Dim mySQL As String
Dim strSel As String
Dim strWhere As String
Dim qdf As DAO.QueryDef
'Build SELECT Statement (strSel) based on user input (checkboxes)
'Build WHERE clause (strWhere) based on user input
'Build mySQL
mySQL = "SELECT " & strSel & "FROM tblMain " & "WHERE " & strWhere
'Run a query based on mySQL
Set qdf = CurrentDb.QueryDefs("qryResults")
qdf.SQL = mySQL
DoCmd.OpenQuery("qryResults")
I don't see why it needs to be any more complicated than that.
I see no need for a table here at all. If all you want to do is build a SQL statement on the fly and then open the query to view the results all you need is QueryDefs. Just create one saved query. Call it whatever you want and select whatever fields from the table. It won't matter how you build it initially because it will be getting modified regularly anyway. Then your code is simply;
Code:
Dim mySQL As String
Dim strSel As String
Dim strWhere As String
Dim qdf As DAO.QueryDef
'Build SELECT Statement (strSel) based on user input (checkboxes)
'Build WHERE clause (strWhere) based on user input
'Build mySQL
mySQL = "SELECT " & strSel & "FROM tblMain " & "WHERE " & strWhere
'Run a query based on mySQL
Set qdf = CurrentDb.QueryDefs("qryResults")
qdf.SQL = mySQL
DoCmd.OpenQuery("qryResults")
I don't see why it needs to be any more complicated than that.
Supplying parameters to a saved query is faster than building the SQL string for each search. The saved query has a stored execution plan while the execution plan must be rebuilt each time the new SQL is run.
However, displaying a query directly to a user is not a good design practice as it provides no managment of the data modifications. The user can edit anything they like. The query results should be displayed in a form or report with the query used as the RecordSource.
But since the data is to be displayed in a form then there is no point editing the SQL of the RecordSource query. One might as well feed the SQL text directly to the RecordSource of the form.
I agree with all of your points. However, in this case the OP seems to want to allow the users to not only select different criteria, but to actually select which fields they want returned by the resulting query. May or may not be a good idea - depending on the competence of the user base perhaps - but that seems to be the intent. Using a form for display would certainly provide more control over data interaction, but that would add another level of complication in that form design aspects/Control Sources would need to be managed on the fly.
I suppose they could use, for example, a form in datasheet view with enough Controls to handle the the maximum number of fields that could be returned by the query (assuming the maximum would be a relatively small number of fields) and just allow random columns to be blank after requery. A less than elegant solution for certain.
