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
– Blackwood, Commented May 17, 2016 at 18:55

Anthony Griggs · Accepted Answer · 2016-06-17 22:03:04Z

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

lordkian · Accepted Answer · 2016-05-17 18:54:06Z

0

its because of third line the " ends string and ' #')" ' will be out of string so put "" instead of " AT = "select * from subform where ([AppointDate] = # & Me.cbSelectDate & ""#')"

answered May 17, 2016 at 18:54

lordkian

1057 bronze badges

1 Comment

Chris Over a year ago

closer, but the line now gives me a type mismatch, something is misinterpreted as string maybe, hmmm...

Colin · Accepted Answer · 2016-05-17 20:23:16Z

0

Me.cbSelectDate is a part of the string within the code, try something like:

"select * from ..." & Me.cbSelectDate & "...end stuff"

answered May 17, 2016 at 20:23

Colin

1688 bronze badges

1 Comment

Chris Over a year ago

I tried this line : AT = "select * from subform where ([AppointDate] = #" & Me.cbSelectDate & "#)" but the mismatch continues appearing

Collectives™ on Stack Overflow

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

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related