Subform updating mainform lag by one action 1

rafe · Mar 8, 2001

OK I'm having a bit of brain fade b/c I must be missing the obvious here

I'm trying to update a form description based upon totals in the subform header. SumOfQty, SumOfAmt, MinOfFromDate, MaxOfToDate, AvgOfRate in the subform are used to build up a string used in document headers. This built up string is a default value that sometimes needs to be changed by the user (me).

Problem: if i change Qty which then changes sumOfQty then nothing is seen in mainform's header except literals. If then change the Rate which changes avgOfRate then the literals & sumOfqty will be in the header but not the new avgRate. & so on.

I'm using the subForm's AfterUpdate event to pass the changes. & upon each of the relevant subform fields i'm doing a

Code:

    DoCmd.RunCommand acCmdSaveRecord

The modules are small so I'll include relevant parts here.

main form's code....

Code:

Option Compare Database
Option Explicit

Private Sub Documents_PayeeID_AfterUpdate()
    '....for set up stuff deleted....
    Me.SubForm.SetFocus
    DoCmd.GoToControl &quot;Index&quot;
End Sub

Private Sub Form_Current()
    '...not relevant code...
End Sub

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
End Sub

Private Sub UpdateHeader() 'What I'm trying to accomplish... but not called in this module
    Me.Header = &quot;Consulting &quot; _
        & &quot; from &quot; & Format(Me.SubForm.Form.MinOfFromDate, &quot;Medium Date&quot;) _
        & &quot; to &quot; & Format(Me.SubForm.Form.MaxOfToDate, &quot;Medium Date&quot;) _
        & &quot; (&quot; & Format(Me.SubForm.Form.AvgOfRate, &quot;Currency&quot;) _
        & &quot; x &quot; & Format(Me.SubForm.Form.SumOfQuantity, &quot;#,##0.00&quot;) & &quot;)&quot;
End Sub

SUBFORM'S CODE

Code:

Option Compare Database
Option Explicit

Private Sub ToDate_BeforeUpdate(Cancel As Integer)
    '...validations deleted...
End Sub

Private Sub Rate_AfterUpdate()
    Me.Amount = Me.Rate * Me.Quantity
    UpdateDescription
End Sub

Private Sub Quantity_AfterUpdate()
    Me.Amount = Me.Rate * Me.Quantity
    UpdateDescription
End Sub

Private Sub FromDate_AfterUpdate()
    UpdateDescription
End Sub

Private Sub ToDate_AfterUpdate()
    UpdateDescription
End Sub

Private Sub Index_AfterUpdate()
    Me.Fund = DLookup(&quot;[Fund]&quot;, &quot;Funds&quot;, _
        &quot;[Index]=&quot; & Me.Index & &quot; AND [FromDate]<=#&quot; & date _
        & &quot;# AND [ToDate]>=#&quot; & date & &quot;#&quot;)
    UpdateDescription
End Sub

Private Sub UpdateDescription()
    DoCmd.RunCommand acCmdSaveRecord
    Me.Description = &quot;Consulting&quot; _
        & &quot; for &quot; & Trim(Me.Index.Column(1)) & &quot; project&quot; _
        & &quot; from &quot; & Format(Me.FromDate, &quot;Medium Date&quot;) _
        & &quot; to &quot; & Format(Me.ToDate, &quot;Medium Date&quot;) _
        & &quot; (&quot; & Format(Me.Rate, &quot;Currency&quot;) _
        & &quot; x &quot; & Format(Me.Quantity, &quot;#,##0.00&quot;) & &quot;)&quot;
End Sub

Private Sub Form_AfterUpdate()
    Me.Parent.Form.Header = &quot;Consulting &quot; _
        & &quot; from &quot; & Format(Me.MinOfFromDate, &quot;Medium Date&quot;) _
        & &quot; to &quot; & Format(Me.MaxOfToDate, &quot;Medium Date&quot;) _
        & &quot; (&quot; & Format(Me.AvgOfRate, &quot;Currency&quot;) _
        & &quot; x &quot; & Format(Me.SumOfQuantity, &quot;#,##0.00&quot;) & &quot;)&quot;
End Sub

ghubbell · Mar 8, 2001

Try tossing this in to your UpdateHeader sub as the first line:
DoCmd.RunCommand acCmdSaveRecord

and remove it from the subForm's AfterUpdate event. I think this might work...
Gord
ghubbell@total.net

rafe · Mar 8, 2001

next stupid question...

so when should invoke that updateHeader sub?

updateHeader sub is actually disabled in the main form's code. it was there when i was trying different scenarios based upon changes to hidden controls in the main form that had a control source linked to the subform's total controls in the footer... but there doesn't seem to be an event you can trap when a value is changed programmatically.

i'm chasing my tail

btw: thanks for catching this thread.

ghubbell · Mar 8, 2001

I would say just as you have it: any of your fields would make a change to the results so on any should call your sub. Only problem is it may cause your cursor to bounce around the record... Gord
ghubbell@total.net

rafe · Mar 8, 2001

i put the following in the main form's code and set the main form's Me.Header's AfterUpdate event to point to it. but same thing it still lags by one update.

Code:

Private Sub Header_AfterUpdate()
    DoCmd.RunCommand acCmdSaveRecord
    Me.Header = &quot;Consulting &quot; _
        & &quot; from &quot; & Format(Me.SubForm.Form.MinOfFromDate, &quot;Medium Date&quot;) _
        & &quot; to &quot; & Format(Me.SubForm.Form.MaxOfToDate, &quot;Medium Date&quot;) _
        & &quot; (&quot; & Format(Me.SubForm.Form.AvgOfRate, &quot;Currency&quot;) _
        & &quot; x &quot; & Format(Me.SubForm.Form.SumOfQuantity, &quot;#,##0.00&quot;) & &quot;)&quot;
End Sub

ghubbell · Mar 8, 2001

We got a bit crossed up there... Place your "UpdateHeader" sub in your subforms module, and what is in your header? Unless you're speaking of a textbox named "Header" (not good practice i'm afraid), were on completely different paths here. Try this (or similar) and remove other stuff from your main form...

Private Sub UpdateHeader()
Me.Parent![NameOfTheTextBoxYouWantToUpdate] = "Consulting " _
& " from " & Format(Me.MinOfFromDate, "Medium Date&quot

_
& " to " & Format(Me.MaxOfToDate, "Medium Date&quot

_
& " (" & Format(Me.AvgOfRate, "Currency&quot

_
& " x " & Format(Me.SumOfQuantity, "#,##0.00&quot

& &quot

"
End Sub Gord
ghubbell@total.net

rafe · Mar 8, 2001

Sorry for the confusion...

I’m afraid that the "not good practice I’m afraid" is in fact the case yes the text box in the main form IS named Me.Header. Made sense at the time (that's the field name too) but i DO see your point with 20/20 hindsight. I have to laugh at myself, but I apologize to you

I’ve stripped out the code from the Main Form & made the notational change. But the updates are still lagging.

ghubbell · Mar 8, 2001

Silly aren't we. did we forget this again?:
DoCmd.RunCommand acCmdSaveRecord
as the first line? My mistake, sorry. And sorry for delayed reply. Keep trying, I want this to work! Gord
ghubbell@total.net

rafe · Mar 8, 2001

Not at all... & thanks for sticking with it!

BTW: You seem to be a recent but a most welcome addition to the boards. Glad to have your help. & You've been quite busy.

Ditto on that wanting it to work idea. Altho to be honest about it I can code a work-around -- double click on Me.Header in main form will do the update OK if code is moved there -- it's one of those extra step things that I bothers me & if I forget the extra step the form gets routed to the trash. Ahh College admin. gotta love it.

Anyway... here's where we're at... same symptoms

Main form code:

Code:

Option Compare Database
Option Explicit

Private Sub Documents_PayeeID_AfterUpdate()
    If Me.Fringe Then
        Me.Documents_Type = ccfDocuments_Type.PAF
        Me.SubForm.Form.Account.DefaultValue = &quot;6231&quot;
        Me.ApprovedBy = DFirst(&quot;[PayeeID]&quot;, &quot;Payees&quot;, &quot;[CenterDir]=Yes&quot;)
    Else
        Me.Documents_Type = ccfDocuments_Type.CheckReq
        Me.SubForm.Form.Account.DefaultValue = &quot;7211&quot;
        Me.ApprovedBy = DFirst(&quot;[PayeeID]&quot;, &quot;Payees&quot;, &quot;[AdminAssoc]=Yes&quot;)
    End If
    Me.Function = ccfDocuments_Function.Consulting
    Me.SubForm.Form.Function.DefaultValue = ccfExpenses_Function.Consulting
    Me.PAFCode = 50
    Me.SubForm.Form.Rate.DefaultValue = Nz(Me.Base, 0)
    Me.SubForm.SetFocus
    DoCmd.GoToControl &quot;Index&quot;
End Sub

Private Sub Form_Current()
    If Nz(Me.Documents_PayeeID, 0) Then
        Me.SubForm.Enabled = True
    Else
        Me.SubForm.Enabled = False
    End If
    Me.Documents_PayeeID.SetFocus
End Sub

Private Sub Form_Load()
    DoCmd.GoToRecord , , acNewRec
End Sub

SUBFORM CODE

Code:

Option Compare Database
Option Explicit

Private Sub ToDate_BeforeUpdate(Cancel As Integer)
    Dim str As String
    Dim mn As Date, mx As Date

    str = &quot;&quot;
    If Nz(Me.Parent.Form.Documents_PayeeID, &quot;&quot;) <> &quot;&quot; Then _
        str = str & &quot;[PayeeID]=&quot; & Me.Parent.Form.Documents_PayeeID & &quot; &quot;
    If Nz(Me.Function, &quot;&quot;) <> &quot;&quot; Then
        If str <> &quot;&quot; Then str = str & &quot; AND &quot;
        str = str & &quot;[Function]='&quot; & Me.Function & &quot;' &quot;
    End If
    If Nz(Me.Index, &quot;&quot;) <> &quot;&quot; Then
        If str <> &quot;&quot; Then str = str & &quot; AND &quot;
        str = str & &quot; [Index]=&quot; & Me.Index
    End If

    mn = Nz(DLookup(&quot;[MinOfFromDate]&quot;, &quot;GetPaymentDateRange&quot;, str), #1/1/1970#)
    mx = Nz(DLookup(&quot;[MaxOfToDate]&quot;, &quot;GetPaymentDateRange&quot;, str), #1/1/1970#)
    If (Me.FromDate >= mn And Me.FromDate <= mx) _
        Or (Me.ToDate >= mn And Me.ToDate <= mx) Then _
        If MsgBox(&quot;The entered dates overlap with a previous payment.&quot; _
            & vbCrLf & &quot;Are you sure you want to do this?&quot; _
            , vbYesNo) = vbNo Then Cancel = True
End Sub

Private Sub Rate_AfterUpdate()
    Me.Amount = Me.Rate * Me.Quantity
    UpdateDescription
End Sub

Private Sub Quantity_AfterUpdate()
    Me.Amount = Me.Rate * Me.Quantity
    UpdateDescription
End Sub

Private Sub FromDate_AfterUpdate()
    UpdateDescription
End Sub

Private Sub ToDate_AfterUpdate()
    UpdateDescription
End Sub

Private Sub Index_AfterUpdate()
    Me.Fund = DLookup(&quot;[Fund]&quot;, &quot;Funds&quot;, _
        &quot;[Index]=&quot; & Me.Index & &quot; AND [FromDate]<=#&quot; & date _
        & &quot;# AND [ToDate]>=#&quot; & date & &quot;#&quot;)
    UpdateDescription
End Sub

Private Sub UpdateDescription()
    DoCmd.RunCommand acCmdSaveRecord
    Me.Description = &quot;Consulting&quot; _
        & &quot; for &quot; & Trim(Me.Index.Column(1)) & &quot; project&quot; _
        & &quot; from &quot; & Format(Me.FromDate, &quot;Medium Date&quot;) _
        & &quot; to &quot; & Format(Me.ToDate, &quot;Medium Date&quot;) _
        & &quot; (&quot; & Format(Me.Rate, &quot;Currency&quot;) _
        & &quot; x &quot; & Format(Me.Quantity, &quot;#,##0.00&quot;) & &quot;)&quot;
End Sub

Private Sub Form_AfterUpdate()
    Me.Parent![Header] = &quot;Consulting &quot; _
        & &quot; from &quot; & Format(Me.MinOfFromDate, &quot;Medium Date&quot;) _
        & &quot; to &quot; & Format(Me.MaxOfToDate, &quot;Medium Date&quot;) _
        & &quot; (&quot; & Format(Me.AvgOfRate, &quot;Currency&quot;) _
        & &quot; x &quot; & Format(Me.SumOfQuantity, &quot;#,##0.00&quot;) & &quot;)&quot;
End Sub

ghubbell · Mar 8, 2001

Thanks Rafe,
I'll have to pick this one up in an hour or two however perhaps someone else will step in in my absense and club us both! TTYL Gord
ghubbell@total.net

ghubbell · Mar 8, 2001

Rafe, I think I've got it. (nice clean code when you throw it in vb by the way)! :
Try this:
Option Compare Database
Option Explicit
Private Sub Documents_PayeeID_AfterUpdate()
If Me.Fringe Then
Me.Documents_Type = ccfDocuments_Type.PAF
Me.SubForm.Form.Account.DefaultValue = "6231"
Me.ApprovedBy = DFirst("[PayeeID]", "Payees", "[CenterDir]=Yes&quot

Else
Me.Documents_Type = ccfDocuments_Type.CheckReq
Me.SubForm.Form.Account.DefaultValue = "7211"
Me.ApprovedBy = DFirst("[PayeeID]", "Payees", "[AdminAssoc]=Yes&quot

End If
Me.Function = ccfDocuments_Function.Consulting
Me.SubForm.Form.Function.DefaultValue = ccfExpenses_Function.Consulting
Me.PAFCode = 50
Me.SubForm.Form.Rate.DefaultValue = Nz(Me.Base, 0)
Me.SubForm.SetFocus
DoCmd.GoToControl "Index"
End Sub
Private Sub Form_Current()
If Nz(Me.Documents_PayeeID, 0) Then
Me.SubForm.Enabled = True
Else
Me.SubForm.Enabled = False
End If
Me.Documents_PayeeID.SetFocus
End Sub
Private Sub Form_Load()
DoCmd.GoToRecord , , acNewRec
End Sub
'SubForm CODE
'Option Compare Database
'Option Explicit
Private Sub ToDate_BeforeUpdate(Cancel As Integer)
Dim str As String
Dim mn As Date, mx As Date
str = ""
If Nz(Me.Parent.Form.Documents_PayeeID, "&quot

<> "" Then str = str & "[PayeeID]=" & Me.Parent.Form.Documents_PayeeID & " "
If Nz(Me.Function, "&quot

<> "" Then
If str <> "" Then str = str & " AND "
str = str & "[Function]='" & Me.Function & "' "
End If
If Nz(Me.Index, "&quot

<> "" Then
If str <> "" Then str = str & " AND "
str = str & " [Index]=" & Me.Index
End If
mn = Nz(DLookup("[MinOfFromDate]", "GetPaymentDateRange", str), #1/1/1970#)
mx = Nz(DLookup("[MaxOfToDate]", "GetPaymentDateRange", str), #1/1/1970#)
If (Me.FromDate >= mn And Me.FromDate <= mx) Or (Me.ToDate >= mn And Me.ToDate <= mx) Then If MsgBox("The entered dates overlap with a previous payment." & vbCrLf & "Are you sure you want to do this?", vbYesNo + vbQuestion + vbDefaultButton2) = vbNo Then Cancel = True
End Sub
Private Sub Rate_AfterUpdate()
Me.Amount = Me.Rate * Me.Quantity
UpdateDescription
End Sub
Private Sub Quantity_AfterUpdate()
Me.Amount = Me.Rate * Me.Quantity
UpdateDescription
End Sub
Private Sub FromDate_AfterUpdate()
UpdateDescription
End Sub
Private Sub ToDate_AfterUpdate()
UpdateDescription
End Sub
Private Sub Index_AfterUpdate()
Me.Fund = DLookup("[Fund]", "Funds", "[Index]=" & Me.Index & " AND [FromDate]<=#" & Date & "# AND [ToDate]>=#" & Date & "#&quot

UpdateDescription
End Sub

'The real change is here:

Private Sub UpdateDescription()
DoCmd.RunCommand acCmdSaveRecord
Me.Description = "Consulting" & " for " & Trim(Me.Index.Column(1)) & " project" & " from " & Format(Me.FromDate, "Medium Date&quot

& " to " & Format(Me.ToDate, "Medium Date&quot

& " (" & Format(Me.Rate, "Currency&quot

& " x " & Format(Me.Quantity, "#,##0.00&quot

& &quot

"
Me.Parent![Header] = "Consulting " & " from " & Format(Me.MinOfFromDate, "Medium Date&quot

& " to " & Format(Me.MaxOfToDate, "Medium Date&quot

& " (" & Format(Me.AvgOfRate, "Currency&quot

& " x " & Format(Me.SumOfQuantity, "#,##0.00&quot

& &quot

"
End Sub

Sorry I really should get some "specs" and am working at home off of little 133's & 200's with small screens, so I stretched out your code for clarity.
If this fails (please no please no) add a hidden text box on your subform and Call it "GordsFix" (Reddick is cringing), and make your "Header" control source:
=[NameOfYourSubFormAsSeenByTheMainForm].Form![GordsFix]
Please tell me it works! or not.... Never quit!

Gord
ghubbell@total.net

rafe · Mar 12, 2001

Got it. It's a race condition! If I put a break on the line that updates the header textbox then it all works OK when I manually allow the program to continue. If I just let it fly on its own then it symtoms as per above because the write op hasn't finished yet or the footer updates(?).

Now the question is how can I get Access to wait for the table write & footer texbox updates to complete?

Any ideas?

rafe · Mar 12, 2001

Got it.

Me.Recalc

It was the footer text boxes that were lagging.

rafe · Mar 13, 2001

In the spirit of full disclosure.

If I add the Me.Recal line to the routine below... bingo problem solved. The Footer text boxes (Me.MinOfFromDate etc.) weren't getting updated before they were getting used by Me.Parent![Header] set operation. By putting the Recalc in Access waits until all controls are recalculated & all is right with the world.

Code:

Private Sub UpdateDescription()
    DoCmd.RunCommand acCmdSaveRecord
    Me.Recalc
    Me.Description = &quot;Consulting&quot; _
        & &quot; for &quot; & Trim(Me.Index.Column(1)) & &quot; project&quot; _
        & &quot; from &quot; & Format(Me.FromDate, &quot;Medium Date&quot;) _
        & &quot; to &quot; & Format(Me.ToDate, &quot;Medium Date&quot;) _
        & &quot; (&quot; & Format(Me.Rate, &quot;Currency&quot;) _
        & &quot; x &quot; & Format(Me.Quantity, &quot;#,##0.00&quot;) & &quot;)&quot;
     Me.Parent![Header] = &quot;Consulting &quot; _
        & &quot; from &quot; & Format(Me.MinOfFromDate, &quot;Medium Date&quot;) _
        & &quot; to &quot; & Format(Me.MaxOfToDate, &quot;Medium Date&quot;) _
        & &quot; (&quot; & Format(Me.AvgOfRate, &quot;Currency&quot;) _
        & &quot; x &quot; & Format(Me.SumOfQuantity, &quot;#,##0.00&quot;) & &quot;)&quot;
End Sub

Problem solved & thanks for the help!

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Subform updating mainform lag by one action 1

rafe

Technical User

ghubbell

Programmer

rafe

Technical User

ghubbell

Programmer

rafe

Technical User

ghubbell

Programmer

rafe

Technical User

ghubbell

Programmer

rafe

Technical User

ghubbell

Programmer

ghubbell

Programmer

rafe

Technical User

rafe

Technical User

rafe

Technical User

Similar threads

Part and Inventory Search

Sponsor