Main Form ComboBox to Filter Records in SubForm (1 Viewer)

shiwawa

New member
Local time
Yesterday, 21:25
Joined
May 24, 2009
Messages
1
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.
 

Attachments

  • ScreenShot.jpg
    ScreenShot.jpg
    79.9 KB · Views: 1,327

DevastatioN

Registered User.
Local time
Today, 01:25
Joined
Nov 21, 2007
Messages
242
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.
 

Mr. Southern

Registered User.
Local time
Yesterday, 23:25
Joined
Aug 29, 2019
Messages
90
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?
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 21:25
Joined
Aug 30, 2003
Messages
35,741
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.
 

theDBguy

I’m here to help
Staff member
Local time
Yesterday, 21:25
Joined
Oct 29, 2018
Messages
19,134
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!
 

Mr. Southern

Registered User.
Local time
Yesterday, 23:25
Joined
Aug 29, 2019
Messages
90
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.
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 21:25
Joined
Aug 30, 2003
Messages
35,741
What is the text of the error, and what exactly is the code now? You wouldn't want the ' delimiters around a numeric value.
 

Mr. Southern

Registered User.
Local time
Yesterday, 23:25
Joined
Aug 29, 2019
Messages
90
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
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 21:25
Joined
Aug 30, 2003
Messages
35,741
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
 

Mr. Southern

Registered User.
Local time
Yesterday, 23:25
Joined
Aug 29, 2019
Messages
90
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!
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 21:25
Joined
Aug 30, 2003
Messages
35,741
Happy to help!
 

pbaldy

Wino Moderator
Staff member
Local time
Yesterday, 21:25
Joined
Aug 30, 2003
Messages
35,741
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.
 

Users who are viewing this thread

Top Bottom