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
Set filter based on value in text box
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Set filter based on value in text box
Try
Me.Filter = "ProjectManagerID=" & Nz(Me.txtPMID, 0)
Me.Filter = "ProjectManagerID=" & Nz(Me.txtPMID, 0)
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Set filter based on value in text box
Thanks Hans. The null error is gone but it is still not filtering.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Set filter based on value in text box
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
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Set filter based on value in text box
Stripped down file attached. Thanks for looking at this.
You do not have the required permissions to view the files attached to this post.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Set filter based on value in text box
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
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Set filter based on value in text box
FilterOn= True did it. Thanks again for helping with this. Much appreciated Hans.
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Set filter based on value in text box
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.
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.
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Set filter based on value in text box
What is the Row Source of the combo box currently? What are the values of its Column Count and Column Widths?
Best wishes,
Hans
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Set filter based on value in text box
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"
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"
-
- Administrator
- Posts: 78488
- Joined: 16 Jan 2010, 00:14
- Status: Microsoft MVP
- Location: Wageningen, The Netherlands
Re: Set filter based on value in text box
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
Hans
-
- 4StarLounger
- Posts: 402
- Joined: 14 Apr 2010, 15:59
Re: Set filter based on value in text box
Fantastic. That is great to know how to do that. Thank you again Hans. Much appreciated.