Option Group Filtering

[kevsim]

I have a form which is based on a Table called T_Product. There is an unbound ComboBox where I can select a Product record, this works OK. I have now added a field in the T_Product table called ProdComp. The idea of this field, is when I enter data in the product form, I can separate Products from Components and vice versa. When I Type “=Product” in the ProdComp field in the T_Product query grid criteria, I can only select products from the combo box. I then remove this selection and type “=Component” and can only select components, this works as I want. I have never used an Option Group before, so I decided to use this for my selection from the combo box. When I select Product, then select from the combo box, all listings appear, I select Component, then select from the combo box, all listings appear, I choose All and all listings appear. My selections will not filter for my choice. Below is the code I used in the Option Group and the Sequel statement for the combo box. I would appreciate some assistance in what I am doing wrong.
Option Group
Private Sub Frame110_AfterUpdate()
If Frame110 = 1 Then
Me.Filter = "ProdComp = ‘Product’"
Me.FilterOn = True
End If
If Frame110 = 2 Then
Me.Filter = "ProdComp = ‘Component’"
Me.FilterOn = True
End If
If Frame110 = 3 Then
Me.FilterOn = False
End If
End Sub
Combo Box
SELECT DISTINCTROW T_Product.ProductID, T_Product.Product, T_Product.Manufacturer, T_Product.ProdComp
FROM T_Product
ORDER BY T_Product.Product, T_Product.Manufacturer;
kevsim

 

[Travis]

If I understand correctly what you are trying to do is to filter the combobox. The "Me" refers to the Form Object.

Create a query out of the SELECT statement that you are using for your combobox:

SELECT DISTINCTROW T_Product.ProductID, T_Product.Product, T_Product.Manufacturer, T_Product.ProdComp
FROM T_Product
WHERE T_ProdComp Like IIF(FORMS![YOUR FORMS NAME].Frame110=1,"Product’",IIF(FORMS![YOUR FORMS NAME].Frame110=2,"Component","*"))
ORDER BY T_Product.Product, T_Product.Manufacturer;


Then on the After Update of Frame110 do a Requery on the combobox

 

[kevsim]

Travis, Thank you for the info, I kept playing and finally came up with the code, it also allows for changing the labels. I also called the routine when the form opens, all works well. Below is the code I used.
Private Sub Frame110_AfterUpdate()
Dim SQLText, WClause
SQLText = "SELECT [ProductID],[Product],[Manufacturer]FROM T_Product WHERE ProdComp = "
Select Case Frame110
Case 1
WClause = Chr(34) & "Product" & Chr(34)
Me.Label2.Visible = True
Me.Label111.Visible = False
Me.Label124.Visible = False
Case 2
WClause = Chr(34) & "Component" & Chr(34)
Me.Label2.Visible = False
Me.Label111.Visible = True
Me.Label124.Visible = False
Case 3
SQLText = "SELECT [ProductID],[Product],[Manufacturer]FROM T_Product"
WClause = ""
Me.Label2.Visible = False
Me.Label111.Visible = False
Me.Label124.Visible = True
End Select
With Me![Combo1]
.RowSource = SQLText & WClause
.Requery
End With
End Sub
kevsim