Checkbox form to allow user to select query output fields

[dizzydangler]

Hi, I'm new to access (2007, running on XP), but I've gotten some great help from this site on my project so far. However, the more I know, the more complex the problems seem to become....

I'm working with all of my data in a single table and, right now, I've got a form linked to a query that allows the user to input whatever search criteria they want through a variety of controls. I've also created a series of check boxes on the form representing every possible field (columns in the table) that the query could return. Since not every user needs or wants to see every field contained in the records, I would like the user to be able to limit the fields returned by the query to only the checkboxes he/she ticks. After the fields are selected, the query should run as usual, returning only the records that satisfy the specified criteria, but only displaying the fields ticked off in the boxes.

I'm building the query in SQL View, and I figure that the key to this must have something to do with the "SELECT" line. Since a normal "fixed-field" query has the fields to be return specified by

SELECT [NameOfTable]![NameOfField1], [NameOfTable]![NameOfField2], etc...

I've tried to build a "dynamic" SELECT statement using the IIF function:

SELECT IIF([Form]![chkbx1]=True, [NameOfTable]![NameOfField1], Null), IIF([Form]![chkbx2]=True, [NameOfTable]![NameOfField2], Null), etc...

This is not working, of course--I don't think "Null" is the proper argument to enter for the condition "chkbx1=Fal se", but I don't know what else to do. Are there any other conditional functions that could help me build a list of SELECT fields based on the Yes/No value of the checkbox controls in the form?

Or am I going about this in completely the wrong way. I think there must be a way to do this, because what I'm trying to get the query to do doesn't seem to be anything too out of the ordinary. Any help would be appreciated!

Thanks in advance,

Joe

[ADezii]

I prefer the single strWhere approach, and I'll show you how it can be done with Multiple criteria when I get a chance. Someone will probably beat me to the punch, and that is fine.

[NeoPa]

@Tarheel
That sounds like a good design.

As far as one or more string variables goes, it really only matters what you end up with as a result (in the SQL string). If you use multiple variables, be careful not to concatenate them together, forgetting to leave a space or other white-space characters between them. I include below some advice I often give out about working with SQL in string variables.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

[NeoPa]

NB. I moved another (hijack) post by the same member (Tarheel) to Cascading ComboBoxes.

[ADezii]

@Tarheel
Hello Tarheel. Since you have been closely monitoring this Thread, I used the existing code, only revised to demo every possible combination of Criteria on three Fields, namely: [Region], [City], and [Title]. SQL is not my strong point, so do not take my code as the ultimate truth. Members like NeoPa, Stewart, missinglinq, etc. are better than I at this. Use this as a guideline, but follow their further advice if given.

Dim ctl As Control
Dim strSQL As String
Dim strSQL_2 As String
Dim strWhere As String
Dim qdf As DAO.QueryDef
Dim qdfDemo As DAO.QueryDef
Const conQUERY_NAME As String = "Selective Employee Fields"     'Physical Query Name
 
'If Criteria is specified for the [City] Field, the Check Box must be selected
If Not IsNull(Me![txtCity]) Then
  Me![Check10].Value = True
    strWhere = " [City] ='" & Me![txtCity] & "' AND "
End If
 
'If Criteria is specified for the [Region] Field, the Check Box must be selected
If Not IsNull(Me![txtRegion]) Then
  Me![Check14].Value = True
    strWhere = strWhere & " [Region] ='" & Me![txtRegion] & "' AND "
End If
 
'If Criteria is specified for the [Title] Field, the Check Box must be selected
If Not IsNull(Me![txtTitle]) Then
  Me![Check6].Value = True
    strWhere = strWhere & " [Title] ='" & Me![txtTitle] & "' AND "
End If
 
For Each ctl In Me.Controls                 'Query every Control on the Form
  If ctl.ControlType = acCheckBox Then      'Is it a Check Box?
    If ctl.Value Then                       'Is it selected?
      strSQL = strSQL & ctl.Tag & " ,"      'Build the Field Selection(s)
    End If
  End If
Next
 
If strSQL = "" Then Exit Sub        'No Fields selected, get out!
 
CurrentDb.QueryDefs.Delete conQUERY_NAME        'DELETE existing Query
 
On Error GoTo Err_cmdTest_Click
 
'Build complete SQL Statement
If strWhere = "" Then
  strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM Employees;"
Else
  strWhere = Left$(strWhere, Len(strWhere) - 5)
  strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM Employees WHERE " & strWhere & ";"
End If
 
'Create the QueryDef Object
Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)
 
DoCmd.OpenQuery "Selective Employee Fields", acViewNormal, acReadOnly

P.S. - Combo Boxes vs Text Boxes does not make a differe3nce, in this case it is only the Name of the Control that matters. Pay close attention to Lines 9 thru 25 and 42 thru 47.

[Tarheel]

Brilliant. Thanks so much, ADezii!

Works Perfectly

Sorry NeoPa, I figured I would pose a follow-up question while I had ADezii's attention. No cause for alarm.

[NeoPa]

Absolutely no cause (As long as it doesn't happen again) ;D