Results 1 to 9 of 9
  1. #1
    jas0501's Avatar
    jas0501 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    15

    Issue of Control Refering to subform total control - You entered an expression that has no value

    Issue: How to deal with timing issues around controls that refer to subform controls that are computed DCount() or SUM()

    Goal:
    Highlight control, txt_valueOfInterest, when value does is off.
    Approach:
    a rectangle underneath the control, box_valueOfInterestAlert, is made visible with the control off

    Sub displayAlerts()
    if me.txt_valueOfInterest > 0 then
    ' this is the line that sometimes has no value
    me.box_valofOfInterestAlert.visible = true
    else
    me.box_valofOfInterestAlert.visible = true
    endif
    end sub


    txt_valueOfInterest refers to a subform total field, Control source = [_subfrm].[Form]![txt_countOfproblems] which is a computed sum or dcount.The subform is in datasheet view and the filtered base on the form value. When editing the records in the subform I want to the parent form displayAlerts() to be called to refresh the display and indicate issues.

    The problem is that there is a timing issue and txt_valueOfInterest does not immediately get the corrected value, and sometimes has no value.

    My attempt was from the subform to cause the parent form to process a timer event. The timing is unclear to 3 timers seem to cover the delay, but sometimes fails.

    in the subform after update on the control of interest:

    Me.Parent.TimerInterval = 900

    and then in the parent form's timer()



    Private Sub Form_Timer()
    Me.TimerInterval = Me.TimerInterval - 300
    displayAlerts()
    If Me.TimerInterval <= 0 Then
    Me.TimerInterval = 0
    End If
    end sub

    This works a good portion of the time but periodically displayAlerts() catches txt_valueOfInterest with no value.

    Can anyone suggest a better approach?

  2. #2
    JeffChr is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Feb 2014
    Posts
    82
    maybe do your computations in a query, if possible.

  3. #3
    jas0501's Avatar
    jas0501 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    15
    Given that the control in the parent form is dependent on the value in the subform and editing the subform is changing the result I think a query suffers from the same timing issue.
    There would still be the issue of when to run the query to ensure that the result had been computed in the subform. Also what would be the trigger in the parent form to initiate the query.

  4. #4
    jas0501's Avatar
    jas0501 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    15

    Modified approach seems to work.

    As a modification to the approach outlined I added error handling in the displayAlerts() and on error incremented form.timerInterval by 300. This forces an additional iteration of displayAlerts() and gets past any "no value" situations brought about by the subform computation being in flight.

    I didn't mention that the setting of timerInterval to 900 decremented by 300 was arrived at be fiddling and my not be optimal. The requirement is that enough time has elapsed for the subform to have completed the sum and the parent form to have acquired the new value. To quick and the old value is used or the "no value" is encountered.

    I'd love to find a cleaner approach.

  5. #5
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Was this issue ever resolved? I'm having the same problem when using a Sum() field on a subform that is not getting updated in a requery loop.

  6. #6
    jas0501's Avatar
    jas0501 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    15
    Update:
    The solution I arrived at is a bit convoluted, but it works.
    In the parent form I added timer code:
    Code:
    Private Sub Form_Timer()
    Static counter As Integer
        If IsNull(Me.txt_PrevSubFormDate) Or Me.txt_PrevSubFormDate <> Me.txt_SubFormDate Then
            viewCheck
            Me.txt_PrevSubFormDate = Me.txt_SubFormDate
            Me.TimerInterval = 250
            counter = 0
        End If
        counter = counter + 1
        If counter = 10 Then
            TimerInterval = 0
        End If
    End Sub
    where viewcheck is checking for items to highlight.

    The Me.txt_SubFormDate control source is
    Code:
    =[_subfrm_Courses].[Form]![txt_WhenChanged]
    ------------------------------------------

    In the subform for controls of interest I added After Update and Form On Delete events that included calls to whenChanged

    Code:
    Sub whenChanged()
        Me.txt_WhenChanged = Now()
        Me.Parent.TimerInterval = someNonZeroTimerSetting 
    End Sub

    These instrumented controls in the subform will trigger a viewcheck in the parent form. The timer looping is to ensure the timer loop doesn't beat out the system's handling of the parent form's =Sum() processing.

    It is definitely an ugly approach, and suffers from some telltale screen flicker, but it does get the job done.

    ------------------------------------
    The short story is use Form_Timer() in the parent form to loop, doing the viewcheck, in conjunction with changes in the subform triggering the timer in the parent form.

    If someone has a cleaner approach please share.

  7. #7
    jas0501's Avatar
    jas0501 is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Dec 2014
    Posts
    15
    I forgot to mention on one refinement. The whenChanged was changed to be a function

    Code:
    Function whenChanged()
        Me.txt_WhenChanged = Now()
        Me.Parent.TimerInterval = someNonZeroTimerSetting 
       whenChanged = 1
    End Function
    and could then be used in Control Source formulas, i.e.

    Code:
    =Sum([Team%]*[Credits]*[TeachingFactor])*whenChanged()
    to trigger the parent's FormTimer()

  8. #8
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    Thanks, Jas, for the quick response. I, too, implemented a "stall" timer to make my code work, which is ironic because I spent a good amount of time refining my query and code in order to make it run faster only to encounter this problem.

  9. #9
    klnlsu is offline Competent Performer
    Windows 7 64bit Access 2013
    Join Date
    Apr 2015
    Posts
    107
    UPDATE: I removed the sum total from my subform and created a second subform to display the total. The data source for the second subform is an aggregate query that sums the data of the query for the first subform. It seems to work much better.

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

Similar Threads

  1. Replies: 10
    Last Post: 02-20-2013, 07:04 AM
  2. Total Control Value in SubForm
    By libraccess in forum Forms
    Replies: 4
    Last Post: 06-06-2012, 01:34 AM
  3. Replies: 3
    Last Post: 03-29-2012, 12:40 PM
  4. Replies: 5
    Last Post: 10-13-2011, 03:36 PM
  5. Replies: 1
    Last Post: 11-25-2010, 11:00 AM

Tags for this Thread

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