Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!
  • Students Click Here

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here


acCmdFilterBySelection With Main And Subforms

acCmdFilterBySelection With Main And Subforms

acCmdFilterBySelection With Main And Subforms

Hi there,

I have a main form linked to a subform by a Unique ID field.

I have a command button on the main form that uses "acCmdFilterBySelection" to filter by the selected field.

When this button is clicked I'd like the subform to be filtered by this same field.

Does anyone know how I can do this with VBA?


RE: acCmdFilterBySelection With Main And Subforms

Perhaps something like:


Dim ctl As Control

    Set ctl = Screen.PreviousControl
    DoCmd.RunCommand acCmdFilterBySelection
    Select Case ctl.Value
    Case IsDate(ctl.Value)
        Me.[Subform Control Name].Form.Filter = ctl.Name & "=#" & ctl.Value & "#"
    Case IsNumeric(ctl.Value)
        Me.[Subform Control Name].Form.Filter = ctl.Name & "=" & ctl.Value
    Case Else
        Me.[Subform Control Name].Form.Filter = ctl.Name & "='" & ctl.Value & "'"
    End Select
    Me.[Subform Control Name].Form.FilterOn = True

RE: acCmdFilterBySelection With Main And Subforms

Hi there,

Thanks for you response, it's great and has me on my way works, but I have one further wish.

The code works if FilterBySelection is used once...if I want to further filter the mainform using the FilterBySelection button a second time, the subform recordset resets, then filters by the last FilterBySelection field rather than both.

Do you know how the code can be adapted to fit in with this? - I've tried fiddling but haven't got anywhere.

Thanks for your help.

RE: acCmdFilterBySelection With Main And Subforms

Use And:
strCurrentFilter=Me.[Subform Control Name].Form.Filter
Me.[Subform Control Name].Form.Filter = strCurrentFilter & " AND " & ctl.Name ...
You will probably need a clear or show all too.

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close