Filtering SubForm

  • (2 Pages)
  • +
  • 1
  • 2

16 Replies - 1393 Views - Last Post: 24 March 2016 - 10:49 AM

#1 RustyAMcM  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 192
  • Joined: 21-January 15

Filtering SubForm

Posted 22 March 2016 - 01:34 PM

I have a problem I have 1 form names frmRevNew that has a few fields including a dropdown which holds family names for parts this form also has a sub form named frmRevSub that I am trying to filter by the drop down. The following code is what I thought would work I have tried a few different ways but cannot get this working any ideas?

Me.frmRevSub.Form.Filter = "[Family]= " & Combo42 & Forms!frmRevNew.ControlName & Combo42

This post has been edited by RustyAMcM: 22 March 2016 - 01:34 PM


Is This A Good Question/Topic? 0
  • +

Replies To: Filtering SubForm

#2 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: Filtering SubForm

Posted 22 March 2016 - 05:24 PM

Text values need to be surrounded by single quotes, so you are looking for [Family] = 'Smith' with the value Smith being read from the combobox.

You should rename your combobox to something meaningful.
Was This Post Helpful? 0
  • +
  • -

#3 RustyAMcM  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 192
  • Joined: 21-January 15

Re: Filtering SubForm

Posted 23 March 2016 - 05:16 AM

andrewsw,

so your saying I need to add '' after the "[Family] = " like this ?

Me.frmRevSub.Form.Filter = "[Family]= " & '' & Forms!frmRevNew.ControlName & Combo42 



if this is not correct please give me an example.

I have no idea what is going to be selected it could be 1 of about 700 different possabilities I just need the value after selection passed to the sub form...
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: Filtering SubForm

Posted 23 March 2016 - 05:42 AM

That isn't near. That isn't surrounding with single quotes and you don't need ControlName.

You need to study basic string concatenation, it is fundamental to all programming.

Something like, "[Family] = '" & Forms!frmName.cboName & "'"
Was This Post Helpful? 0
  • +
  • -

#5 RustyAMcM  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 192
  • Joined: 21-January 15

Re: Filtering SubForm

Posted 23 March 2016 - 05:42 AM

andrewsw,

I finally got the right value to the code with the following however it is still not filtering the sub form any ideas?

Me.frmRevSub.Form.Filter = "[Family]= " & "'" & Combo42.Text & "'"

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: Filtering SubForm

Posted 23 March 2016 - 05:44 AM

You can omit .Text, it defaults to the Value property.

What is this Value? If there is a hidden id column then the filter will be based on this number, rather than the displayed text.
Was This Post Helpful? 0
  • +
  • -

#7 RustyAMcM  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 192
  • Joined: 21-January 15

Re: Filtering SubForm

Posted 23 March 2016 - 06:02 AM

There is a hidden ID field and that is what I pick up with out the .text however the displayed text is what I need to filter on so I am having to call the .text to get this value.
Was This Post Helpful? 0
  • +
  • -

#8 RustyAMcM  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 192
  • Joined: 21-January 15

Re: Filtering SubForm

Posted 23 March 2016 - 11:59 AM

not sure what is wrong here everything I find when researching this says I am doing this correctly however it is not working if anyone could shed light on this it would be greatly appreciated
Was This Post Helpful? 0
  • +
  • -

#9 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: Filtering SubForm

Posted 23 March 2016 - 12:17 PM

If you need the value from the second column you would use the Column Property =Forms!frmName!cboName.Column(1).

(If you have an ID though, the filtering would normally be based on the ID.)
Was This Post Helpful? 0
  • +
  • -

#10 RustyAMcM  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 192
  • Joined: 21-January 15

Re: Filtering SubForm

Posted 23 March 2016 - 01:13 PM

andrewsw I will give that a try and see what happens...
Was This Post Helpful? 0
  • +
  • -

#11 RustyAMcM  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 192
  • Joined: 21-January 15

Re: Filtering SubForm

Posted 23 March 2016 - 01:32 PM

ok still not working I am posting a link to a pic of the form here: Posted Image

The red is where the selection takes place the blue is what needs to be filtered red is on main form blue is on sub form I have no idea why this is not working I am not even getting an error message it just does nothing...
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: Filtering SubForm

Posted 23 March 2016 - 01:40 PM

On what event are you filtering the records? Put a breakpoint in there, or a MsgBox, to check that it runs.

Your Save button suggests that you are trying to use the combobox as both a filtering feature, and to display the id of the current record. You cannot do both with the same control. You need to clearly separate the two uses.
Was This Post Helpful? 0
  • +
  • -

#13 RustyAMcM  Icon User is offline

  • D.I.C Head

Reputation: 3
  • View blog
  • Posts: 192
  • Joined: 21-January 15

Re: Filtering SubForm

Posted 23 March 2016 - 01:58 PM

the filtering happens on the change event of the combo box the save button does nothing atm however will be used to save a new revision I just placed it there so I don't forget to use it...
Was This Post Helpful? 0
  • +
  • -

#14 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: Filtering SubForm

Posted 23 March 2016 - 02:37 PM

If you are certain the event is running then according to AllenBrowne you may need to change the RecordSource of the main form. (Try adding FilterOn = True first, following the first example at the link.)

I thought simple filtering would work (as you have attempted) but maybe I was thinking about filtering on the main form, not a subform. It's been a while..

Allen Browne's site is an excellent reference for Access.
Was This Post Helpful? 0
  • +
  • -

#15 andrewsw  Icon User is online

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,889
  • Joined: 12-December 12

Re: Filtering SubForm

Posted 23 March 2016 - 02:53 PM

I've just experimented with this and it works with the single quotes removed (because I'm dealing with a number) and following the Filter with FilterOn = True. I also use AfterUpdate rather than Change.
Private Sub cboStaffMember_AfterUpdate()
    Me.sbfStaff.Form.Filter = "[StaffID] = " & Me.cboStaffMember
    Me.sbfStaff.Form.FilterOn = True
End Sub


It works equally well using the Column Property,
Private Sub cboStaffMember_AfterUpdate()
    Me.sbfStaff.Form.Filter = "[StaffID] = " & Me.cboStaffMember.Column(0)
    Me.sbfStaff.Form.FilterOn = True
End Sub

Was This Post Helpful? 0
  • +
  • -

  • (2 Pages)
  • +
  • 1
  • 2