Main Form ComboBox to Filter Records in SubForm

[shiwawa]

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.

[ADezii]

Programmatically setting the Filter Property for a Sub-Form from a Main Form can be a little problematic since you must know exactly when to turn the Filter ON and when to turn it OFF. That being said, here are a few, simple, assumptions:

1. The 2 Tables involved are [Orders] and [Order Details].
2. These Tables are Linked by the [Order ID] Field via a 1 to MANY Relationship, namely:
   [Orders].[Order ID]{1} ==> [Order Details].[Order ID]{MANY}.
3. The [Order Details] Table contains a Field named [Section]{TEXT}.
4. The Name of the Order Details Sub-Form is Order Details.
5. The Source Object for the Order Details Sub-Form is subfOrderDetails.
6. The Name of the Filtering Combo Box is cboFilter.
7. cboFilter draws its Data from a Sections Table consisting of [SectionID] and [Section] Fields.
8. cboFilter is a Single Column Combo Box consisting of the [Section Field] only. If this is not the case, the code must be modified, namely substitute Me![cboFilter].Column(1) for Me![cboFilter].
9. Here is the code which must reside in the AfterUpdate() Event of cboFilter:
   

   Private Sub cboFilter_AfterUpdate()
   Dim intNumOfDetails As Integer
   Dim strMsg As String
    
   'Will the Filter (Order ID/Section) actually produce any Records?)
   intNumOfDetails = DCount("*", "[Order Details]", "[Order ID] = " & Me![Order ID] & _
                             " And [Section] = '" & Me![cboFilter] & "'")
    
   If Not IsNull(Me![cboFilter]) Then      'must be an entry
     If intNumOfDetails > 0 Then           'Filter produces Records
       Me![Order Details].Form.Filter = "[Section] = '" & Me![cboFilter] & "'"
       Me![Order Details].Form.FilterOn = True
     Else
       strMsg = "No Order Details exist for Order ID [" & Me![Order ID] & "] and " & _
                "Section [" & Me![cboFilter] & "]!"
         MsgBox strMsg, vbExclamation, "No Order Details"
     End If
   End If
   End Sub

10. Any problems, just let us know.

[shiwawa]

Used your code and got an error stop on this line:

intNumOfDetails = DCount("*", "[Order Details]", "[Order ID] = " & Me![Order ID] & _
" And [Section] = '" & Me![CboFilter] & "'")

Meanwhile, your assumptions were right except for nos 4 and 5 which I adjusted accordingly.

Any ideas?

[ADezii]

@shiwawa

1. What is the Error Message that you are receiving?
2. I know some questions are redundant, but just to be sure...
3. Is your Table Name [Order Details]?
4. Is the [Order ID] Field named 'exactly' that, namely [Order ID], and not OrderID?
5. Is the [Order ID] Field NUMERIC in nature?
6. Does the [Order Details] Table have a Field named Section?
7. Is the Section Field a TEXT Data Type?
8. Is your Filter Combo Box named cboFilter?
9. If all the above Response are Yes, would you be willing to mail me a Copy of the Database with some Sample Data?

[NeoPa]

You should find the following article (Example Filtering on a Form) helpful. There is an example attached.

[NeoPa]

A new thread (Add Button) has been created from a new post in this thread. Please visit if you think you can be of help.