combo box filter works in one case but not the other

[jleval]

I have a combo box that can filter my form and its sub form with the following code inserted in the after update event:

 Me.[ToolMeasurementCutterToolMeasurement Subform].Requery

the code in the rowsource is this:

SELECT DISTINCT CutterToolMeasurement.PartNumber FROM CutterToolMeasurement ORDER BY PartNumber;

This is able to look up my part number in the toolMeasurement form and display it in the ToolMeasurementCutterToolMeasurement Subformsubform below.

I am able to look up specific part numbers.

I want to be able to look up by trace number as well and I put the following code:

RowSource

SELECT DISTINCT CutterToolMeasurement.TraceNumber FROM CutterToolMeasurement ORDER BY TraceNumber;

after update event:

 Me.[ToolMeasurementCutterToolMeasurement Subform].Requery

Nothing happens. Any Ideas? I wish I could attach my database but it is too small. If there is anything else I need to add, let me know.

Thanks

 

[vbaInet]

I don't see any Filter in your SQL statement so I'm not even sure how you think it works in the first instance. Can you explain.

 

[jleval]

Attached is the properties showing how the combo box is somehow linked allowing a filter to be applied. the cboPartNumberTest allows me to filter all the partnumbers. I also want to be able to filter by TraceNumber as well. Is this master/child thing somehow limiting me to only use one filter?

Could you make a suggestion as to how to use multiple filters?

I know this is frustrating. Its driving me nuts. Feel free to ask any questions.

Any help is greatly appreciated:banghead:

 

[vbaInet]

In such a case you can use the Filter and FilterOn properties of the subform. Here's an example:

Me.SubformControl.Form.Filter = "ID = " & Me.txtID & " AND Surname = '" & Me.Combobox & "'"
Me.SubformControl.Form.FilterOn = True

So I'm filtering the subform by ID and Surname, not an ideal filter but it gives you an idea.