samedi 7 mai 2016

Access survey filter subquestions based on checkbox answers to previous parent questions

I need assistance with an issue filtering a continuous form immediately when a box is checked for a record in the continuous form. I have a survey questionnaire in Access 2013 with parent questions and subquestions with checkboxes to answer yes to specific questions. Each question is a single record on a continuous form. The form filters out subquestions unless the parent question is checked. This is done using a Dlookup function to find if the checkbox for a particular question ID is checked and then it re-filters the form to show the original questions as well as the subquestions.

The form filters the correct information but I'm unable to get it to filter immediately when the checkbox is checked. It filters correctly in the On Load event. I also applied the filter to the On click even of the checkbox. I think there is a problem using On Click because Access doesn't recognize that the query's checkbox value is checked until after I click another control on the form so it doesn't immediately filter when checking the box but it does when I click the next checkbox in line. This is confusing to the end-user because questions will appear above the question they've already answered. I've tried copying the filter VBA code or a requery function into the On Click, On Mouse Up, On Mouse Down, On Current, and After Update events and none of them work. The only option that works is the On Paint event but then the form flickers constantly because it's constantly filtering it.

Does anyone know how to get it to filter immediately when a box is checked?

Here's my filter code:

    Dim strA As String
    Dim strB As String
    Dim strC As String
    Dim strD As String

    strA = "[QuestionNo] Like 'EM?'"
    strB = "[QuestionNo] Like 'EM*' AND [chkAnswer]=True"

    If DLookup("chkAnswer", "qryQuestions", "[QuestionNo] = 'EM1'") = True Then
    strC = "[QuestionNo] Like 'EM1?'"
    Else
    strC = "[QuestionNo] Like 'EM?'"
    End If

    If DLookup("chkAnswer", "qryQuestions", "[QuestionNo] = 'EM1a'") = True Then
    strD = "[QuestionNo] Like 'EM1a*'"
    Else
    strD = "[QuestionNo] Like 'EM?'"
    End If

    Me.Filter = strA & " OR " & strB & " OR " & strC & " OR " & strD
    Me.FilterOn = True
    Me.Requery

Thanks in advance!




Aucun commentaire:

Enregistrer un commentaire