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

shiwawa

New member
Local time
Today, 16:33
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,652

DevastatioN

Registered User.
Local time
Today, 20:33
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
Today, 18:33
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
Today, 16:33
Joined
Aug 30, 2003
Messages
36,118
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
Today, 16:33
Joined
Oct 29, 2018
Messages
21,358
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
Today, 18:33
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
Today, 16:33
Joined
Aug 30, 2003
Messages
36,118
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
Today, 18:33
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
Today, 16:33
Joined
Aug 30, 2003
Messages
36,118
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
Today, 18:33
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
Today, 16:33
Joined
Aug 30, 2003
Messages
36,118
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.
 

gavan

New member
Local time
Tomorrow, 02:33
Joined
Aug 6, 2023
Messages
1
(IF any one need to show all record in sub form when the combobox is empty or null )

If Len(Me.cboEmployee & vbNullString) > 0 Then
[Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeID] = " & Me.cboEmployee
Else
[Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeID] = [EmployeeID] like '*'"
End If
[Forms]![frm_YearCalendar]![subFormTotalDays].Form.FilterOn = True


with best regard
gavano
 

theDBguy

I’m here to help
Staff member
Local time
Today, 16:33
Joined
Oct 29, 2018
Messages
21,358
(IF any one need to show all record in sub form when the combobox is empty or null )

If Len(Me.cboEmployee & vbNullString) > 0 Then
[Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeID] = " & Me.cboEmployee
Else
[Forms]![frm_YearCalendar]![subFormTotalDays].Form.Filter = "[EmployeeID] = [EmployeeID] like '*'"
End If
[Forms]![frm_YearCalendar]![subFormTotalDays].Form.FilterOn = True


with best regard
gavano
Hi @gavano. Welcome to AWF!

Just FYI, this is an old thread, and it's more advisable to use Is Null over the Like operator.

Cheers!
 

pbaldy

Wino Moderator
Staff member
Local time
Today, 16:33
Joined
Aug 30, 2003
Messages
36,118
Welcome gavano. I'll add that your Else syntax is invalid as is, but personally I wouldn't even set a filter in the Else (FilterOn = False)
 

Users who are viewing this thread

Top Bottom