Update calc field after context Filter

[bhammer]

My form has calculated fields that count from a query displayed as a subform. It's a crosstab query, so I display the query datasheet rather than a form because the column headings are not constant.



Anyway, I need to update the calc fields after the user applies a filter by using the context menu (right click). There is no event that is triggered for this, as far as I can see, because it is a query rather than a form. How can I .requery the calc field textboxes??

[June7]

By 'calculated fields' you mean textbox on main form with expression in ControlSource? This is a new one for me. What happens when the Refresh button on toolbar is clicked? If that works then code a button on main form with Refresh/Requery code.

[bhammer]

Yes, a textbox on the main form with ControlSource =Count([AddressCount]) where AddressCount is calculated on the crosstab query "subform" which is not a form, but a query displayed on the main form as a subform. I'm sure this is the problem, but I cannot make a form from a crosstab.

[bhammer]

F9, SHIFT+F9, Records>Refresh all have no effect.

[June7]

Want to provide db for analysis? Follow instructions at bottom of my post.

Yes, stabilizing crosstab to run perpetually as the source for a form or report is not easy but sometimes possible.

[bhammer]

Making a form from a crosstab is really not what I am trying to accomplish. I just want the calculated field on the main form to update after the user Filters the crosstab query via the right-click context menu. There seems to be no Event that I can trap in order to perform a Requery on the text box.

I'm trying to attach an mdb, but the Manage Attachments dialog is unclear to me.

Anyway, if you get my mdb, open Form1 and note the calculated field at the bottom of the main form matches the number of records displayed in the crosstab query (6), good. Then, Filter by Selection using the context menu on any field, such as Phase, and the calculated field does NOT update to the new record count (number of Addresses). This is the problem. How to requery that field after filtering.

db1.zip

[June7]

Here's what I learned. Even if you get the textbox to recalc, the result will still be 6 because the DCount is looking at the query object which has not been saved with the filter criteria. Need to look at the filtered recordset of the subform container.

This is the best I can get (remove expression from the textbox):

Private Sub Form_Current()
Me.Text2 = Me.Child0.Form.RecordsetClone.RecordCount
End Sub

Private Sub Child0_Exit(Cancel As Integer)
Me.Text2 = Me.Child0.Form.RecordsetClone.RecordCount
End Sub

The only other option I can see is to stabilize the crosstab so can build a form with it. How many issues are possible?

[bhammer]

Thanks for looking at this.

Up to 100 issues are possible, so stabilizing the crosstab is not workable. I've looked into dynamically generating headers in a crosstab, but that gets complex quickly. I could limit the design but would prefer a max flexible solution, if possible.

Form_Current fires on the main form, right? but main form is unbound...so no OnCurrent event happens there.

Child0_Exit happens when cursor leaves the query container? but this will not fire as long as the user stays in the query performing Filter on Selections.

In my working app the crosstab query's QueryDef is dynamically generated SQL built from the RecordSource and current Filter applied on the calling form. So the workaround for now is that the user must filter for what he wants back on the calling form, then click the button that generates the SQL and opens the form that shows the calc field. Not as slick as if the calc field responded after a filter on the query!

The odd thing to me is that there is no ApplyFilter Event for right-click filtering...in a query...

[June7]

I know the main form is unbound but the Current event still works.

100 issues is a lot but does not make stabilizing the crosstab impossible. The query could be designed to always produce a column for every issue even if there are no associated detail records for that issue. This requires a table of all possible issues to be included in the query with join type 'Include all records from Issues ...'
The tblIssue is already included so change the jointype.

Why does the crosstab query have two tblIssue?

Add another record to tblIssue and consider:
TRANSFORM Count(tblAddress.AddressID) AS CountOfAddressID
SELECT Nz([AddressID],"none") AS AddID, tblAddress.StreetNumber, tblAddress.StreetName, tblAddress.Phase
FROM tblIssue LEFT JOIN (tblAddress RIGHT JOIN tblObservations ON tblAddress.AddressID = tblObservations.Address_ID) ON tblIssue.IssueID = tblObservations.Issue_ID
GROUP BY Nz([AddressID],"none"), tblAddress.StreetNumber, tblAddress.StreetName, tblAddress.Phase
PIVOT tblIssue.Issue;

[bhammer]

The crosstab should only have one tblIssue. Access added tblIssue_1 for some reason...

If I alter the crosstab to include "blank" addresses, won't that make counting the addresses in the list a problem?

I have an idea to workaround requerying the calculated textbox even though no Event is available after applying a Filter using the right-click--The OnTimer event could requery every 2 seconds or so.

Another idea: how can I get the main form to mirror the current RecordSet of the subform, with a Filter applied?

[June7]

Don't know why Access added tblIssue_1 except maybe you accidentally clicked twice when adding table from the table list. I still do that sometimes. I removed it and made a join with tblIssue.

Did you try the suggested query? The count looks fine to me.

The timer event did occur to me but timer is a process hog and still not 'immediate' although I guess users could come to accept a 2-second delay.

Main form to mirror the subform recordset? - probably and would require code in some event.

[bhammer]

I tried the Right Join but the Total Addresses textbox on the main form does not update after a Filter on Selection, which is what I'm after. The record count next to the navigation buttons updates, but I am trying to get the calculated field on the main form to display that count.

Using the Current event works with:
Me.Text2 = Me.Child0.Form.RecordsetClone.RecordCount

What would be better is to set the recordset of the main form to the RecorsetClone of the subform. Then have the calculated fields count from the main form. But I cannot get that strategy to work.
Set Me.Recordset = Forms.Form1.Child0.Form.RecordsetClone ??
Then the Control Source for Text2 could be "=Count([AddID])"

This works correctly when the form opens, but after filtering, displays #Error...

[June7]

Right, the suggested query does nothing for the form/subform current design. It was offered as a way to have a subform container with a form as SourceObject. Subform in Continuous view could have a textbox in footer (visible or not visible) that calculates the recordcount, textbox on main form could reference that footer textbox.