Search code examples
vbams-accesssubformopenform

Opening form with subforms with whereCondition

I have a form with values dateFrom and dateTo. On click of button i want to open a new form which contains two subforms. One of the subforms shows filtered records. It shows the records that have note_date in range between dateFrom and dateTo.

note_date is one of the columns in query which is Record Source in the filtered subform

So what doesnt work is the filter for one of the subforms.

Here is my code how i thought it would work

Overview_of_vacation_notes is the name of the form that contains two subforms

dtmFrom = Text56.Value 'start date
dtmTo = Text58.Value   'end date
Dim strCriteria As String

strCriteria = "[note_date] >= #" & Format(dtmFrom, "yyyy-mm-dd") & "# AND [note_date] <= #" & Format(dtmTo, "yyyy-mm-dd") & "#"
    
DoCmd.OpenForm "Overview_of_vacation_notes", whereCondition:=strCriteria

Is there a way i could do something like this?

DoCmd.OpenForm "Overview_of_vacation_notes", subformName.whereCondition:=strCriteria

Because my code doesn't work cause of the whereCondition using the strCriteria on the main form and not the subform

Solution

  • DoCmd.OpenForm "Overview_of_vacation_notes" is opening parent form. So, you can not apply where condition to subform to this line. You need to set filter criteria to subform and filter that subform to show filtered data. Try below codes.

    dtmFrom = Text56.Value 'start date
dtmTo = Text58.Value   'end date
Dim strCriteria As String

strCriteria = "[note_date] >= #" & Format(dtmFrom, "yyyy-mm-dd") & "# AND [note_date] <= #" & Format(dtmTo, "yyyy-mm-dd") & "#"
    
DoCmd.OpenForm "Overview_of_vacation_notes"
Forms![Overview_of_vacation_notes]![YourSubform].Form.FilterOn = False 'Clear previous filter.
Forms![Overview_of_vacation_notes]![YourSubform].Form.Filter = strCriteria 'Set filter criteria
Forms![Overview_of_vacation_notes]![YourSubform].Form.FilterOn = True 'Apply filter.