Filtering by fields in a continuous subform. How?

[twiggymates]

I am attempting to filter data in a query set of a particular field in a subform based on data selected in another field. However, as this is a continuous subform this produces errors and erases the data when i enter a new record (or have different data in different records).

At the minute i am filtering using the "form_orderdetails.catogoryid.value" field in a query criteria. Is there anyway of just selecting the field value of the current record?

Wording this is not very easy so please bear with me.

 

[MinusM]

The question is not very clear
Are you using a dropdown to show the second field?
If so you can change the rowsource on OnClick event of the dropdown. On Afterupdate Event of the dropdown change back the rowsource without filter



Best of luck

 

[twiggymates]

Sorry about the wording. Yes, I am using a drop down combo box to filter selections in a second drop down combo box. As I am filtering based on the first combo box (by placing a form_orderdetails.catogoryid.value in the criteria of the query), this is fine for the first record. But, as the form is a continuous form, if I try and add a second record with a different first combo selecton, all the second combos are deleted (because (i think) the form_orderdetails.carogoryid.value has more than one value).

Basically the question is this: how can I make each record filter a combo independantly based in something selected in a second combo in a continuous form?

Not any easy question to write at all.

 

[MinusM]

You just cannot do that.

You are not losing the data of the first row, but it is not getting displayed since the data is out of range of the new rowsource

What I sugggested is how you can get around it. Set the rowsource to be without filter. But if you are having the same item value for different category id then you have a problem. YOU WILL HAVE TO HAVE A UNIQUE ITEM VALUE.

If item value is unique then change the rowsource adding the filter on OnClick of the Combo. DO not forget to replace the rowsource to without fiter once you go out of the combo

Best of luck

 

[twiggymates]

MinusM - thats great - it does work except whilst I am in the particular combo box I am changing it makes the same combo box in other records go blank - anyway of stopping this? When I exit all returns okay. This is my code:

Private Sub ServiceID_Enter()
ServiceID.RowSourceType = "Table/Query"
ServiceID.RowSource = "CatogoryQuery"
End Sub

Private Sub ServiceID_Exit(Cancel As Integer)
Forms![OrderDetails]![TotalCost] = Me!SumCost
ServiceID.RowSource = "SELECT Service.CustomerID, Service.ServiceID, Service.ServiceName, Service.CatogoryID, Category.Category, Service.ServiceNoLongerInUse FROM Category INNER JOIN Service ON Category.CategoryID=Service.CatogoryID WHERE (((Service.CustomerID)=Forms!OrderDetails!CustomerID)) ORDER BY Service.ServiceName, Category.Category;"
Form.Refresh
End Sub

It does work I am just trying to make it look aesthetically pleasing.

Thanks for the help

 

[MinusM]

I am not sure what is your CategoryQuery, but if you are filtering for just cutomerid then I guess the codes should be reversed. The code in Enter Event should be in Exit event and vice versa

BUT MAKE SURE GET RID OF THE FORM.REFRESH

In case you didn't get the logic, I will explain.
When the [red]combo box drops down [/red] the filter is enabled so that the user is seeing only the filtered set. When you change the rowsource immediately it takes effect. So do not call form.refresh.

When you call Refresh, the other rows' combos also get filtered rowsource and saved data doesn't match any of the lines in this fitered rowsource. So it seems to be emptied.

You call refresh only when there is no filter in the rowsource in case there is a need for it. This way you can fool the system

In short, what you have to make sure is when the ComboList is displayed, Activate the filter. When it closes remove the filter from the rowsource

Best of luck

 

[twiggymates]

Minus - I don't think the refresh is the problem. I have removed it anyway. Basically whenever I enter the service select combo, it activates the query and whenever I leave it is removed. When I enter the query it filters (catogoryquery) based on a combo called catogory - i.e. this is in the criteria of the catogoryquery as form_servicedetails.catogory. As there could be lots of rows displayed it temporarily blanks anyother service combo in any other rows unless it is the same catogory as the row I am editing. When I exit all returns okay. This strictly speaking is not a great problem but could make people think there is an error. Any other advice - so far you have basically given me a solution which is great but I believe there must be a solution to stop it going blank.