|
|
I have what seems to be simple issue - but can find no documentation on this.
The refreshing of forms doesn't seem to follow the order of VBA code. Or
maybe the issue is that the speed at which things refresh cause it to appear
that the order is different. Here is the code:
Private Sub Returned_AfterUpdate()
Dim curCredit As Integer
Me.Refresh
curCredit = Forms![General]![frmContactReturn]![frmSaleInfo].Form![Credit]
Forms![General]![frmReturnSales].Form![curCreditOwed] = curCredit
Forms![General]![frmReturnSales].Form.Refresh
End Sub
Problem: The field [Credit] updates after the Form frmReturnSales
refreshes. I have a control on frmReturnSales that shows the value in the
control [Credit] but it actually shows the "old" value, not the "new"
calculated value once the Me.Refresh event occurs. In other words the
calculated control is always one step behind in displaying the value.
More info: The form [General] has two forms and these forms have subforms.
It is these two subforms that hold the controls I am trying to "link" the
values between.
Any help is appreciated. I'm already pretty bald and getting hariless as we
speak :)..
|
|
"blitzn" <blitzn[ at ]discussions.microsoft.com> wrote in message
news:C379ADF0-A02E-4033-ACB6-9E02E9C918C5[ at ]microsoft.com...
[Quoted Text] >I have what seems to be simple issue - but can find no documentation on
>this.
>
> The refreshing of forms doesn't seem to follow the order of VBA code. Or
> maybe the issue is that the speed at which things refresh cause it to
> appear
> that the order is different. Here is the code:
>
> Private Sub Returned_AfterUpdate()
> Dim curCredit As Integer
> Me.Refresh
> curCredit = Forms![General]![frmContactReturn]![frmSaleInfo].Form![Credit]
> Forms![General]![frmReturnSales].Form![curCreditOwed] = curCredit
> Forms![General]![frmReturnSales].Form.Refresh
> End Sub
>
> Problem: The field [Credit] updates after the Form frmReturnSales
> refreshes. I have a control on frmReturnSales that shows the value in the
> control [Credit] but it actually shows the "old" value, not the "new"
> calculated value once the Me.Refresh event occurs. In other words the
> calculated control is always one step behind in displaying the value.
>
> More info: The form [General] has two forms and these forms have
> subforms.
> It is these two subforms that hold the controls I am trying to "link" the
> values between.
>
> Any help is appreciated. I'm already pretty bald and getting hariless as
> we
> speak :)..
I'm not sure I understand your setup and what you are trying to accomplish.
I suspect also that you are using Refresh when you may need to use Recalc or
Requery, or some other method.
This code you posted, for the AfterUpdate event of a control named
"Returned" -- what form is it on? Is it on the "General" form, or on one of
the subforms? If a subform, which subform?
From what I can see, you have this form/subform arrangement:
General
frmContactReturn
frmSaleInfo
frmReturnSales
Is that right?
Is Forms![General]![frmContactReturn]![frmSaleInfo].Form![Credit] a
calculated field? Are you calling the Refresh method to make it
recalculate? Is the Returned control (whose AfterUpdate event this code is
in) part of that calculation?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
Dirk,
Thanks for the interst/help. Let me try to answer your questions.
The General form has two subforms (frmContactReturn and frmReturnSales).
frmContactReturn has a subform on it named frmsalesInfo. This form shows
recent sales to a contact and has a check box control named Returned and a
calculated control named Credit which sums the credit owed to a customer for
returned goods. The form frmReturnSales has a subform named SalesInput
(which I don't think is relevant). On the Form frmReturnSales is an unbound
control named curCreditOwed. I want to show the value from the calculated
control Credit in the unbound control curCreditOwed.
So the sequence I assumed would follow was when the user clicks the Returned
checkbox the afterupdate event would fire, the form frmSalesInfo would
refresh and the value in Credit would update (this part is working well),
then I would assign the value in Credit to the variable curCredit (this works
fine) and then lastly display this value in the unbound control
curCreditOwed.
What actually happens is the control curCreditOwed updates early, that is to
say that before the value in Credit can show the calculation, curCreditOwed
updates.
For example, Credit has a value of 0, curCreditOwed has a value of 0, the
user clicks on Return, the value of cueCreditOwed flickers (as if to update)
and remains 0, then the value in Credit changes to 500 (which is the correct
amount). If the user clicks another record's Returned checkbox, then the
curCreditOwed flickers and the value updates to 500 (the "old" value in
Credit), and then the Credit field updates to 1000.
So it seems that Credit is too slow in updating. It should update to 1000
first, then that value should be assigned to the unbound field curCreditOwed.
I hope this makes more sense.
"Dirk Goldgar" wrote:
[Quoted Text] > "blitzn" <blitzn[ at ]discussions.microsoft.com> wrote in message
> news:C379ADF0-A02E-4033-ACB6-9E02E9C918C5[ at ]microsoft.com...
> >I have what seems to be simple issue - but can find no documentation on
> >this.
> >
> > The refreshing of forms doesn't seem to follow the order of VBA code. Or
> > maybe the issue is that the speed at which things refresh cause it to
> > appear
> > that the order is different. Here is the code:
> >
> > Private Sub Returned_AfterUpdate()
> > Dim curCredit As Integer
> > Me.Refresh
> > curCredit = Forms![General]![frmContactReturn]![frmSaleInfo].Form![Credit]
> > Forms![General]![frmReturnSales].Form![curCreditOwed] = curCredit
> > Forms![General]![frmReturnSales].Form.Refresh
> > End Sub
> >
> > Problem: The field [Credit] updates after the Form frmReturnSales
> > refreshes. I have a control on frmReturnSales that shows the value in the
> > control [Credit] but it actually shows the "old" value, not the "new"
> > calculated value once the Me.Refresh event occurs. In other words the
> > calculated control is always one step behind in displaying the value.
> >
> > More info: The form [General] has two forms and these forms have
> > subforms.
> > It is these two subforms that hold the controls I am trying to "link" the
> > values between.
> >
> > Any help is appreciated. I'm already pretty bald and getting hariless as
> > we
> > speak :)..
>
>
> I'm not sure I understand your setup and what you are trying to accomplish.
> I suspect also that you are using Refresh when you may need to use Recalc or
> Requery, or some other method.
>
> This code you posted, for the AfterUpdate event of a control named
> "Returned" -- what form is it on? Is it on the "General" form, or on one of
> the subforms? If a subform, which subform?
>
> From what I can see, you have this form/subform arrangement:
>
> General
> frmContactReturn
> frmSaleInfo
> frmReturnSales
>
> Is that right?
>
> Is Forms![General]![frmContactReturn]![frmSaleInfo].Form![Credit] a
> calculated field? Are you calling the Refresh method to make it
> recalculate? Is the Returned control (whose AfterUpdate event this code is
> in) part of that calculation?
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
"blitzn" <blitzn[ at ]discussions.microsoft.com> wrote in message
news:045407B9-F991-4B74-8538-9D6DC4A0322D[ at ]microsoft.com...
[Quoted Text] > Dirk,
>
> Thanks for the interst/help. Let me try to answer your questions.
>
> The General form has two subforms (frmContactReturn and frmReturnSales).
> frmContactReturn has a subform on it named frmsalesInfo. This form shows
> recent sales to a contact and has a check box control named Returned and a
> calculated control named Credit which sums the credit owed to a customer
> for
> returned goods. The form frmReturnSales has a subform named SalesInput
> (which I don't think is relevant). On the Form frmReturnSales is an
> unbound
> control named curCreditOwed. I want to show the value from the calculated
> control Credit in the unbound control curCreditOwed.
So the controls [Returned] and [Credit] are both on the subsubform,
[frmSalesInfo]? What is the ControlSource expression for [Credit]?
> So the sequence I assumed would follow was when the user clicks the
> Returned
> checkbox the afterupdate event would fire, the form frmSalesInfo would
> refresh and the value in Credit would update (this part is working well),
Are you refreshing the form so as to force the record to be saved? Or just
to get the [Credit] control to be recalculated? If saving the current
record isn't necessary, you can force a recalc without saving the record,
which may be faster. If you need to save the record, there's a more
efficient method than Refresh.
> then I would assign the value in Credit to the variable curCredit (this
> works
> fine)
It does? I thought you were saying that it didn't, that curCredit is
picking up the "old" value.
> and then lastly display this value in the unbound control
> curCreditOwed.
>
> What actually happens is the control curCreditOwed updates early, that is
> to
> say that before the value in Credit can show the calculation,
> curCreditOwed
> updates.
>
> For example, Credit has a value of 0, curCreditOwed has a value of 0, the
> user clicks on Return, the value of cueCreditOwed flickers (as if to
> update)
> and remains 0, then the value in Credit changes to 500 (which is the
> correct
> amount). If the user clicks another record's Returned checkbox, then the
> curCreditOwed flickers and the value updates to 500 (the "old" value in
> Credit), and then the Credit field updates to 1000.
>
> So it seems that Credit is too slow in updating. It should update to 1000
> first, then that value should be assigned to the unbound field
> curCreditOwed.
I have an idea how to solve this problem, but I need to know the questions I
asked above:
1. Are the controls [Returned] and [Credit] both on the subsubform,
[frmSalesInfo]?
2. What is the ControlSource expression for [Credit]?
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
1. Are the controls [Returned] and [Credit] both on the subsubform,
[Quoted Text] > [frmSalesInfo]? Yes, but [Credit] is in the form header and [Returned] is in the details section.
2. What is the ControlSource expression for [Credit]?
=Sum(IIf([Returned]=True,([Tax Paid]+[Price]),0))
Thanks Dirk
|
|
Dirk,
i noticed the mid post question:
Are you refreshing the form so as to force the record to be saved? Or just
[Quoted Text] > to get the [Credit] control to be recalculated? If saving the current
> record isn't necessary, you can force a recalc without saving the record,
> which may be faster. If you need to save the record, there's a more
> efficient method than Refresh.
I'm refreshing to get the control to calculate, I do not need to save the
record at this time.
"Dirk Goldgar" wrote:
> "blitzn" <blitzn[ at ]discussions.microsoft.com> wrote in message
> news:045407B9-F991-4B74-8538-9D6DC4A0322D[ at ]microsoft.com...
> > Dirk,
> >
> > Thanks for the interst/help. Let me try to answer your questions.
> >
> > The General form has two subforms (frmContactReturn and frmReturnSales).
> > frmContactReturn has a subform on it named frmsalesInfo. This form shows
> > recent sales to a contact and has a check box control named Returned and a
> > calculated control named Credit which sums the credit owed to a customer
> > for
> > returned goods. The form frmReturnSales has a subform named SalesInput
> > (which I don't think is relevant). On the Form frmReturnSales is an
> > unbound
> > control named curCreditOwed. I want to show the value from the calculated
> > control Credit in the unbound control curCreditOwed.
>
> So the controls [Returned] and [Credit] are both on the subsubform,
> [frmSalesInfo]? What is the ControlSource expression for [Credit]?
>
> > So the sequence I assumed would follow was when the user clicks the
> > Returned
> > checkbox the afterupdate event would fire, the form frmSalesInfo would
> > refresh and the value in Credit would update (this part is working well),
>
> Are you refreshing the form so as to force the record to be saved? Or just
> to get the [Credit] control to be recalculated? If saving the current
> record isn't necessary, you can force a recalc without saving the record,
> which may be faster. If you need to save the record, there's a more
> efficient method than Refresh.
>
> > then I would assign the value in Credit to the variable curCredit (this
> > works
> > fine)
>
> It does? I thought you were saying that it didn't, that curCredit is
> picking up the "old" value.
>
> > and then lastly display this value in the unbound control
> > curCreditOwed.
> >
> > What actually happens is the control curCreditOwed updates early, that is
> > to
> > say that before the value in Credit can show the calculation,
> > curCreditOwed
> > updates.
> >
> > For example, Credit has a value of 0, curCreditOwed has a value of 0, the
> > user clicks on Return, the value of cueCreditOwed flickers (as if to
> > update)
> > and remains 0, then the value in Credit changes to 500 (which is the
> > correct
> > amount). If the user clicks another record's Returned checkbox, then the
> > curCreditOwed flickers and the value updates to 500 (the "old" value in
> > Credit), and then the Credit field updates to 1000.
> >
> > So it seems that Credit is too slow in updating. It should update to 1000
> > first, then that value should be assigned to the unbound field
> > curCreditOwed.
>
> I have an idea how to solve this problem, but I need to know the questions I
> asked above:
>
> 1. Are the controls [Returned] and [Credit] both on the subsubform,
> [frmSalesInfo]?
>
> 2. What is the ControlSource expression for [Credit]?
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
"blitzn" <blitzn[ at ]discussions.microsoft.com> wrote in message
news:65D2018C-EAE2-416D-9724-79E214055D9F[ at ]microsoft.com...
[Quoted Text] > 1. Are the controls [Returned] and [Credit] both on the subsubform,
>> [frmSalesInfo]? Yes, but [Credit] is in the form header and [Returned]
>> is in the details section.
>
> 2. What is the ControlSource expression for [Credit]?
> =Sum(IIf([Returned]=True,([Tax Paid]+[Price]),0))
Okay, then you do need to force the record to be saved, so that the updated
record is available to the Sum() function.
Try this and tell me if it works.
'------ start of revised code -----
Private Sub Returned_AfterUpdate()
Dim curCredit As Currency
' Force record to be saved.
Me.Dirty = False
' Force recalculation of calculated Credit control.
Me!Credit.Requery
curCredit = Me!Credit
With Forms![General]![frmReturnSales].Form
!curCreditOwed = curCredit
'*** NOTE: NOT SURE YOU NEEED THE NEXT LINE
.Refresh
End With
End Sub
'------ end of revised code -----
As noted above, I'm not sure you need to refresh the frmReturnSales subform,
or even save the record at this time. But before looking into that
question, I'd like to know whether the above code works.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
Dirk,
Thank you for the code, it appears to be very close, but not quite working
yet. I can see that the timing of the "flikers" or updates is different and
this is hopeful. I put a watch on the variable and it is in fact not
updating properly (my bad) - when [Return] is checked, the variable
[curCredit] seems to be taking the value of [Credit] prior to the
Me.Credit.Requery event fires. [Credit] eventually updates and displayes
the correct value, but [curCreditOwed] does not.
"Dirk Goldgar" wrote:
[Quoted Text] > "blitzn" <blitzn[ at ]discussions.microsoft.com> wrote in message
> news:65D2018C-EAE2-416D-9724-79E214055D9F[ at ]microsoft.com...
> > 1. Are the controls [Returned] and [Credit] both on the subsubform,
> >> [frmSalesInfo]? Yes, but [Credit] is in the form header and [Returned]
> >> is in the details section.
> >
> > 2. What is the ControlSource expression for [Credit]?
> > =Sum(IIf([Returned]=True,([Tax Paid]+[Price]),0))
>
>
> Okay, then you do need to force the record to be saved, so that the updated
> record is available to the Sum() function.
>
> Try this and tell me if it works.
>
> '------ start of revised code -----
> Private Sub Returned_AfterUpdate()
>
> Dim curCredit As Currency
>
> ' Force record to be saved.
> Me.Dirty = False
>
> ' Force recalculation of calculated Credit control.
> Me!Credit.Requery
>
> curCredit = Me!Credit
>
> With Forms![General]![frmReturnSales].Form
> !curCreditOwed = curCredit
> '*** NOTE: NOT SURE YOU NEEED THE NEXT LINE
> .Refresh
> End With
>
> End Sub
> '------ end of revised code -----
>
> As noted above, I'm not sure you need to refresh the frmReturnSales subform,
> or even save the record at this time. But before looking into that
> question, I'd like to know whether the above code works.
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
"blitzn" <blitzn[ at ]discussions.microsoft.com> wrote in message
news:C40D62DF-9BD7-4F1F-AA3C-A2F78ED47CA5[ at ]microsoft.com...
[Quoted Text] > Dirk,
>
> Thank you for the code, it appears to be very close, but not quite working
> yet. I can see that the timing of the "flikers" or updates is different
> and
> this is hopeful. I put a watch on the variable and it is in fact not
> updating properly (my bad) - when [Return] is checked, the variable
> [curCredit] seems to be taking the value of [Credit] prior to the
> Me.Credit.Requery event fires. [Credit] eventually updates and displayes
> the correct value, but [curCreditOwed] does not.
Okay, let's try adding one more line of code, to make Access let competing
execution threads to finish before pulling the value from the calculated
control. Insert the statement "DoEvents" after "Me!Credit.Requery":
'-------- start of revised code snippet -------
' Force recalculation of calculated Credit control.
Me!Credit.Requery
DoEvents
curCredit = Me!Credit
'-------- end of revised code snippet -------
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
Dirk,
Thanks for hangin' in. The DoEvents code didn't change anything. I've
noticed one thing that seems odd to me. I wrote a line of code to force an
error. In debug mode when I mouse over the curCredit variable it displays a
different value then when I mouse over Me.Credit. If the code has already
executed past this point in the procedure I would think these values (based
on the line of code "curCredit = Me.Credit") should be equal.
I'm plum out of ideas. I would think this should be easy but I've spent
about 4 hours on this today!
"Dirk Goldgar" wrote:
[Quoted Text] > "blitzn" <blitzn[ at ]discussions.microsoft.com> wrote in message
> news:C40D62DF-9BD7-4F1F-AA3C-A2F78ED47CA5[ at ]microsoft.com...
> > Dirk,
> >
> > Thank you for the code, it appears to be very close, but not quite working
> > yet. I can see that the timing of the "flikers" or updates is different
> > and
> > this is hopeful. I put a watch on the variable and it is in fact not
> > updating properly (my bad) - when [Return] is checked, the variable
> > [curCredit] seems to be taking the value of [Credit] prior to the
> > Me.Credit.Requery event fires. [Credit] eventually updates and displayes
> > the correct value, but [curCreditOwed] does not.
>
> Okay, let's try adding one more line of code, to make Access let competing
> execution threads to finish before pulling the value from the calculated
> control. Insert the statement "DoEvents" after "Me!Credit.Requery":
>
> '-------- start of revised code snippet -------
> ' Force recalculation of calculated Credit control.
> Me!Credit.Requery
>
> DoEvents
>
> curCredit = Me!Credit
>
> '-------- end of revised code snippet -------
>
> --
> Dirk Goldgar, MS Access MVP
> www.datagnostics.com
>
> (please reply to the newsgroup)
>
>
|
|
"blitzn" <blitzn[ at ]discussions.microsoft.com> wrote in message
news:DA9ACE76-7558-45E6-8C68-A2B4A5FF278C[ at ]microsoft.com...
[Quoted Text] > Dirk,
>
> Thanks for hangin' in. The DoEvents code didn't change anything. I've
> noticed one thing that seems odd to me. I wrote a line of code to force
> an
> error. In debug mode when I mouse over the curCredit variable it displays
> a
> different value then when I mouse over Me.Credit. If the code has already
> executed past this point in the procedure I would think these values
> (based
> on the line of code "curCredit = Me.Credit") should be equal.
>
> I'm plum out of ideas. I would think this should be easy but I've spent
> about 4 hours on this today!
Would you be interested in having me look at the database? I'd be willing
to have you send me a copy, preferably cut down to just the elements
necessary to demonstrate the problem. If you want to send it, please
compact the copy and zip it so it isn't too big, and I'll have a look at it,
time permitting. You can send it to the address derived by removing NO SPAM
and ".invalid" from the reply address of this message. If that address
isn't visible to you, you can get my address from my web site, which is
listed in my sig. Do *not* post my real address in the newsgroup -- I don't
want to be buried in spam and viruses.
--
Dirk Goldgar, MS Access MVP
www.datagnostics.com
(please reply to the newsgroup)
|
|
|