Problem with form filter (1 Viewer)

[Talus]

Hello All,

I am developing a continuous form for a timber yard, using Access 2016. The form manages pay rates for carriers of loads of timber products. There are three basic types of journeys these carriers can make: from a remote timber source to my client's yard; from my client's yard to a mill, and from a remote timber source to a mill.

The form's recordsource is a query based on a pricing table (tblPricing) that has a column (TypeTrip) that defines the kind of trip the record is for. The query has a WHERE clause that references a tempvar that is set in the AfterUpdate event of an option group whose choices are the three kinds of trips:

SELECT tblPricing.* FROM tblPricing WHERE (((tblPricing.TypeTrip)=[Tempvars]![TV_Pricing_TypeTrip]));

The idea is for the query to provide a fundamental filter for the records being presented on the form, based on the type of trip.

The AfterUpdate event of the option group also calls logic to set additional filters appropriate to to the TypeTrip, using the form's Filter property. The last line in the AfterUpdate event is Me.Requery. This should ensure that the basic set of records being presented belongs to the type of trip specified in the option group.

This works for TripTypes 1 and 2, but not for TypeTrip 3, Timber to Mill:

As you can see, for Timber to Mill the form presents TripType 2 records rather than TripType 3. If the form recordsource query is manually opened on its own while Timber to Mill is selected, it shows the correct records - none, because no Type 3 records are yet in the system.

Has anyone run into this phenomenon before? I don't know whether there is a gap in my education, the form is somehow corrupted, there is a bug in Access, or something else.

Thanks!

 

[gemma-the-husky]

either
how is the subform linked to the main form, so that it auto-updates OR
what is the afterupdate code for the option group/frame, so that it manually updates.

 

[CJ_London]

bit confused - are you talking about filters or criteria?

either way what is the code that populates 'the tempvar that is set in the AfterUpdate event of an option group whose choices are the three kinds of trips' and 'The AfterUpdate event of the option group also calls logic to set additional filters appropriate to to the TypeTrip, using the form's Filter property'? why not just have your query reference the form directly? why apply filters when you are applying criteria?

 

[Gasman]

Well as TypeTrip is 2 is in your last two pics, I have no confidence that you are setting that value correctly?

 

[Talus]

either
how is the subform linked to the main form, so that it auto-updates OR
what is the afterupdate code for the option group/frame, so that it manually updates.

There is no subform.

The afterupdate code is pretty long. Here is are the lines directly related to this issue:

Private Sub grpTypeTrip_AfterUpdate()
SetValuesForCurrentTypeTrip
End Sub

Public Sub SetValuesForCurrentTypeTrip()
TempVars!TV_Pricing_TypeTrip = grpTypeTrip.Value
. . .
Me.Requery
End Sub

 

[arnelgp]

is the Form Recordsource based on a Filtered Query?

 

[Talus]

bit confused - are you talking about filters or criteria?

either way what is the code that populates 'the tempvar that is set in the AfterUpdate event of an option group whose choices are the three kinds of trips' and 'The AfterUpdate event of the option group also calls logic to set additional filters appropriate to to the TypeTrip, using the form's Filter property'? why not just have your query reference the form directly? why apply filters when you are applying criteria?

The code that affects the RecordSource query is posted above.
Why not apply filters? It's six of one, half a dozen of the other.

Well as TypeTrip is 2 is in your last two pics, I have no confidence that you are setting that value correctly?

The value is being set correctly. Opening the RecordSource query manually while the form is open with "Timber to Mill" is selected returns the right results. That's my question - why is the third option returning TypeTrip 2 records on the form?

 

[Talus]

is the Form Recordsource based on a Filtered Query?

It's based on a query with a WHERE clause that uses a tempvar set by the form. Form filters are set in VBA. However, they do not target the column that's used in the query's WHERE clause.

 

[arnelgp]

so you are Adding filter to the Already filtered Query?

Requery is not the solution.
you should Call the sub/function that is doing the Adding of Filter.
if you do not have one, just group all your filter in 1 public sub/function (inside the form).

 

[Talus]

so you are Adding filter to the Already filtered Query?

Requery is not the solution.
you should Call the sub/function that is doing the Adding of Filter.
if you do not have one, just group all your filter in 1 public sub/function (inside the form).

arnelgp, could you please say what you mean by a "filtered query"? The form recordsource query is just a query that includes a WHERE clause. The WHERE clause is what I meant when I said the query was intended to provide a filter.

 

[CJ_London]

Why not apply filters? It's six of one, half a dozen of the other.

no, it's not. One fetches data for the recordsource, the other filters the records in the recordsource. Criteria limits the data brought through, a filter can be changed to show more or less of the data brought through.

 

[Talus]

no, it's not. One fetches data for the recordsource, the other filters the records in the recordsource. Criteria limits the data brought through, a filter can be changed to show more or less of the data brought through.

Yes, I understand that. The problem is that the criteria in the recordsource is not properly limiting the records being allowed through, in one case.

In terms of what tool to use to limit the records being displayed, in this case I use a combination of query criteria and filters.

 

[Gasman]

There is no subform.

The afterupdate code is pretty long. Here is are the lines directly related to this issue:

Private Sub grpTypeTrip_AfterUpdate()
SetValuesForCurrentTypeTrip
End Sub

Public Sub SetValuesForCurrentTypeTrip()
TempVars!TV_Pricing_TypeTrip = grpTypeTrip.Value
. . .
Me.Requery
End Sub

Have you even walked though the code to see what those values are?
I would always be using the Me prefix as well to indicate it is the form?, but that is just me.

 

[shokly]

I suppose you can put a Messag box between the codes before the statement requery, to find out if the options are set to each value selected or not, like below:

Private Sub grpTypeTrip_AfterUpdate()
SetValuesForCurrentTypeTrip
End Sub

Public Sub SetValuesForCurrentTypeTrip()
TempVars!TV_Pricing_TypeTrip = grpTypeTrip.Value
. . .
MsgBox grpTypetrip.Value
Me.Requery
End Sub

 

[Gasman]

I suppose you can put a Messag box between the codes before the statement requery, to find out if the options are set to each value selected or not, like below:

Private Sub grpTypeTrip_AfterUpdate()
SetValuesForCurrentTypeTrip
End Sub

Public Sub SetValuesForCurrentTypeTrip()
TempVars!TV_Pricing_TypeTrip = grpTypeTrip.Value
. . .
MsgBox grpTypetrip.Value
Me.Requery
End Sub

Much simpler to use F8 and step through the code I would have thought?

This is going to be what I call one of those 'silly' errors, where, when the cause is found, there is going to be some forehead slapping.

 

[Talus]

I have checked the value of [Tempvars]![TV_Pricing_TypeTrip] while debugging.

The RecordSource query should be preventing any records with TypeTrip = 2 from being displayed when "Timber to Mill" is selected. As you can see above this isn't working. However, running the same query manually after selecting "Timber to Mill" on the form does return the correct set of records. That suggests to me that the problem is not in the query or its selection criteria.

While it's always possible that this is a "forehead slapping" error, I think the problem is more subtle, or the form is corrupted. I'll play around with it a bit more.

 

[Gasman]

You originally said there were no Type 3 records?

, because no Type 3 records are yet in the system.

 

[Talus]

You originally said there were no Type 3 records?

Yes. There still aren't. Run manually, the query returns the correct set of records for Type 3: a null set.

 

[CJ_London]

what if you select type 1, see the results, then select type 3? Does this bring up the type1 or type 2 records?

 

[Talus]

When you select type 1, then select type 3, it returns a subset of type 1 records.