Main Form ComboBox to Filter Records in SubForm

S

shiwawa

New member
Local time
Today, 02:11
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,911
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 said:
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.
Click to expand...

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?
 
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.
 
Mr. Southern said:
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?
Click to expand...
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!
 
pbaldy said:
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.
Click to expand...

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.
 
What is the text of the error, and what exactly is the code now? You wouldn't want the ' delimiters around a numeric value.
 
pbaldy said:
What is the text of the error, and what exactly is the code now? You wouldn't want the ' delimiters around a numeric value.
Click to expand...

It says Data type mismatch in criteria expression. The code is:[Forms]![frm_YearCalendar]![subFormTotalDays].Form.FilterOn = True
 
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 said:
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
Click to expand...

pbaldy,

That worked, thanks!
 
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.
 
(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
 
gavan said:
(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
Click to expand...
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!
 
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)
 
You must log in or register to reply here.

Similar threads

K
Solved Combobox in Subform not updating data
Replies
2
Views
298
kiaconchim
K
T
Solved How to make vba check all records in continuous form not only top one record
Replies
14
Views
530
TMK
T
C
Join key of '***' not in recordset
Replies
9
Views
275
ChrisLeicester
C
R
Check if value exists in combobox and give user a prompt if they try to change it after it has been set
2
Replies
30
Views
955
RFreund
R
B
Combo Box Search Issue
2
Replies
34
Views
1,336
MajP
MajP

Users who are viewing this thread

Total: 1 (members: 0, guests: 1)
Back
Top Bottom