Help with subform filtering

[crazytegger]

I am trying to filter a subforms combolist options through a combolist on the main form. Heres what i have:

Form: frmOrderDetail
Datasource: tblOrders
cmbOrderSupplie rID w/ control source orders.[ordersupplierid]

Subform: frmOrderDetailS ubform
datasource: qryOrderDetails
cmbItemID w/ row source:
SELECT [items].[itemid], [items].[itemname]
FROM items
WHERE items.[itemsupplierid]="& [Forms!].[frmOrderDetail].[cmbOrderSupplie rId] & "
ORDER BY [items].[itemname];


Can someone please help, i hope this isnt too confusing

[CaptainD]

I assume you are using MS Access

Are you wanting to filter the information through the query or do you want to place the data into the form and then Filter the form?

When you place data in a form you filter by:

This example (and there are other ways to do it) passes the information to the form level variables and then filters based off the values. you could also pass the values to the sub.

I did it this way because I use it for forms and reports.

Private iFilterCode As Integer
Private sFilterName As String
Private iAccountCode As Integer

Option Compare Database
Option Explicit

Private Sub cboRequestFilter_AfterUpdate()
    iFilterCode = framFilter.Value
    sFilterName = cboRequestFilter.Text
    Call FilterForm(Me, sFilterName, iFilterCode)


End Sub

This is in a module

Public Sub FilterForm(FormName As Form, FilterBy As String, iFilterField As Integer)
Dim sFieldName As String
On Error GoTo ErrHandler

Select Case iFilterField
    Case 1
        sFieldName = "Submitted"
    Case 2
        sFieldName = "ProgramSupervisor"
    Case 3
        sFieldName = "ProgramManager"
End Select


If FilterBy = "All Request" Or FilterBy = "" Then
    FormName.FilterOn = False
    FormName.Requery
    Exit Sub
Else
    FormName.Filter = sFieldName & " = '" & FilterBy & "'"
    FormName.FilterOn = True
    FormName.Requery
End If

Exit Sub
ErrHandler:
    MsgBox "Error filtering data, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"

End Sub