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

[NeoPa]

@dizzydangler
I wish I had a pound for everytime I've seen that associated with something either extremely advanced or just plain wrong in concept. The concept here is fine, but where you get the idea this is ordinary from I have no idea. It doesn't sound like you would have the relevant experience to know. This is not a problem of course. Just weird that so many people feel they are in a position to judge when they're just starting out.
@dizzydangler
That would be putting it too strongly. The thinking makes sense, but queries have columns (Fields) as defined by the SQL. A Null value doesn't remove the field. It simply puts a Null value in it for a particular record. Removing a field requires a redesign of the query itself. This can be done by adding the fields dynamically in SQL, but you would still need a QueryDef (saved Access Query) object to update. I wouldn't recommend this way though, as a QueryDef has more attributes per field than are created simply by changing the SQL.

What I would do is create a template QueryDef with all possible fields set up perfectly as you would want them. What you then do (at run time) is create a copy of this QueryDef and then either update the SQL for that object, removing the fields necessary from the SELECT statement, or remove the fields using VBA.

How do these ideas sound to you?

[NeoPa]

Another thought would be to design the template QueryDef as before, but then create the SQL string in VBA selecting from this QueryDef instead of the table. That way all the field sizes and formats would carry through anyway. unfortunately you would still need to create a new (probably only temporary) QueryDef to display the values, as SQL cannot be displayed directly without one. Of course if you only want the query to be used as the input to another object (Report; Form; etc) then this is no longer true. In fact you wouldn't really need the template QueryDef in the first place if that were the case.

[ADezii]

I've just helped someone with the exact same problem. I'll return later and Post my approach with the sample code solution.

[ADezii]

This was my approach to a similar scenario. I based my Demo Code on the Employees Table of the Sample Northwind Database. Feel free to use the code and also download the Attachment if you so desire. The basic concept is:

1. Include a Check Box on a Form for every Field contained in the Employees Table of the Northwind Database.
2. Assign an appropriate Label to each Check Box, but more importantly, set the Tag Property of each Check Box to match exactly the Name of the corresponding Field. For instance, in the Hire Date Check box:
   1. Set the Label to Hire Date
   2. Set the Tag Proprty to HireDate
   3. The Name of the Check Box is completely irrelevant and not needed
3. In the Click() Event of a Command Button:
   1. Declare and Define a Constant named conQUERY_NAME
   2. Loop through every Control on the Form, see if the Control is a Check Box, if it is a Check Box see if it is Selected, if it is Selected concatenate it to a SQL String to be used later.
   3. If the SQL String has a Zero Length, get outta dodge since no Field(s) were selected.
   4. DELETE the existing Physical Query. This will not cause an Error since code execution will resume to the Next Statement at this point.
   5. Build the Final SQL Statement.
   6. Create a Query named conQUERY_NAME based on the built SQL Statement.
   7. Personally, I prefer a Physical Query for possible later reference.
4. The complete Code Segment follows:
   

   Private Sub cmdTest_Click()
   On Error Resume Next
   Dim ctl As Control
   Dim strSQL As String
   Dim strSQL_2 As String
   Dim qdf As DAO.QueryDef
   Dim qdfDemo As DAO.QueryDef
   Const conQUERY_NAME As String = "Selective Employee Fields"     'Physical Query Name
    
   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
   strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM Employees;"
    
   'Create the QueryDef Object
   Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)
    
   Exit_cmdTest_Click:
     Exit Sub
    
   Err_cmdTest_Click:
     MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
     Resume Exit_cmdTest_Click
   End Sub

5. Adding Criteria to the mix would increase the level of complexity and I avoided this.
6. Download the Attachment to get a better picture of exactly what is going on.

[NeoPa]

ADezii's post illustrates with fully fleshed out code how this can be done.

NB. He has taken the basic Create from SQL approach that I advised against in post #2, but with a couple of very minor changes, and a template QueryDef defined by you, you can use this code to handle the more sophisticated approach that lets you control various other options that are not available to basic SQL.

Fundamentally though, this is a perfect illustration of what you need to do.

[dizzydangler]

Thanks, ADezzi--I will give it a try and let you know how it goes. BTW, appreciate the time you took to post such a detailed reply. I'm basically teaching myself/learning as I go, and it really helps me to see the code broken down like this so I can start understanding how it works.

Cheers,

Joe

[ADezii]

@dizzydangler
Not a problem, Joe. I give you a lot of credit for tackling such a problem.

[dizzydangler]

OK, I think I'm making progress. I want to make sure that the assumptions I made about the code you posted are correct, then I'll go into what's happening now.

First of all, I understand that all the terms used are pre-defined SQL functions or commands (I think this is the right terminology), with the exception of:

QUERY_NAME (in lines 8, 20, 28: I assign this name to my query)
cmdTest (in lines 1, 22, 30, 33, 35: should be cmd[whatever I'm calling the "search" button])
SQL and SQL_2 (lines 4, 5, 13, 25, 28: I've left these as-is, for simplicity's sake)
Employees (line 25: Name of database the query is referring to--I'm calling mine Database_1)

Anything else?

OK, I've entered the code in the form's visual basic code builder window. I think it's working...sort of...because when I click the "search" button (to run the query) with no checkboxes ticked, I get...nothing. That is what I would expect to happen because of the line

If strSQL="" Then Exit Sub

However, when one or more boxes are ticked, I get the error message:

"Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'"

I understand that these are the first command words for a query but, the way I understand your code, at least the SELECT and FROM portions of the query should have been created by the line

strSQL_2 ="SELECT" & Left$(strSQL, Len(strSQL)-2) & "FROM Database_1;"

I've tried pasting in the WHERE statement from my original query so that it reads:

strSQL_2 ="SELECT" & Left$(strSQL, Len(strSQL)-2) & "FROM [Database_1]" & "WHERE" & "[the rest of my original query (BTW, this was working when I ran it without the field selector checkboxes--I built it in SQL view of a separate query, then assigned a macro to the "search" button to run the query), consisting of multiple IIF/AND/OR statements];"

but this doesn't seem to be working, either. I get a Compile Error: Syntax Error when I try to save the code.

Please let me know if you can tell what I'm doing wrong--I hope I've provide you with enough information. Once I get this problem licked, my next challenge is to restrict the line

For Each ctl In Me.Controls

to search through a finite number of controls, not all the controls on the form. This is because I've got some boxes that function as query criteria and some boxes that function as "field selectors." I've assigned the criteria boxes names/tags (I've kept the checkbox names and tags the same, for simplicity's sake) like CheckField1 and the field selector boxes names/tags like ShowField1 to differentiate them. I've tried using

For ctl = ShowField1 to ShowField32

and

For ctl = ShowField1 to ShowField32 In Me.Controls

in place of the "For Each ctl In Me.Controls" line.

The first returns the Compile Error: Type Mismatch, and the second returns the Compile Error: Syntax Error. Any suggestions on this would be helpful, but it's somewhat irrelevant until I can get the query to run in the first place.

Thanks for the help!

Joe

[ADezii]

QUERY_NAME (in lines 8, 20, 28: I assign this name to my query)

It is conQUERY_NAME and the difference is major. Replace 'Selective Employee Fields' with the physical Name of the Query that you wish to create.

Const conQUERY_NAME As String = "<Query Name Here>"

cmdTest (in lines 1, 22, 30, 33, 35: should be cmd[whatever I'm calling the "search" button])

Correct, and need not be prefaced with cmd.

Employees (line 25: Name of database the query is referring to--I'm calling mine Database_1)

Not the Name of the Database but the Name of a Table.

OK, I've entered the code in the form's visual basic code builder window. I think it's working...sort of...because when I click the "search" button (to run the query) with no checkboxes ticked, I get...nothing. That is what I would expect to happen because of the line

If strSQL="" Then Exit Sub

Correct

strSQL_2 ="SELECT" & Left$(strSQL, Len(strSQL)-2) & "FROM [Database_1]" & "WHERE" &

Needs modification:

rSQL_2 ="SELECT" & Left$(strSQL, Len(strSQL)-2) & "FROM <Table Name> WHERE " & 'Criteria to follow

For Each ctl In Me.Controls

to search through a finite number of controls, not all the controls on the form. This is because I've got some boxes that function as query criteria and some boxes that function as "field selectors." I've assigned the criteria boxes names/tags (I've kept the checkbox names and tags the same, for simplicity's sake) like CheckField1 and the field selector boxes names/tags like ShowField1 to differentiate them. I've tried using

For ctl = ShowField1 to ShowField32 'Will never work

and

For ctl = ShowField1 to ShowField32 In Me.Controls 'Will never work

in place of the "For Each ctl In Me.Controls" line.

The first returns the Compile Error: Type Mismatch, and the second returns the Compile Error: Syntax Error. Any suggestions on this would be helpful, but it's somewhat irrelevant until I can get the query to run in the first place.

If you have 'other' Check Boxes on your Form for various reasons:

1. Do NOT modify the code logic that loops through the Controls on the Form, but make a simple adjustment to the code itself.
2. Only include a Tag Property Value for those Check Boxes that will be used in the Field selection process, and no other.
3. Change the Control Looping Segment to:
   

   For Each ctl In Me.Controls                                     'Query every Control on the Form
     If ctl.ControlType = acCheckBox And ctl.Tag <> "" Then        'Is it a Check Box with a Tag Value?
       If ctl.Value Then                                           'Is it selected?
         strSQL = strSQL & ctl.Tag & " ,"                          'Build the Field Selection(s)
       End If
     End If
   Next

4. Other than cosmetic, and the incorporation of Criteria, there are no other changes to the code that will be required.