VBA Check Box

dluton · #1 02-09-2010, 06:52 AM

Hi,
I work for an educational charity and we have had a VBA Database designed for us to store our data.
One of the capabilities of the DB is exporting data using various functions and commands. However, some of the coding for one of the check boxes in the database is incorrect. Currently, the check box references a prebuilt query (SchoolRecordProgrammes) looking up a child's dropout status in SchoolDroppedOut column. So if there is a value in the column the box is checked if not it remains unchecked. However, what it should do is check the value, if it is not equal to 1 or 4 then check the box, otherwise don't... Can this be done? Below is the code that is relevant for the check box, specifically the IIF command:

strSQL = strSQL & _
"WHERE qrySchoolRecordProgrammes.SchoolIDX IN (" & trSelectedSchools & ")" & _
" AND qrySchoolRecordProgrammes.FormIDX IN (" & strSelectedForms ")" & _
" AND qrySchoolRecordProgrammes.SchoolYear IN (" & strSelectedSchoolYears & ")" & _
" AND qrySchoolRecordProgrammes.EventIDX IN (" & strSelectedEP & ")" & _
" AND " & IIf(chkDroppedOut, "", "NOT ") & _
" ( qrySchoolRecordProgrammes.SchoolDroppedOut = 3 OR " & _
" qrySchoolRecordProgrammes.SchoolDroppedOut = 255 ) " & _

Kafrin · #2 03-01-2010, 07:04 AM

Yes it's possible, but I'm struggling with the info you've provided to give you a solution. I think you want somthing like:

strSQL = strSQL & _
"WHERE qrySchoolRecordProgrammes.SchoolIDX IN (" & trSelectedSchools & ")" & _
" AND qrySchoolRecordProgrammes.FormIDX IN (" & strSelectedForms ")" & _
" AND qrySchoolRecordProgrammes.SchoolYear IN (" & strSelectedSchoolYears & ")" & _
" AND qrySchoolRecordProgrammes.EventIDX IN (" & strSelectedEP & ")" & _
" AND " & IIf(chkDroppedOut, "", "NOT ") & _
" ( qrySchoolRecordProgrammes.SchoolDroppedOut = 1 OR " & _
" qrySchoolRecordProgrammes.SchoolDroppedOut = 4 ) " & _

...but since I don't know what the 3 and 255 refer to exactly it's difficultt o be sure - you may well need to add the 255 line back in at least:

strSQL = strSQL & _
"WHERE qrySchoolRecordProgrammes.SchoolIDX IN (" & trSelectedSchools & ")" & _
" AND qrySchoolRecordProgrammes.FormIDX IN (" & strSelectedForms ")" & _
" AND qrySchoolRecordProgrammes.SchoolYear IN (" & strSelectedSchoolYears & ")" & _
" AND qrySchoolRecordProgrammes.EventIDX IN (" & strSelectedEP & ")" & _
" AND " & IIf(chkDroppedOut, "", "NOT ") & _
" ( qrySchoolRecordProgrammes.SchoolDroppedOut = 1 OR " & _
" qrySchoolRecordProgrammes.SchoolDroppedOut = 4 OR " & _
" qrySchoolRecordProgrammes.SchoolDroppedOut = 255 ) " & _

I'm also wary as the string clearly continues after what you've shown and without knowing what comes next it's difficult to be sure this is right.

Welcome to the Excel Forum

Forum Statistics