Checkbox form to allow user to select query output fields

**NeoPa** · Nov 10 '09, 12:30 PM

Originally posted by dizzydangler

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.

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.

Originally posted by dizzydangler

Or am I going about this in completely the wrong way.

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** · Nov 10 '09, 12:45 PM

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** · Nov 10 '09, 02:17 PM

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

**ADezii** · Nov 10 '09, 04:18 PM

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:

Include a Check Box on a Form for every Field contained in the Employees Table of the Northwind Database.
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
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.

The complete Code Segment follows:

Code:

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

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

Attached Files

Create QueryDef.zip (419.0 KB, 1239 views)

**NeoPa** · Nov 10 '09, 09:52 PM

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** · Nov 11 '09, 05:09 AM

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** · Nov 11 '09, 01:38 PM

Originally posted by 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

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

**dizzydangler** · Nov 11 '09, 10:07 PM

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** · Nov 11 '09, 11:31 PM

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.

Code:

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:

Code:

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:

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

Change the Control Looping Segment to:

Code:

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

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

**dizzydangler** · Nov 12 '09, 01:23 AM

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

OK--got it! That's what I meant, still learning the syntax.

Needs modification:

Code:

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

Still not sure I'm getting this right. Should <Table Name> be in brackets: [Table Name]? I've had to do that in the SQL view of the "old" query because the actual table name has a space in it. I'm assuming that this line of code defines the string "strSQL_2" which is then pasted by the "Set qdfDemo" command on the next line into an SQL statement much like what I constructed manually to build my "old" query in the SQL view. If this is true, I would assume that it needs the brackets, or I need to remove the space from the table name--something I'd rather not do because I have so much other code written which refers to it with the space in it.

Also, it looks like you've got everything between the FROM and the WHERE statement inside of double quotes. Then you put the & symbol, followed by single quotes, all the criteria code then...? a semi-colon, then double quotes?

Finally, I'm going to have to paste quite a few lines of code into the 'Criteria to follow...' section, because I've got about 20 parameters which are used in the query. Is there a length limit to strSQL_2 and, if so, is it possible to define multiple strings and them use them in the "Set qdfDemo" command line?

I will play around with different combinations to see what works--just asking the questions in advance in case I can't crack it.

Thanks again for your time! No way I'd even be close without your help.

Joe

**ADezii** · Nov 12 '09, 01:38 AM

Still not sure I'm getting this right. Should <Table Name> be in brackets:

If the Table Name contains a Space it must be in Brackets, if not no, as in:

Code:

[Table Name] <==> TableName

Also, it looks like you've got everything between the FROM and the WHERE statement inside of double quotes. Then you put the & symbol, followed by single quotes, all the criteria code then...? a semi-colon, then double quotes?

It can all be contained inside double quotes unless you have to refer to something explicitly. To must also use the proper Delimiters (' for Strings, # for Dates, etc.)
Numeric

Code:

"Select * From Employees Where [EmployeeID] = 9999;"
"Select * From Employees Where [EmployeeID] = " & Me![txtEmployeeID] & ";"

String

Code:

"Select * From Employees Where [LastName] = 'Smith';"
"Select * From Employees Where [LastName] = '" & Me![txtLast] & "';"

Date

Code:

"Select * From Employees Where [BirthDate] = #9/2/1954#;"
"Select * From Employees Where [BirthDate] = #" & Me![txtDOB] & "#;"

**dizzydangler** · Nov 12 '09, 04:31 AM

Still not working.

First question: looking back through what the code is doing, I just realized that the Check Box tags should probably be an exact match of the field names in the table being queried--when I've written code directly into the SQL view of a query (successfully!) , what follows after "SELECT" are strings like [Database 1]![Field 1], etc.

Quote:
If the Table Name contains a Space it must be in Brackets, if not no, as in:
Expand|Select|W rap|Line Numbers [Table Name] <==> TableName

Assume that if the answer to my first question is "yes," then this rule must apply to the Tags, as well, so I either write the tag name as [Field 1], or write the line 13 code as

Code:

strSQL = strSQL & "[ctl.Tag]" & ", "

Does it make a difference which way I do it?

If the rest of the code was working, I would be able to solve this problem by trial and error, but I am still getting the same "Invalid SQL Statement; expected 'DELETE', etc..." when I run the query with one or more of the "field select" boxes ticked. Here is a simplified version of my code, with the criteria for only one field included in the "WHERE" statement (there are about 20 IIF statements, but I thought it would make more sense if I got the code to work with just one):

Code:

Private Sub SearchDB_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 = "Search_Database"

For Each ctl In Me.Controls
If ctl.ControlType = acCheckBox And ctl.Tag <> "" Then
If ctl.Value Then
strSQL = strSQL & ctl.Tag & ","
End If
End If
Next

If strSQL = "" Then Exit Sub
CurrentDb.QueryDefs.Delete conQUERY_NAME
On Error GoTo Err_SearchDB_Click
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);"

Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)


Exit_SearchDB_Click:
Exit Sub

Err_SearchDB_Click:
MsgBox Err.Description, vbExclamation, "Error in SearchDB_Click()"
Resume Exit_SearchDB_Click
End Sub

I tried it again, even more simplified, with no "WHERE" criteria, so the line 25 code looks like:

Code:

strSQL_2 = "SELECT" & Left$(strSQL, Len(strSQL) - 2) & "FROM [Database 1];"

...same result.

There's got to be something wrong with where I'm putting the quotes (delimiters?), I just can't see it.

**MMcCarthy** · Nov 12 '09, 05:27 AM

I've made two minor changes to your code which I have commented. This should get you a little further ahead.

Code:

Private Sub SearchDB_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 = "Search_Database"
      
    For Each ctl In Me.Controls
        If ctl.ControlType = acCheckBox And ctl.Tag <> "" Then
            If ctl.Value Then
                ' put space after comma
                strSQL = strSQL & ctl.Tag & ", "
            End If
        End If
    Next
      
      
    If strSQL = "" Then Exit Sub
    CurrentDb.QueryDefs.Delete conQUERY_NAME
    On Error GoTo Err_SearchDB_Click
    
    ' add space after SELECT, before FROM and after WHERE
    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);"
    
    Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)
      
      
Exit_SearchDB_Click:
    Exit Sub
      
Err_SearchDB_Click:
    MsgBox Err.Description, vbExclamation, "Error in SearchDB_Click()"
    Resume Exit_SearchDB_Click
End Sub

Always remember the spaces before and after when substituting values into a string building a query statement.

**dizzydangler** · Nov 12 '09, 05:52 AM

IT WORKS!!! (sort of!)

after reading over my last post, I went back and re-did all the tags on the "Select Field" checkboxes to match the field names in the [Database 1] table, including brackets [] when they were more than one word. I also realized, based on your explanation, that I needed to type a space after SELECT and before FROM

"SELECT " not "SELECT" and " FROM... not "FROM...

I ran it using a very simple criteria:

Code:

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

Nothing happened, but I got no error message. I went to close it out and, whaddaya know, a query table called Search_Database appears under an "Unrelated Objects" title bar in the Navigation View on the left!

I opened up the table and, sure enough, found all records listed where the field "City" was not null!

I played with the checkboxes and got it to build the query results table using with columns only showing the fields I selected. Getting a little cocky now, I went ahead and inserted one of my full-length criteria strings:

Code:

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

This criteria worked fine in the original query (run from the form called <Search Database>), written like:

Code:

WHERE ((IIF([Forms]![Search Database]![City] Is Null, (([Database 1].[City] Like "*" & [Forms]![Search Database]![City] & "*" Or (([Database 1].[City] Like "*" & [Forms]![Search Database]![City] & "*")))<>False);

It returns all records if the control <City> is left null but if anything is entered, it returns all records containing the text entered in the control.

All I did was drop the [Forms]![Search Database]! from in front of the control fields. I ran it that way the first time, got a "Type Mismatch" error, then figured I didn't need the form reference since I was running the query straight from the form itself (and your examples didn't have it, either).

No dice, still got the "Type Mismatch" error.

So, my questions are, in order of importance:

1) what is wrong with my criteria formatting?
2) how do I get the query results to automatically pop up in a new table, instead of showing up in the navigation bar after I close the form? (when I hang the DB on the server, the navigation bar is hidden to protect the users--all excel junkies like myself--from the temptation to open up the master table and start plowing through the data to find what they want. So, I need the table results to pop up automatically)
3) Is there a way to control the order of the columns in the query results?

ADezii, you've helped me way more than I can tell you. This is not a life-or-death issue, by any means, and I don't want to impose on your time and patience. At this point, I'm working to provide a more convenient and user-friendly product to the users and trying to learn something useful in the process. (and, OK, I am REALLY stubborn, especially when I KNOW there's got to be a way to get this thing to do what I want it to.) Any help is appreciated, but I'll understand if you want to call it quits. I'll just take my clown show to another forum...:)

Joe

Checkbox form to allow user to select query output fields

Checkbox form to allow user to select query output fields

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment