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

[dizzydangler]

Mary, in plain english, my criteria says:

"If the value in the control is null, return records with all values (both null and not null) for this field. If the control is not null, return all records which include the text entered in the control"

It's a little ungainly, but it has worked just fine as a criteria in the "standalone " query I built using the SQL view and linked to the original data entry form.

I tried your latest suggestion, and didn't get an error message, but didn't get it to return any results, either. Then I re-wrote my original criteria, taking your advice to replace double quotes inside the IIF statement with single quotes:

strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM [Database 1] " & "WHERE " & "((IIf([Forms]![Search_Database]![City] Is Null,(([Database 1].[City]) Like '*' & [Forms]![Search_Database]![City] & '*' Or ([Database 1].[City]) Is Null),(([Database 1].[City]) Like ('*' & [Forms]![Search_Database]![City] & '*'))))<>False);"

Something interesting happened. If I leave the <City> control blank, it returns all records in the table (which it should!) only problem is, I can only see the resulting table when I switch the form to design view. When I do this, the table
<Search_Databas e> drops down from top of the navigation bar and shows up under a bar labeled "Unrelated Objects." If I double-click, I can see the results.

If I enter a text string in the <City> control, the same thing happens--when I switch the form to Design View, the <Search_Databas e> table appears. However, when I double-click on the table to open it, I receive an "Enter Parameter" prompt for

Forms]![Search_Database]![City

If I enter a text string in this box, the query opens with exactly the data I'm looking for: all records which contain the text string. I don't know why the results aren't appearing until after I switch to Design View, and I can't write the criteria in such a way that it will run the query with no additional input. It's GOT to have something to do with they syntax used to "feed" the text string defined by strSQL_2 into the currentDb.Creat QueryDef function, because when I enter the criteria directly into the SQL view of a query, it runs just fine.

I've got to take a break to get some sleep, but I'll be back at it again tomorrow....

[MMcCarthy]

Add this line to the end of your code to open the query.

DoCmd.OpenQuery "conQUERY_NAME"

[NeoPa]

Wouldn't that be :

DoCmd.OpenQuery conQUERY_NAME

I'm going on the name of the item only here of course.

[MMcCarthy]

@NeoPa
LOL!

You are perfectly right Ade, didn't realise that was a variable name and not a query name.

Use NeoPa's example Joe.

[NeoPa]

I think, together, we add up to an almost decent expert :D

[MMcCarthy]

@NeoPa
Ha Ha ... :P

[ADezii]

What are you guys doing to my code! (LOL)!

[MMcCarthy]

@ADezii
I didn't touch it Sir, it was Ade Sir, really Sir, I'm innocent ... RUN ADE!

LOL!

[ADezii]

dizzydangler, I think that part of the problem is that you are attempting to modify and/or incorrectly copying perfectly functioning code such as provided and later modified in Post #5. In order to build your WHERE Clause into the logic, I included a Text Box on the Form named txtCity, within which you can specify a City to Filter on. Take notice of the code changes specified below:

1. New Variable Declaration in Line #4 to hold the WHERE Clause.
2. Code to check the Status of the txtCity Text Box, and to Set (Check) the City Check Box if necessary (Lines 9 thru 15).
3. Building of a simple WHERE Clause Lines 12 and 14).
4. Removal of the semi-colon in Line #32, and the concatenating of the WHERE Clause (strWhere).

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 = " WHERE [City] ='" & Me![txtCity] & "';"
Else    'No Criteria
   strWhere = ";"
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
strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM Employees" & strWhere
 
'Create the QueryDef Object
Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)

[dizzydangler]

IT WORKS!!!

I haven't checked the code that you posted yet, ADezii, but with Ade and Mary's input, I was able to get the query table to open automatically when the search button is clicked. Then it was just a matter of starting with a very basic criteria and building it up into a more complex expression.

First, to make the query return ALL records, null and not null:

strSQL_2="SELECT " & Left$(strSQL, Len(strSQL)-2) & " FROM [Database 1] " & "Where [City] Is Not Null Or [City] Is Null;"

Then I figured out how to write an IIF statement that returns all records with "null" values if the control is left blank and all records with "not null" values if anything is typed in:

strSQL_2="SELECT " & Left$(strSQL, Len(strSQL)-2) & " FROM [Database 1] " & "Where IIF([Forms]![Search Database]![City] Is Null, [City] Is Null, [City] Is Not Null;"

Then I got the LIKE statement working, returning all records which contain the field that is entered in the control:

strSQL_2="SELECT " & Left$(strSQL, Len(strSQL)-2) & " FROM [Database 1] " & "Where [City] Like '*'&[Forms]![Search Database]![City]&'*';"

And, finally, put it all together to give me the criteria that I want (everything if the control is left blank, all records which contain the entered text if anything is typed into the control:

strSQL_2="SELECT " & Left$(strSQL, Len(strSQL)-2) & " FROM [Database 1] " & "Where IIF([Forms]![Search Database]![City] Is Null, [City] Is Null Or [City] Is Not Null, [City] Like '*'&[Forms]![Search Database]![City]&'*';"

In this format, I should be able to string criteria together with the AND function (I've already gotten it to work with two, it's just a matter of jamming the rest in now that I know the correct syntax).

Speaking of that, I've found that editing code in the VB editor attached to the form is much less forgiving than what I was doing previously in the SQL View of the query. Spaces matter, for instance! I wasn't able to get the Like function working until I took out all the spaces in the expression <'*"&[ControlName]&"*">, and "SELECT" didn't work until I added a space afterwards and made it "SELECT ". Wow, I feel like I have learned a lot thanks to all three of your help and patience--and I know I'm not even scratching the surface.

I only have three (minor) questions left (for this project, at least!):

1) when I start stringing my criteria expressions together, they get really long really fast, but when I type "enter" to break the line and start a new one, like I used to do in SQL View, the code won't compile. What's the syntax for inserting a line break in the code, for ease of reading/editing?

2) is there a way to control the order of the columns when the query table is built? Not a major deal, because I can always move them around after the table appears. It looks like they're coming up in a random order, but they come up this way every time--maybe in the order in which I created the check boxes? Is there any way to change the order besides physically moving the boxes around on the form?

3) I created a macro to prevent mutually exclusive boxes from being checked at the same time (for instance, if the "return all fields" radio button is ticked, it automatically checks all 32 available fields, but as soon as you "uncheck" one of the 32 checkboxes, the radio button switches from "return all fields" to "return selected fields:") This macro won't run unless you manually enable it every time you open the file. Is there a way to save the file to sharepoint so that the macro remains permanently enabled no matter who opens it, eliminating the extra step?

Thank you, thank you, thank you all for your help!

Joe