Set filter based on value in text box

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Set filter based on value in text box

Post by scottb »

I have a form frmProjects that has a text box (txtPMID) with the default value set to the current user’s ResourceID (numerical )
I am trying to create a command button on frmProjects to set a filter for a combo box (cmbProjectManager, control source ProjectManagerID) = txtPMID. The goal is to have a button to filter the records on the form to the value in cmbProjectManager that matches the value in txtPMID. I hope that makes sense.

I have tried Me.Filter = [cmbProjectManager] = Forms!frmProjects!txtPMID but nothing happens unless cmbProjectManager is blank then I get
“Run time error 94 – Invalid use of null”. Ideally I would like the filter to work whether the current record was null or not. Again filtering records on the form to the value of txtPMID.
Thank you for any help.
-S

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Set filter based on value in text box

Post by HansV »

Try

Me.Filter = "ProjectManagerID=" & Nz(Me.txtPMID, 0)
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Set filter based on value in text box

Post by scottb »

Thanks Hans. The null error is gone but it is still not filtering.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Set filter based on value in text box

Post by HansV »

Could you create a stripped-down copy of the database, without sensitive information, zip the copy and attach it to a reply?
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Set filter based on value in text box

Post by scottb »

Stripped down file attached. Thanks for looking at this.
You do not have the required permissions to view the files attached to this post.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Set filter based on value in text box

Post by HansV »

I fear that you stripped the database a bit too much - there is no field ProjectManagerID in tblProjects. But I think I know the problem: after setting the Filter property of the form, you must activate the filter by setting the FilterOn property to True:

Code: Select all

Private Sub Command189_Click()
    Me.Filter = "ProjectManagerID=" & Nz(Me.txtPMID, 0)
    Me.FilterOn = True
End Sub
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Set filter based on value in text box

Post by scottb »

FilterOn= True did it. Thanks again for helping with this. Much appreciated Hans.

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Set filter based on value in text box

Post by scottb »

The filter has been very helpful. I wanted to know if within the same logic I can filter a combo box on the same form.
I have a combo box (cmbSearchProjects) on the same form. It currently displays all active projects.
Would it be possible to also filter cmbSearchProjects to show just the records for txtPMID when user users activates the filter? Part of the data source for cmbSearchProjects is ProjectManagerID.
I would rather not make cmbSearchProjects conditional on another combo box, just filter to the PM if the user wants. Can this be done with VB or do I have to approach it through query/requery?
Thank you.

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Set filter based on value in text box

Post by HansV »

What is the Row Source of the combo box currently? What are the values of its Column Count and Column Widths?
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Set filter based on value in text box

Post by scottb »

The row source is:
SELECT tblProjects.ProjectID, tblProjects.ProjectName, tblProjects.ProjectActive, tblProjects.ProjectManagerID FROM tblProjects WHERE (((tblProjects.ProjectActive)=True)) ORDER BY tblProjects.ProjectName;
Column count is 2. Column widths = 0";4"

User avatar
HansV
Administrator
Posts: 78235
Joined: 16 Jan 2010, 00:14
Status: Microsoft MVP
Location: Wageningen, The Netherlands

Re: Set filter based on value in text box

Post by HansV »

You can change the row source from a command button cmdFilterCombo:

Code: Select all

Private Sub cmdFilterCombo_Click()
    If IsNull(Me.txtPMID) Then
        Me.cmbSearchProjects.RowSource = "SELECT ProjectID, ProjectName FROM tblProjects WHERE " & _
            "ProjectActive=True ORDER BY ProjectName"
    Else
        Me.cmbSearchProjects.RowSource = "SELECT ProjectID, ProjectName FROM tblProjects WHERE " & _
            "ProjectActive=True AND ProjectManagerID=" & Me.txtPMID & " ORDER BY ProjectName"
    End If
End Sub
Best wishes,
Hans

scottb
4StarLounger
Posts: 402
Joined: 14 Apr 2010, 15:59

Re: Set filter based on value in text box

Post by scottb »

Fantastic. That is great to know how to do that. Thank you again Hans. Much appreciated.