Checkboxes

[bigbadbess24]

I have 4 check boxes represent 4 grade levels. When I click on a grade I have a query run for me a list of all 9th graders or 10th graders or both together.
When I run the form for the first time, all four boxes are check, when I unclick all of them and I click which ones I want the form does not work. But if I leave them click the report will work. Basically what is happening is when I unclick them, the lose their value.
I have the check box set up with a Default Value as "09" or "10" base on what Grade Level there is.
So my two questions are, how do I keep the value of the check boxes after I unclick and click again and Secondly, is there a way to have the boxes unchecked when I run the program?

 

[Uncle Gizmo]

Default value is misleading.

The problem is the default value of the check box is misleading you. This default value will only appear when the check box is initially loaded. Once you make a selection of either checking or un-checking the check box, then the value changes to either -1 to 0. These values represent yes or no, true or false.

You can demonstrate this quite easily yourself, set up a new form and locate a check box on the form and a text box. Set the default value for the check box to 10

Make an afterupdate event for your check box:

Private Sub ChkBox_AfterUpdate()
txtBox = chkBox.Value
End Sub

and add this form load event as is,

Private Sub Form_Load()
txtBox = chkBox.Value
End Sub

where chkbox is the name of your check box and txtbox is the name of your text box.

 

[bigbadbess24]

I understand what you are saying but why do I need a text box?

 

[bigbadbess24]

Ok I see what you mean when I have the text box, and it switches between 1and 0. But how do I get it to work? It showed me how it goes between -1 and 0

 

[Uncle Gizmo]

It should also show the default value of the control when the form loads up.

The Nextstep is to use the values -1 and zero which represent yes or no, true or false. It will probably be necessary to use an "If then" statement to change these values so that they can be incorporated into your query. So it might be a good idea if you posted your query!

You can do this by viewing the query in SQL view, there you will see the text which identifies the query. Post that text.

 

[bigbadbess24]

Here is SQL of the query

SELECT [tbl STUDENT DATA - ACTIVE].SchoolDistrict, [tbl STUDENT DATA - ACTIVE].Grade, [tbl STUDENT DATA - ACTIVE].First, [tbl STUDENT DATA - ACTIVE].Last
FROM [tbl STUDENT DATA - ACTIVE]
WHERE ((([tbl STUDENT DATA - ACTIVE].SchoolDistrict)=[Forms]![frm Grade/District Chkbox]![cboDistrict]) AND (([tbl STUDENT DATA - ACTIVE].Grade)=[Forms]![frm Grade/District Chkbox]![chk9] Or ([tbl STUDENT DATA - ACTIVE].Grade)=[Forms]![frm Grade/District Chkbox]![chks10] Or ([tbl STUDENT DATA - ACTIVE].Grade)=[Forms]![frm Grade/District Chkbox]![chk11] Or ([tbl STUDENT DATA - ACTIVE].Grade)=[Forms]![frm Grade/District Chkbox]![Chk12]));

 

[Uncle Gizmo]

I think there may be a problem!

I believe that this statement and the other similar statements:

tbl STUDENT DATA - ACTIVE].Grade)=[Forms]![frm Grade/District Chkbox]![chk9]

will expect a value, so if you don't provide a value passed to it by "chk9" it will not return any records.

It would be reasonable to expected that if you did not pass a value that it would return all the results however it does not work this way unfortunately. if you don't pass it a value, it will be looking for records without a value.

 

[Uncle Gizmo]

Multi select list box

Looking back through your questions, I think that John's solution may have been a better bet, although I have never tried this method myself I think it will work.
I personally prefer to build the SQL string with a case statement. This has the disadvantage that the SQL code has to be compiled every time, whereas John's idea may avoid this. Interesting!

 

[bigbadbess24]

It may be a good idea but its not the look they are looking for. I am actually not going to use checkboxes but instead radio buttons but I know they are one of the same. Thanks for your help anyway. I will try to fumble around with IF statements and see what I can come up with.

 

[Rod D]

This SQL might be your solution

Hi Bess

I have had a look at what I think you want to do and have come up with the following SQL statement...

SELECT [tbl STUDENT DATA - ACTIVE].SchoolDistrict, [tbl STUDENT DATA - ACTIVE].Grade, [tbl STUDENT DATA - ACTIVE].First, [tbl STUDENT DATA - ACTIVE].Last
FROM [tbl STUDENT DATA - ACTIVE]
WHERE 
((([tbl STUDENT DATA - ACTIVE].SchoolDistrict)=[Forms]![frm Grade/District Chkbox]![cboDistrict]) AND (([tbl STUDENT DATA - ACTIVE].Grade)="09") AND (([Forms]![frm Grade/District Chkbox]![chk9])=-1)) OR 
((([tbl STUDENT DATA - ACTIVE].SchoolDistrict)=[Forms]![frm Grade/District Chkbox]![cboDistrict]) AND (([tbl STUDENT DATA - ACTIVE].Grade)="10") AND (([Forms]![frm Grade/District Chkbox]![chks10])=-1)) OR 
((([tbl STUDENT DATA - ACTIVE].SchoolDistrict)=[Forms]![frm Grade/District Chkbox]![cboDistrict]) AND (([tbl STUDENT DATA - ACTIVE].Grade)="11") AND (([Forms]![frm Grade/District Chkbox]![chk11])=-1)) OR 
((([tbl STUDENT DATA - ACTIVE].SchoolDistrict)=[Forms]![frm Grade/District Chkbox]![cboDistrict]) AND (([tbl STUDENT DATA - ACTIVE].Grade)="12") AND (([Forms]![frm Grade/District Chkbox]![Chk12])=-1))
ORDER BY [tbl STUDENT DATA - ACTIVE].Grade;

What this does is look at whether the check-boxes have been set (Value of -1) for each grade year (I'm assuming years 9 - 12), being in the UK, the US grades are lost on me

I did notice that the years are stored as strings and that in a sample db you posted a while back, your 9th Grade is stored as "09". This makes sorting the list work, but maybe this should be changed to numeric (byte) with upper and lower limits of 9 and 12. - Just a thought.

I did put in extra Cr/Lf into the statement after the WHERE for readability sakes and these should probably be removed when you copy it into your query/code.

For completeness, I have added screen dumps of the query builder for you.

To get the check-boxes / radio buttons to appear unchecked when the form opens, set their default values to 0.

Hope this helps as the thought of putting in IF statements was too much to bear thinking about...

Regards
Rod

 

[bigbadbess24]

Amazing

Wow! That worked. I never really thought about setting up that way. I knew that my default value was wrong. Once again thanks for your help on another problem!