Go Back   Access World Forums > Microsoft Access Discussion > Forms

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 05-24-2009, 04:15 AM   #1
shiwawa
Registered User
 
Join Date: May 2009
Posts: 1
Thanks: 0
Thanked 0 Times in 0 Posts
shiwawa is on a distinguished road
Main Form ComboBox to Filter Records in SubForm

I have a problem filtering records in my subform using a ComboBox in the Main Form. I have tried options proferred in other forums and have still not git the desired result. I know I must be missing something as this looks quite simple.

I have attached a screenshot to help.

What I need to for the records to be filtered by the Section Field using either of the three options in the ComboBox. The ComboBox looksup data from a Sections table (SectionID, Section).

Main Table Name: Orders (Master Link: Order ID)
SubForm Table Name: Order Details (Child Link: Order ID)
ComboBox Object Name: CboFilter

I will appreciate any help.
Attached Images
File Type: jpg ScreenShot.jpg (79.9 KB, 727 views)

shiwawa is offline   Reply With Quote
Old 05-25-2009, 04:25 AM   #2
DevastatioN
Newly Registered User
 
Join Date: Nov 2007
Location: Nova Scotia, Canada
Posts: 242
Thanks: 0
Thanked 7 Times in 6 Posts
DevastatioN is on a distinguished road
Re: Main Form ComboBox to Filter Records in SubForm

Hello,

On the afterupdate event of your combobox you need the following:

[Forms]![frmMain]![frmSub].Form.Filter = "[Field] = '" & Me.ComboBox & "'"
[Forms]![frmMain]![frmSub].Form.FilterOn = True

This will set the filter property of the subform.
__________________
Degree: Bachelor of Applied Arts in Information Technology
Database Analyst
Department of National Defense (Canada)
DevastatioN is offline   Reply With Quote
The Following User Says Thank You to DevastatioN For This Useful Post:
pbuethe (10-15-2018)
Old 09-10-2019, 12:51 PM   #3
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 9
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Main Form ComboBox to Filter Records in SubForm

Quote:
Originally Posted by DevastatioN View Post
Hello,

On the afterupdate event of your combobox you need the following:

[Forms]![frmMain]![frmSub].Form.Filter = "[Field] = '" & Me.ComboBox & "'"
[Forms]![frmMain]![frmSub].Form.FilterOn = True

This will set the filter property of the subform.
DevastatioN,

I am trying to do the same thing.

I put: [Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeName] = '" & Me.cboEmployee & "'"
[Forms]![frm_YearCalendar]![subFormTotalDays].Form.FilterOn = True

After I select an option from the combobox it filters the subform but doesn't show any results. Am I missing something?

Mr. Southern is offline   Reply With Quote
Old 09-10-2019, 01:04 PM   #4
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Main Form ComboBox to Filter Records in SubForm

Make sure the bound column of the combo is the name rather than an ID field. If it's the ID field, use that for your filter instead of the name.
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Hank.School (10-02-2019)
Old 09-10-2019, 01:28 PM   #5
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 4,686
Thanks: 50
Thanked 1,078 Times in 1,059 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Main Form ComboBox to Filter Records in SubForm

Quote:
Originally Posted by Mr. Southern View Post
DevastatioN,

I am trying to do the same thing.

I put: [Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeName] = '" & Me.cboEmployee & "'"
[Forms]![frm_YearCalendar]![subFormTotalDays].Form.FilterOn = True

After I select an option from the combobox it filters the subform but doesn't show any results. Am I missing something?
Wow! This is a 10-year old thread, and I think the last time DevastatioN posted anything on this forum was in 2011. I hope Paul's suggestion helps. Good luck!
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is offline   Reply With Quote
Old 09-10-2019, 01:49 PM   #6
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 9
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Main Form ComboBox to Filter Records in SubForm

Quote:
Originally Posted by pbaldy View Post
Make sure the bound column of the combo is the name rather than an ID field. If it's the ID field, use that for your filter instead of the name.
pbaldy,

The bound column of the combo box is actually EmployeeID. I changed the code and I get Run-time Error '3464' and highlights the second line of the code.
Mr. Southern is offline   Reply With Quote
Old 09-10-2019, 01:51 PM   #7
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Main Form ComboBox to Filter Records in SubForm

What is the text of the error, and what exactly is the code now? You wouldn't want the ' delimiters around a numeric value.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-10-2019, 01:58 PM   #8
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 9
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Main Form ComboBox to Filter Records in SubForm

Quote:
Originally Posted by pbaldy View Post
What is the text of the error, and what exactly is the code now? You wouldn't want the ' delimiters around a numeric value.
It says Data type mismatch in criteria expression. The code is:[Forms]![frm_YearCalendar]![subFormTotalDays].Form.FilterOn = True
Mr. Southern is offline   Reply With Quote
Old 09-10-2019, 02:00 PM   #9
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Main Form ComboBox to Filter Records in SubForm

The first line was the important one. Did you remove the delimiters as I suggested? Probably:

[Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeID] = " & Me.cboEmployee
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
The Following User Says Thank You to pbaldy For This Useful Post:
Mr. Southern (09-10-2019)
Old 09-10-2019, 02:05 PM   #10
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 9
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Main Form ComboBox to Filter Records in SubForm

Quote:
Originally Posted by pbaldy View Post
The first line was the important one. Did you remove the delimiters as I suggested? Probably:

[Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeID] = " & Me.cboEmployee
pbaldy,

That worked, thanks!
Mr. Southern is offline   Reply With Quote
Old 09-10-2019, 02:06 PM   #11
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Main Form ComboBox to Filter Records in SubForm

Happy to help!
__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Old 09-10-2019, 02:15 PM   #12
Mr. Southern
Newly Registered User
 
Join Date: Aug 2019
Location: United States
Posts: 59
Thanks: 9
Thanked 0 Times in 0 Posts
Mr. Southern is on a distinguished road
Re: Main Form ComboBox to Filter Records in SubForm

Quote:
Originally Posted by pbaldy View Post
Happy to help!
What would be the best way to get it to show blank results unless you picked something from the combo box?
Mr. Southern is offline   Reply With Quote
Old 09-10-2019, 02:20 PM   #13
pbaldy
Wino Moderator
 
pbaldy's Avatar
 
Join Date: Aug 2003
Location: Nevada, USA
Posts: 33,126
Thanks: 13
Thanked 4,078 Times in 4,013 Posts
pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold pbaldy is a splendid one to behold
Re: Main Form ComboBox to Filter Records in SubForm

So if nothing is selected the subform shows nothing? One way:

Code:
If Len(Me.cboEmployee & vbNullString) > 0 Then
  [Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeID] = " & Me.cboEmployee 
Else
  [Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeID] = 0"
End If 
[Forms]![frm_YearCalendar]![subFormTotalDays].Form.FilterOn = True
But I'd probably start the subform off with a record source that returned no records and then manipulate its record source here.

__________________
Paul
Microsoft Access MVP 2007-2019

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
pbaldy is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Filter Subform based on Combobox value in Main form pushpm Forms 1 04-23-2009 09:02 AM
Main form Combobox and subform gaju_3112 Forms 3 12-17-2007 09:29 PM
Subform records not tied to main form CEH Forms 1 12-20-2006 09:36 PM
[SOLVED] Counting records in a subform and displaying it moshi Forms 15 10-15-2003 03:55 AM
Need a field on my main form to reflect all records from my subform. SomeGuy Forms 2 12-01-2000 07:54 PM




All times are GMT -8. The time now is 04:29 AM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World