write a code to filter data(using combobox in form)from a subform in Access

Question

Ok this is my scenario: I have a form with a subform, in msAccess2013 this subform has a table and I want to filter the rows using a list in a combobox, so that the subform presents me the records depending of the selection of that combo box, The combo box data type is meant to be Date, it is also a cascade combobox (it will show data regardless of another combobox) so at the end I will filter the records that only has that specific date in the subform table.

OK this is the code that I have:

    Private Sub cbSelectDate_AfterUpdate()
    Dim AT As Date
    AT = "select * from subform where ([AppointDate] = # & Me.cbSelectDate &                 "#')"
    Me.subform.Form.RecordSource = AT

    Me.subform.Form.Requery

    End Sub

vb doesn't completely accept my code It says "Compile error: Expected: end of statement" Please Help??

I suspect that this code is VBA and not VB.Net. If that is the case, you should edit your question to replace the vb.net tag with access-vba. — Blackwood

Anthony Griggs Anthony Griggs · Accepted Answer · 2016-06-17T21:21:27

Your setting your subforms recordsource as a Date... This isn't going to work. There are a few different approaches you could use. SQL like in your example is one of them. But in this case I would probably just apply a filter on your subforms recordsource. This is untested but it should be close to what you want. The only thing I can't remember is if Pounds(#) are used in Form filters or not ... I know they are sued in Access SQL and Expressions but form filters are different sometimes.

Private Sub cbSelectDate_AfterUpdate()
    Dim strFilter As String
    Dim rst As DAO.Recordset

    ' Clone The Current Subform's Recordset
    Set rst = Me.SubForm.Form.RecordsetClone

    ' Get the Current Filter If One Has Already Been Applied to the Subform
    If Me.SubForm.Form.FilterOn Then strFilter = Me.SubForm.Form.Filter

    If Not rst.EOF Then
        If strFilter > "" Then
            strFilter = strFilter & " AND [AppointDate] = #" & Me.cbSelectDate & "#"
        Else
            strFilter = "[AppointDate] = #" & Me.cbSelectDate & "#"
        End If
        Me.SubForm.Form.Filter = strFilter
        Me.SubForm.Form.FilterOn = True
    End If
    Set rst = Nothing
    Me.SubForm.Form.Requery
End Sub

write a code to filter data(using combobox in form)from a subform in Access

3 Answers