filter records from ComboBox

  • Thread starter Thread starter te_butts
  • Start date Start date
T

te_butts

I have a Combobox on a MainForm, and i want to filter the records on a
subform based on the selection from the combobox.

How would i do this using VBA?

Thanks
 
This is what i have:
----------------
If Len(Combo102) > 0 Then
With Forms![Add an Order and Details]![Order Details
Subform].Form.ProductID
.RowSource = "SELECT Warehouse FROM Products WHERE Warehouse = " &
Combo102
End With
End If
----------------

When i click on the combo102 and get a Warehouse, it's all good of course,
but when i click on the combobox in the subform, a popup window comes up and
is asking me to select something (i can't read it all because the window is
too small).

Now, i am not using an ID to pull it from, i am just using a "text" field.

I was using an ID for the Warehouse, but then it would store the Warehouse
ID in the table, and i don't want to do that.

Any more help would be appreciated.
 
I was using an ID for the Warehouse, but then it would store the Warehouse
ID in the table, and i don't want to do that.
Click to expand...

You really should be using the PK from the Warehouse table (WarehouseID?)
linked to the Product table by WarehouseID (the FK).


For example, If you have two tables: Product and Warehouse, the SQL for the
subform would look like:

(this is air code!!!)

SELECT Warehouse.WarehouseDesc
FROM Warehouse INNER JOIN Product ON Warehouse.WarehouseID_PK =
Product.WarehouseID_FK
WHERE Product.WarehouseID_FK = [forms]![Add an Order and Details].[combo102];


The SQL for Combo102 would be:

Select WarehouseID_PK, WarehouseDesc From Warehouse Order By WarehouseDesc;

And the after update event of Combo102 would be:

Sub Combo102_AfterUpdate()
Me.[Order Details Subform].Requery
End Sub


NOTE : Combo102 is unbound and in the form header.


I don't know your form design, how the subform relates to the main form, so
this probably won't work. But it should give you an idea on how to proceed.


HTH
--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


te_butts said:
This is what i have:
----------------
If Len(Combo102) > 0 Then
With Forms![Add an Order and Details]![Order Details
Subform].Form.ProductID
.RowSource = "SELECT Warehouse FROM Products WHERE Warehouse = " &
Combo102
End With
End If
----------------

When i click on the combo102 and get a Warehouse, it's all good of course,
but when i click on the combobox in the subform, a popup window comes up and
is asking me to select something (i can't read it all because the window is
too small).

Now, i am not using an ID to pull it from, i am just using a "text" field.

I was using an ID for the Warehouse, but then it would store the Warehouse
ID in the table, and i don't want to do that.

Any more help would be appreciated.

Steve Sanford said:
Look at this:

http://www.mvps.org/access/forms/frm0005.htm


HTH
Click to expand...
Click to expand...
 
Back
Top