Full Version: Problem filtering subform with combo box db attached
UtterAccess Forums > Microsoft® Access > Access Forms
equestrian
I have frmCustomerLookup. It has a subform that displays line items that have been sold. It also has two combo boxes. One allows the user to select a customer, and the other allows the user to select an inventory item. When the screen pulls up the customer and part information are both blank and the subform displays all the line items. This is fine. However, what I would like to do is:
if a customer is selected only show that customer's line items
if an inventory id is selected only show line items with that inventory id
if a customer and inventory id is selected only show line items that match the cusomer and inventory id
I have tried:
CODE
Private Sub cboCustomer_Change()
Me.Filter = "[Cust_pk] =[cboCustomer].Column(1)"
Me.FilterOn = True
End Sub

This did not work. I did not get any errors, but the data in the subform remained the same. Any suggestions?
theDBguy
Try changing it to:
e.Filter = "[Cust_pk] = " & Me.[cboCustomer].Column(1)
Hope that helps...
hdlee
Instead of
Me.Filter = "[Cust_pk] =[cboCustomer].Column(1)"
Me.FilterOn = True

Why don't you try this.
Me.subForm.Form.Filter = "[Cust_pk] = " &[cboCustomer].Column(1)
Me.subForm.Form.FilterOn = True

If [Cust_pk] is a text then, use
Me.subForm.Form.Filter = "[Cust_pk] = ' " &[cboCustomer].Column(1) & " ' "

HTH
Edited by: hdlee on Mon May 12 17:10:13 EDT 2008.
equestrian
theDBguy,
Otried your suggestion, but it did not filter the subform. I didn't get any errors, but I think it was trying to filter the main form.
equestrian
hdlee,
Otried the code you gave, but I am getting a compile error. It says method or data member not found.
!--c1-->
CODE
Me[color="blue"].SubForm[/color].Form.Filter = "[Cust_pk] = " &[cboCustomer].Column(1)
Me.subForm.Form.FilterOn = True

any other suggestions?
theDBguy
Sorry about that... I didn't download your db, so I was just going by the code you posted. Good luck.
hdlee
I didn't know you have attached a file.
Otook a look and the code below should work.
I recommend you to make the code on AfterUpdate instead of Change event.
Also, use CustID instead of Cust_pk.

Private Sub cboCustomer_AfterUpdate()

Me![qryPartsSoldtoCustomer subform1].Form.Filter = "CustID='" & Me.cboCustomer.Column(1) & "'"
Me![qryPartsSoldtoCustomer subform1].Form.FilterOn = True

End Sub

HTH

Edited by: hdlee on Tue May 13 9:34:45 EDT 2008.
equestrian
Thank you hdlee. The code you provided worked great. I was able to filter the subform by the customer combo box. I also was able to alter the code to filter the subform based on the part number combo box. If you don't mind I have two questions. One is since I am learning, why did I have to use CustId instead of Cust_pk? I am just wondering in case I come across something like this at a later date.
econd, I now need to have the combo boxes work at the same time. Another words, if the subform is already filtered by the part number combo box and the user selects a value in the customer combo box I need the subform to be filtered by both combo boxes. Here is the code I tried:
CODE
Private Sub cboCustomer_AfterUpdate()
If cboPartNumber.Column(1) > " " Then
[color="blue"]Me![qryPartsSoldtoCustomer subform1].Form.Filter = "CustID='" & Me.cboCustomer.Column(1) & "'"
Me![qryPartsSoldtoCustomer subform1].Form.Filter = "InvtID='" & Me.cboPartNumber.Column(1) & "'" [/color]
Me![qryPartsSoldtoCustomer subform1].Form.FilterOn = True
Else
Me![qryPartsSoldtoCustomer subform1].Form.Filter = "CustID='" & Me.cboCustomer.Column(1) & "'"
Me![qryPartsSoldtoCustomer subform1].Form.FilterOn = True
End If
End Sub

This doesn't quite work correctly. It is only filtering on the part number combo box. I somehow need to combine the two line that are blue. I am not sure however what the syntax is. I would appreciate any help you can give me.
hdlee
1. You can use Cust_pk instead of CustID. But, you have to modify your code.
Since you used Me.CboCustomer.Column(1) which is CustID, I used it.
To use Cust_pk, you have to use Me.cboCustomer.Column(0).
Also, if you use a numeric field, you have to omit a single quote(') in filter criteria. (This is a common mistake.)
. To filter both of them, you have to concatenate the filter criteria.
Take a look at the code below.
Also, there is a difference using a numeric field and text field criteria (with and without a single quote.)
Private Sub cboCustomer_AfterUpdate()
Call FilterSubForm
End Sub
Private Sub cboPartNumber_AfterUpdate()
Call FilterSubForm
End Sub
Private Sub Command38_Click()
Me.cboCustomer = ""
Call FilterSubForm
End Sub
Private Sub Command39_Click()
Me.cboPartNumber = ""
Call FilterSubForm
End Sub
Private Sub FilterSubForm()

Dim strFilter As String

If Me.cboCustomer <> "" Then
If Me.cboPartNumber <> "" Then
strFilter = "Cust_pk=" & Me.cboCustomer.Column(0) & " And InvtID='" & Me.cboPartNumber.Column(1) & "'"
Else
strFilter = "Cust_pk=" & Me.cboCustomer.Column(0)
End If
Else
If Me.cboPartNumber <> "" Then
strFilter = "InvtID='" & Me.cboPartNumber.Column(1) & "'"
End If
End If
Me![qryPartsSoldtoCustomer subform1].Form.Filter = strFilter
Me![qryPartsSoldtoCustomer subform1].Form.FilterOn = True
End Sub
equestrian
Thank you!
I am going to have " Combo Boxes start at Column(0)" tattooed on my forehead! Of course, I will probably forget to look at it.
I am attaching a copy of my db because I looked and looked on this site and on the Internet in general and I could not find a working example of this. Maybe someone else has been looking and this will help them. Thanks again.
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.