473,835 Members | 1,851 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Checkbox form to allow user to select query output fields

33 New Member
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
Expand|Select|Wrap|Line Numbers
  1. SELECT [NameOfTable]![NameOfField1], [NameOfTable]![NameOfField2], etc...
I've tried to build a "dynamic" SELECT statement using the IIF function:
Expand|Select|Wrap|Line Numbers
  1. 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
Nov 10 '09 #1
45 27775
NeoPa
32,584 Recognized Expert Moderator MVP
@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?
Nov 10 '09 #2
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Nov 10 '09 #3
ADezii
8,834 Recognized Expert Expert
I've just helped someone with the exact same problem. I'll return later and Post my approach with the sample code solution.
Nov 10 '09 #4
ADezii
8,834 Recognized Expert Expert
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:
    Expand|Select|Wrap|Line Numbers
    1. Private Sub cmdTest_Click()
    2. On Error Resume Next
    3. Dim ctl As Control
    4. Dim strSQL As String
    5. Dim strSQL_2 As String
    6. Dim qdf As DAO.QueryDef
    7. Dim qdfDemo As DAO.QueryDef
    8. Const conQUERY_NAME As String = "Selective Employee Fields"     'Physical Query Name
    9.  
    10. For Each ctl In Me.Controls                 'Query every Control on the Form
    11.   If ctl.ControlType = acCheckBox Then      'Is it a Check Box?
    12.     If ctl.Value Then                       'Is it selected?
    13.       strSQL = strSQL & ctl.Tag & " ,"      'Build the Field Selection(s)
    14.     End If
    15.   End If
    16. Next
    17.  
    18. If strSQL = "" Then Exit Sub        'No Fields selected, get out!
    19.  
    20. CurrentDb.QueryDefs.Delete conQUERY_NAME        'DELETE existing Query
    21.  
    22. On Error GoTo Err_cmdTest_Click
    23.  
    24. 'Build complete SQL Statement
    25. strSQL_2 = "SELECT " & Left$(strSQL, Len(strSQL) - 2) & " FROM Employees;"
    26.  
    27. 'Create the QueryDef Object
    28. Set qdfDemo = CurrentDb.CreateQueryDef(conQUERY_NAME, strSQL_2)
    29.  
    30. Exit_cmdTest_Click:
    31.   Exit Sub
    32.  
    33. Err_cmdTest_Click:
    34.   MsgBox Err.Description, vbExclamation, "Error in cmdTest_Click()"
    35.   Resume Exit_cmdTest_Click
    36. 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.
Attached Files
File Type: zip Create QueryDef.zip (419.0 KB, 1131 views)
Nov 10 '09 #5
NeoPa
32,584 Recognized Expert Moderator MVP
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.
Nov 10 '09 #6
dizzydangler
33 New Member
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
Nov 11 '09 #7
ADezii
8,834 Recognized Expert Expert
@dizzydangler
Not a problem, Joe. I give you a lot of credit for tackling such a problem.
Nov 11 '09 #8
dizzydangler
33 New Member
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
Nov 11 '09 #9
ADezii
8,834 Recognized Expert Expert
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.
Expand|Select|Wrap|Line Numbers
  1. 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:
Expand|Select|Wrap|Line Numbers
  1. 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:
    Expand|Select|Wrap|Line Numbers
    1. For Each ctl In Me.Controls                                     'Query every Control on the Form
    2.   If ctl.ControlType = acCheckBox And ctl.Tag <> "" Then        'Is it a Check Box with a Tag Value?
    3.     If ctl.Value Then                                           'Is it selected?
    4.       strSQL = strSQL & ctl.Tag & " ,"                          'Build the Field Selection(s)
    5.     End If
    6.   End If
    7. Next
  4. Other than cosmetic, and the incorporation of Criteria, there are no other changes to the code that will be required.
Nov 11 '09 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

4
4637
by: Jay | last post by:
Hi everybody ! I am currently writing a webpage that displays a list of records ( each record has a checkbox associated with it) to allow users to select any record they want to delete (much like in "hotmail" or "yahoo" e-mail where u can select particular message to delete) ? Anybody have any idea how to do it ? And one more question, I like to write my page so that when the user
4
6177
by: Shufen | last post by:
Hi, I'm a newbie that just started to learn python, html and etc. I have some questions to ask and hope that someone can help me on. I'm trying to code a python script (with HTML) to get values from a html form that consists of about 10 checkbox and a textbox where user have to key in a value to perform a search. From python tutors, I learned that I have to use the following method:
6
1955
by: Megan | last post by:
Hi everybody- I'm trying to use a checkbox to control whether or not a date field in a query "Is Null" or "Is Not Null." I have 2 date fields: InDate and OutDate. If there is an OutDate, then the record is finished. If there is no OutDate, then the record is not finished. I'm using an unbound form (frmStatus) to enter data into a query to
4
7025
by: Dave Boyd | last post by:
Hi, I have two very similar forms each with a subform. The main form gets a few fields from the user and passes this back to a query that the subform is bound to. The requery is done when the user enters the last qualifying field on the main form. In one case this works fine, the subform shows the data the user wants to update -- which means showing all the data put in previously (ie showing this via the requery and the continuous...
3
11847
by: elyob | last post by:
Okay, I'm about to add some checkboxes to a form, and then store the data in a column on a MySQL database. Just wanted to know best practice, at the moment I'm writing and thinking .. God that's ungly code ... <input type="checkbox" name="delta" <?php if ($delta==1) { echo "checked"; }?>> Delta<br> <input type="checkbox" name="mastercard" <?php if ($mastercard==1) { echo "checked"; }?>> Mastercard<br> <input type="checkbox" name="solo"...
8
4470
by: Sid | last post by:
I hope someone could help me with this. I am trying to setup a criteria to decide when to allow/not allow user to click on the check box. logically it looks simple but I am not able to incorporate in my data access page. I have a numerical field called tier status and right next to it I have a checkbox. I would like to allow user to check and uncheck the checkbox as long as the tier
0
4106
by: cyberdawg999 | last post by:
Greetings all in ASP land I have overcome one obstacle that took me 2 weeks to overcome and I did it!!!!! I am so elated!! thank you to all who invested their time and energy towards helping me with my problems. Now for my new little problem,I had a problem posting the values from checkbox fields to a database and thats the obstacle I overcame. Now the second part is my new problem is that I want that the next time that page loads for...
11
4318
by: =?Utf-8?B?UGFyYWcgR2Fpa3dhZA==?= | last post by:
Hi All, I have a large recordset to be displayed on a ASP 3.0 page. I am using recordset paging for this. For the next and previous link i am passing href as <a href=<Page URl>?page=<%= iPageCurrent - 1 %for Previous. Now when i display the contents i also add a checkbox (for deletion) to each of the records. Now user should be able to select one or more checkboxes across pages and then should be allow to delete all selections together....
3
1636
scubak1w1
by: scubak1w1 | last post by:
Hello, I posted this in the HTML forum, but it was suggested I post it over here as well by a moderator. I have a form that will not 'fire' in non-IE browsers?? I have poked and poked at the code and can not find why. The AJAX changes the query button in non-IE browsers, at least in the sense it is not opaque anymore, the 'title' check changes, etc - it just doesn't "fire" when you click the icon/image in Mozilla, etc but DOES in Internet...
0
9652
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
10808
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
1
10560
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10233
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
1
7766
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...
0
5804
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4433
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
2
3993
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
3
3088
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.