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=False", 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.

[dizzydangler]

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:

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]

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:

[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

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

String

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

Date

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

[dizzydangler]

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|Wrap|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

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):

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:

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]

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

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]

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:

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:

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:

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

[dizzydangler]

Hey again, msquared! I caught the spacing error in the "," and the "SELECT" and "FROM... statements, but I didn't get the ...WHERE " & "((IIF... bit. Let me give it a try....

[dizzydangler]

Nope, no luck. I tried:

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);"
 

and

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]) Is Null),(([Database 1].[City]) Like (" * " & [City] & " * "))))<>False);"
 

both give me the "Type Mismatch" error. Could it be something else?

Joe

[MMcCarthy]

Hi Joe

Firstly remember to add code tags to your posts as it is confusing to follow otherwise.

I'm just looking at your criteria now and will get back shortly.

Mary

[MMcCarthy]

Joe

Your Iff statement doesn't make sense to me. Can you tell me in English what the logic of this is supposed to say.

Mary

[MMcCarthy]

OK Joe

Try this ...

strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM [Database 1] " & _
"WHERE [Forms]![Search Database]![City] Is Null " & _
"OR [Database 1].[City]) Like ' * ' & [Forms]![Search Database]![City] & ' * ' "
 

I don't think you should have been using IIF at all. Also I replaced the double quotes around the * with single quotes. You can't put double quotes inside a string (not easily at least) so you have to use single quotes.

Mary

[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_Database> 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_Database> 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.CreatQueryDef 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

[ADezii]

I only have the time to answer the first question right now. I think that you are referring to the Line Continuation Character which enables you to continue long lines of code/statement into multiple lines. This Character consists of the ( _) Space and Underscore. A picture is worth a thousand words in this case. The following code will execute cleanly and I kept it in the same context in which you are working:

Dim strSQL As String
Dim qdf As dao.QueryDef
 
strSQL = "SELECT Employees.EmployeeID, Employees.LastName, Employees.FirstName, " & _
         "Employees.Title, Employees.TitleOfCourtesy, Employees.BirthDate, Employees.HireDate, " & _
         "Employees.Address, Employees.City, Employees.Region, Employees.PostalCode, " & _
         "Employees.Country, Employees.HomePhone, Employees.Extension, Employees.Photo, " & _
         "Employees.Notes, Employees.ReportsTo, Employees.[OLE Field]FROM Employees WHERE " & _
         "Employees.City = 'London';"
 
Set qdf = CurrentDb.CreateQueryDef("YaDaYaDa", strSQL)

[dizzydangler]

Everything works! The form allows the user to select whichever output fields they want, then input criteria in text box/check box controls to filter whichever records they want to see.

The only hiccup I've encountered after hanging the file on our sharepoint portal is that when users open the file as "read only"--which I would prefer that they do, to avoid messing up the data--the drop-down menus on the form don't work. You can click on the arrow button and see the menu selections, but when you select one of the menu items, there's just a "beep" and it doesn't populate the control like it does when opened in "edit" mode. I don't know if this has something to do with my previous question about enabling the macro?

[NeoPa]

If they're bound controls then they cannot work in Read-Only mode (because it's R/O). Are they?

[dizzydangler]

They are. I didn't think you could make an unbound combo box--can you?

BTW, I managed to solve the column-order problem. I realized the columns were populating the table in the order in which I had created them, so I just deleted all the boxes and built them back into the form, with associated macros, in the order that I wanted them to appear in the output table. Took a little while, but I'm happy with the results.

Solved the "enable macro" problem, too--just added a line "click options/enable this content" at the top of the opening form. Not elegant, but it works!

Thanks so much--to all of you--for your help on this project. No way I could have done it without you!

Joe

[NeoPa]

@dizzydangler
Absolutely. They only need to be bound (otherwise don't) if you need to save the selection away in the underlying record (Record Source). This doesn't sound like what you need here. For unbound ComboBoxes, as with any other controls, simply set the Control Source to blank (Delete any contents).
@dizzydangler
I'm sorry I didn't respond to this earlier Joe. Right-click on any control on a form that can be included in the ordering (where the tab key will take you next) and select Tab Order... This will enable you to have full control over what appears where in the list.

[dizzydangler]

This doesn't sound like what you need here. For unbound ComboBoxes, as with any other controls, simply set the Control Source to blank (Delete any contents).

This allowed me to populate the controls with selections from the combo boxes in read-only mode, but when I clicked the "search" button, I got the error message: "Cannot update. Database or object is read-only."

Is there a way to allow the form to execute a search in read-only mode without having to update the database?

Right-click on any control on a form that can be included in the ordering (where the tab key will take you next) and select Tab Order... This will enable you to have full control over what appears where in the list.

I'd already set the tab order, but this did not affect the order of the field columns in the query output table. The only way I was able to control this was by creating the checkboxes in the form in the exact order that I wanted them to appear in the query output table.

[NeoPa]

@dizzydangler
As far as I'm aware, it will work as long as you have no bound controls involved in your filtering. I can only assume that you have something, somewhere, which the system thinks you are trying to change when you make a selection (as that is what the error message is saying).
@dizzydangler
Ah. That makes sense if you're talking about the order in which the code processes the objects in the For ... Each loop. Sorry. I misunderstood the question.

[dizzydangler]

I can only assume that you have something, somewhere, which the system thinks you are trying to change when you make a selection (as that is what the error message is saying).

Must be. But I can live with it as-is. Thanks again for all your help!

Joe

[NeoPa]

Always pleased to help where we can Joe.

It sounds like a good place to stop. Knowing where the problem lies is often the hardest part of solving it. I expect if we knew that, we could proceed relatively easily, but identifying where it is would probably be more work now than the problem deserves.

Good luck :)

BTW I set ADezii's reply as the best answer. I figured that was the one that most got you going on your way.

[Tarheel]

Hi Everybody,

I am working on a very similar situation and came accross this extremely timely thread. I followed all of your posts and created a form that creates a query based only on the fields I select from comboboxes on the form. Thanks to everyone for your hard work and great skills.

I am also attempting to take this one step further and add a WHERE clause base on ADezii's example of WHERE as a string. Just wondering if I could declare multiple "strWhere" (i.e. strWhere1, strWhere2, strWhere3, etc...), build them, and add them to the SQL string like:

" FROM Employees " & strWhere1 & strWhere2 & strWhere3.

Or if I just inlcude all of the Where cases in the single "strWhere"

Or if I should just construct a massive IIF statement like dizzydangler ended up doing.

I do have previous Access knowledge, but am very new to this concept of running a query from a form. I do love the idea of this functionality and really want to finish this project.

Any advice is greatly appreciated.

Thanks Guys!

p.s: As I mentioned, I have comboboxes on my form so the user is guided/limited to their inputs, rather than text boxes.

[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