conditional Query for Rowsource

Question

I currently have a rowsource, which I want to enhance as per the desired code. How do I make it work. If the combo is checked in the form, then I want the desired rowsource, else the existing one (which is working fine)

Current Row source query

SELECT Tbl_BatchData.form_num AS Expr1

FROM Tbl_BatchData

WHERE ((([Forms]![Brwse_Edit_Frm]![cmb_BatchNo])=[Batch_Num]));

Desired rowsource query

if [forms]![Brwse_Edit_Frm]![chk_FlagFilter]=-1

select Tbl_BatchData.form_num AS Expr1

FROM Tbl_BatchData

WHERE ([Forms]![Brwse_Edit_Frm]![cmb_BatchNo])=[Batch_Num] and

[Forms]![Brwse_Edit_Frm]![cmb_Flag]=[flag]

else

SELECT Tbl_BatchData.form_num AS Expr1

FROM Tbl_BatchData

WHERE ((([Forms]![Brwse_Edit_Frm]![cmb_BatchNo])=[Batch_Num]))

Answer 1

You need code in two places: in the After Update event of the check box, so that the row source gets changed when the user clicks the check box, and in the On Current event of the form, so that the row source is kept up-to-date as the user moves from record to record.

Private Sub chk_FlagFilter_AfterUpdate()

    Call UpdateRowSource

End Sub

Private Sub Form_Current()

    Call UpdateRowSource

End Sub

Private Sub UpdateRowSource()

    If Me.chk.FlagFilter = True Then

        Me.NameOfCombo.RowSource = "SELECT form_num FROM Tbl_BatchData " & _

            "WHERE ([Forms]![Brwse_Edit_Frm]![cmb_BatchNo])=[Batch_Num] " & _

            " AND [Forms]![Brwse_Edit_Frm]![cmb_Flag]=[flag]"

    Else

        Me.NameOfCombo.RowSource = "SELECT form_num FROM Tbl_BatchData " & _

            "WHERE [Forms]![Brwse_Edit_Frm]![cmb_BatchNo]=[Batch_Num]"

    End If

End Sub

where NameOfCombo is the name of the combo box whose row source you want to change.

Answer 2

Thank you so much. Sorry for the late reply... I had to come down to visit family....

best regards,

Anuj

Answer 3

Sorry, it is a check box. If the check box(chk_FlagFilter) is checked, it should then query the value in the combo as well (cmb_Flag)

Answer 4

What do you mean by "If the combo is checked in the form"? Which combo box, and what does "checked" mean for a combo box?