469,320 Members | 2,121 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,320 developers. It's quick & easy.

Help with subform filtering

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

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

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


Can someone please help, i hope this isnt too confusing
May 2 '06 #1
1 1447
CaptainD
135 100+
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.

Expand|Select|Wrap|Line Numbers
  1. Private iFilterCode As Integer
  2. Private sFilterName As String
  3. Private iAccountCode As Integer
  4.  
  5. Option Compare Database
  6. Option Explicit
  7.  
  8. Private Sub cboRequestFilter_AfterUpdate()
  9.     iFilterCode = framFilter.Value
  10.     sFilterName = cboRequestFilter.Text
  11.     Call FilterForm(Me, sFilterName, iFilterCode)
  12.  
  13.  
  14. End Sub
  15.  
This is in a module
Expand|Select|Wrap|Line Numbers
  1. Public Sub FilterForm(FormName As Form, FilterBy As String, iFilterField As Integer)
  2. Dim sFieldName As String
  3. On Error GoTo ErrHandler
  4.  
  5. Select Case iFilterField
  6.     Case 1
  7.         sFieldName = "Submitted"
  8.     Case 2
  9.         sFieldName = "ProgramSupervisor"
  10.     Case 3
  11.         sFieldName = "ProgramManager"
  12. End Select
  13.  
  14.  
  15. If FilterBy = "All Request" Or FilterBy = "" Then
  16.     FormName.FilterOn = False
  17.     FormName.Requery
  18.     Exit Sub
  19. Else
  20.     FormName.Filter = sFieldName & " = '" & FilterBy & "'"
  21.     FormName.FilterOn = True
  22.     FormName.Requery
  23. End If
  24.  
  25. Exit Sub
  26. ErrHandler:
  27.     MsgBox "Error filtering data, error # " & Err.Number & ", " & Err.Description, vbOKOnly, "Error"
  28.  
  29. End Sub
  30.  
May 2 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

3 posts views Thread by Todd | last post: by
3 posts views Thread by dhowell | last post: by
2 posts views Thread by David W. Fenton | last post: by
3 posts views Thread by paquer | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
reply views Thread by suresh191 | last post: by
reply views Thread by listenups61195 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.