Results 1 to 13 of 13
  1. #1
    bhammer is offline Novice
    Windows XP Access 2002
    Join Date
    Apr 2012
    Posts
    11

    Update calc field after context Filter

    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??

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    bhammer is offline Novice
    Windows XP Access 2002
    Join Date
    Apr 2012
    Posts
    11
    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.

  4. #4
    bhammer is offline Novice
    Windows XP Access 2002
    Join Date
    Apr 2012
    Posts
    11
    F9, SHIFT+F9, Records>Refresh all have no effect.

  5. #5
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  6. #6
    bhammer is offline Novice
    Windows XP Access 2002
    Join Date
    Apr 2012
    Posts
    11
    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

  7. #7
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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?
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  8. #8
    bhammer is offline Novice
    Windows XP Access 2002
    Join Date
    Apr 2012
    Posts
    11
    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...

  9. #9
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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;
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    bhammer is offline Novice
    Windows XP Access 2002
    Join Date
    Apr 2012
    Posts
    11
    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?

  11. #11
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  12. #12
    bhammer is offline Novice
    Windows XP Access 2002
    Join Date
    Apr 2012
    Posts
    11
    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...

  13. #13
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    53,632
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Iff query with date calc?
    By technet in forum Queries
    Replies: 3
    Last Post: 02-08-2012, 10:17 AM
  2. calc field
    By nashr1928 in forum Forms
    Replies: 8
    Last Post: 11-09-2011, 09:21 PM
  3. Calc fields
    By nashr1928 in forum Forms
    Replies: 5
    Last Post: 02-26-2011, 08:35 PM
  4. F1 Context sensitive help in Access 2002
    By NOTLguy in forum Programming
    Replies: 6
    Last Post: 10-25-2010, 02:50 PM
  5. Replies: 3
    Last Post: 11-28-2009, 11:21 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums