Do something until conditon is met (loop?)

  • Thread starter Thread starter GaryS
  • Start date Start date
G

GaryS

Hello -Using Access 2003 I have the following code in the click event of a
command button.

If Forms!frmlease!TotalDue + Me.txtLicenseFee < Forms!frmlease!SalePrice -
Me.txtSalePrice Then

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

End If

Basically I need the code to run several times until the condition is met (a
loop?). I've tried several if then statements together but it seems the
condition only gets checked once per click, as clicking several times
achieves the result. I know this can be accomplished with some sort of loop,
but I don't understand how to do it. Any help would be greatly appreciated.

Gary in Michigan, USA
 
Gary,

Basically, you want a Do...Loop structure, like so:

Do While Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice -
Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

DoEvents 'Necessary to free up other processes
Loop

But be advised - loops are dangerous things, because they (a) use up
valuable resources, and (b) they can continue forever unless you provide an
'out clause'.

If I were you, I'd be looking at a better way to do this. Obviously you're
introducing a time-based loop - waiting for something to change. What is
triggering the change that you're waiting for?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Gary

You could use a Do While loop but what does the code have to loop through? I
think you are referring to a recordset aren't you?
 
I decided maybe more explanation was required.

FrmLease calculates lease payments. Normally people pay certain costs up
front, but some times I need to roll those costs in to the Sale Price. When
I add to the Sale Price it changes the TotalDue. I need to run the code
(recalculate) 4 times to make amount added to the Sale Price = the TotalDue.
I hope this helps some kind soul make sense out of what I am trying to do.
Thank you
 
Gary,

I see what you mean. Try putting this behind the click event of your button:

While Not Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice - Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

Wend

That should do the trick for ya...

hth
 
Hmm, that apparently starts an endless loop. I modefied your code slightly
by changing the < to <=, but still get lockup, with no change SalePrice when
the code is executed. Is there a way to only have it loop 6 times?
 
It would appear you are right about endless loops. If you look at my 2nd
post you will get more of an idea of what I am trying to do. The execution
of the code should create the event that stops the code, but it doesn't seem
like Access can check during the code to see if TotalDue is changing as the
SalePrice changes.

Thanks!
 
Hi Gary,

I think we need to find a better way to do this. A loop is not a good idea
in this scenario.

Can you explain (as if to a dummy, because I don't know your business),
exactly what each figure is and how the end result is derived?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
GaryS said:
I decided maybe more explanation was required.

FrmLease calculates lease payments. Normally people pay certain costs up
front, but some times I need to roll those costs in to the Sale Price. When
I add to the Sale Price it changes the TotalDue. I need to run the code
(recalculate) 4 times to make amount added to the Sale Price = the TotalDue.
I hope this helps some kind soul make sense out of what I am trying to do.
Click to expand...


Well, I not the one to fo it, but it does make sense. There
are financial people that understand that and have formulas
(without a loop) to calculate that sort of thing. If you
can't find a person that has the formula you need on the tip
of thir tongue, you could always try a decent public
library.

If you persist in using a loop, first write out all the
steps without a loop and see how close you get to the
desired value. Then use the difference (.2 dollars??) as an
acceptable margin of error in the test that should end the
loop. As I said above, even with a relatively small error,
it would be more productive fo you to find someone that can
provide the right formula and get an exact result.
 
Exactly what I was thinking, Marsh.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
 
Gary,

If no conditions are met you are ending up in an endless loop. That's what
Graham and Marshall are pointing out to you.

if you want your loop to only run six times you could try:


dim iCount as integer

iCount=0
Do While Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice -
Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

icount=icount+1
DoEvents 'Necessary to free up other processes

if icount=6 then exit do
Loop

see of that get's you anywhere. Remember the posts from Graham and Marshall
stating you rethink tour strategy (what if the next time it should run for 8
times? you would be changing your code constantly).

btw you also have to do the proper error handling as well in this situation...

hth
 
--
Gary in Michigan, USA


Graham R Seach said:
Hi Gary,

I think we need to find a better way to do this. A loop is not a good idea
in this scenario.

Can you explain (as if to a dummy, because I don't know your business),
exactly what each figure is and how the end result is derived?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia
Click to expand...
 
Maurice, your 6 count loop works, but I still have to click the command
button 4 times to get the figures synced, so that the amount added to the
selling price equals the total due amount. It's like Access cannot
dynamically check the TotalDue field as it runs the loop to see if if needs
to add more to the Sale Price.
 
Hmm, we will get there. Right now you are in the same situation as where you
started from.

Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally deducting the
saleprice again from itself is that correct?

Give me some numbers and of what you are testing with so I can try to mimic
it.

Right now I see four fields

[SalePrice] - bound field?
[txtSalePrice] - bound field?
[TotalDue] - bound field?
[LicenseFee] - Bound field?

So give me an example with numbers in your fields at the start and what the
outcome should be at the end..
 
Hi Maurice,

Ok, I've trying to avoid all the sordid details for simplification purposes.
I actually have to forms here, frmLease, which has worked well for me for
years and does the lease payment calculations and gives me the TotalDue, and
a new unbound popup form called frmRoll. On frmRoll I have an unbound
txtbox, txtSalePrice, which captures the SalePrice as it's default value from
frmLease. I also have txtLicenseFee as an unbound textbox, with default
value of 0, and then the command button. The idea is to have and save the
original saleprice from frmLease for comparrison purposes.
Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally deducting the
saleprice again from itself is that correct?
Click to expand...

No, at this point SalePrice is already there. What I am doing is adding the
the amounts from TotalDue and txtLicenseFee to the SalePrice.
Give me some numbers and of what you are testing with so I can try to mimic
it.
Click to expand...

Ok, SalePrice $33000
TotalDue $1109.90

First click of command button ads $1109.90 to SalePrice, changing it to
$34109.90, but then TotalDue changes to $1167.63, mostly because the first
payment changed (first payment is part of the 1109.90, and now part of the
$1167.63). Notice that the difference between the first TotalDue is $57.73.
Clicking on the command button a 2nd time adds that amount to the SalePrice.
The new difference is 1.71, and clicking on the command button again adds
that amount to the SalePrice. The new difference is .05, and clicking a 4th
time adds that amount to the SalePrice, and finally we have the Amount that
has been added to the SalePrice equalling the TotalDue amount which is now
$1169.39. SalePrice has gone from the original amount of $33000 to $34169.39.

What I need is to end up with the amount added to Sale = the final TotalDue
amount.

[SalePrice] - bound field? Yes
[txtSalePrice] - bound field? No, unbound and on popup
frmRoll
[TotalDue] - bound field? No, Calculated field on
frmLease
[LicenseFee] - Bound field? No, unbound and on popup
frmRoll

Maybe there is a better mathematical way to do this, but what I have been is
first adding the TotalDue amount to the SalePrice, then checking how much
more then new TotalDue amount is and then adding the differnce to the
SalePrice, and then checking again for the new TotalDue amount, and then
adding the difference again to the SalePrice. Usually takes doing this about
4 times to get the amount that is added to the SalePrice to = the TotalDue
amount. Again, remember, all I am trying to do is add the exact amount of
the Up front or TotalDue monies to the SalePrice so that the customer can
avoid coming up with any cash out of pocket.

I hope this explains it. Thanks so much for your help.

--
Gary in Michigan, USA


Maurice said:
Hmm, we will get there. Right now you are in the same situation as where you
started from.

Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally deducting the
saleprice again from itself is that correct?

Give me some numbers and of what you are testing with so I can try to mimic
it.

Right now I see four fields

[SalePrice] - bound field?
[txtSalePrice] - bound field?
[TotalDue] - bound field?
[LicenseFee] - Bound field?

So give me an example with numbers in your fields at the start and what the
outcome should be at the end..


--
Maurice Ausum


GaryS said:
Maurice, your 6 count loop works, but I still have to click the command
button 4 times to get the figures synced, so that the amount added to the
selling price equals the total due amount. It's like Access cannot
dynamically check the TotalDue field as it runs the loop to see if if needs
to add more to the Sale Price.
Click to expand...
Click to expand...
 
Gary,
Ok, I've trying to avoid all the sordid details for simplification
purposes.
Click to expand...
To do this right, you can't avoid it.

So where did you get the $1109.90 from. Is it calcuated? If so, how?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


GaryS said:
Hi Maurice,

Ok, I've trying to avoid all the sordid details for simplification
purposes.
I actually have to forms here, frmLease, which has worked well for me for
years and does the lease payment calculations and gives me the TotalDue,
and
a new unbound popup form called frmRoll. On frmRoll I have an unbound
txtbox, txtSalePrice, which captures the SalePrice as it's default value
from
frmLease. I also have txtLicenseFee as an unbound textbox, with default
value of 0, and then the command button. The idea is to have and save the
original saleprice from frmLease for comparrison purposes.
Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally deducting
the
saleprice again from itself is that correct?
Click to expand...

No, at this point SalePrice is already there. What I am doing is adding
the
the amounts from TotalDue and txtLicenseFee to the SalePrice.
Give me some numbers and of what you are testing with so I can try to
mimic
it.
Click to expand...

Ok, SalePrice $33000
TotalDue $1109.90

First click of command button ads $1109.90 to SalePrice, changing it to
$34109.90, but then TotalDue changes to $1167.63, mostly because the first
payment changed (first payment is part of the 1109.90, and now part of the
$1167.63). Notice that the difference between the first TotalDue is
$57.73.
Clicking on the command button a 2nd time adds that amount to the
SalePrice.
The new difference is 1.71, and clicking on the command button again adds
that amount to the SalePrice. The new difference is .05, and clicking a
4th
time adds that amount to the SalePrice, and finally we have the Amount
that
has been added to the SalePrice equalling the TotalDue amount which is now
$1169.39. SalePrice has gone from the original amount of $33000 to
$34169.39.

What I need is to end up with the amount added to Sale = the final
TotalDue
amount.

[SalePrice] - bound field? Yes
[txtSalePrice] - bound field? No, unbound and on popup
frmRoll
[TotalDue] - bound field? No, Calculated field on
frmLease
[LicenseFee] - Bound field? No, unbound and on popup
frmRoll

Maybe there is a better mathematical way to do this, but what I have been
is
first adding the TotalDue amount to the SalePrice, then checking how much
more then new TotalDue amount is and then adding the differnce to the
SalePrice, and then checking again for the new TotalDue amount, and then
adding the difference again to the SalePrice. Usually takes doing this
about
4 times to get the amount that is added to the SalePrice to = the TotalDue
amount. Again, remember, all I am trying to do is add the exact amount of
the Up front or TotalDue monies to the SalePrice so that the customer can
avoid coming up with any cash out of pocket.

I hope this explains it. Thanks so much for your help.

--
Gary in Michigan, USA


Maurice said:
Hmm, we will get there. Right now you are in the same situation as where
you
started from.

Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally deducting
the
saleprice again from itself is that correct?

Give me some numbers and of what you are testing with so I can try to
mimic
it.

Right now I see four fields

[SalePrice] - bound field?
[txtSalePrice] - bound field?
[TotalDue] - bound field?
[LicenseFee] - Bound field?

So give me an example with numbers in your fields at the start and what
the
outcome should be at the end..


--
Maurice Ausum


GaryS said:
Maurice, your 6 count loop works, but I still have to click the command
button 4 times to get the figures synced, so that the amount added to
the
selling price equals the total due amount. It's like Access cannot
dynamically check the TotalDue field as it runs the loop to see if if
needs
to add more to the Sale Price.
--
Gary in Michigan, USA


:

Gary,

If no conditions are met you are ending up in an endless loop. That's
what
Graham and Marshall are pointing out to you.

if you want your loop to only run six times you could try:


dim iCount as integer

iCount=0
Do While Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice -
Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

icount=icount+1
DoEvents 'Necessary to free up other processes

if icount=6 then exit do
Loop

see of that get's you anywhere. Remember the posts from Graham and
Marshall
stating you rethink tour strategy (what if the next time it should
run for 8
times? you would be changing your code constantly).

btw you also have to do the proper error handling as well in this
situation...

hth
--
Maurice Ausum


:

Hmm, that apparently starts an endless loop. I modefied your code
slightly
by changing the < to <=, but still get lockup, with no change
SalePrice when
the code is executed. Is there a way to only have it loop 6 times?
--
Gary in Michigan, USA


:

Gary,

I see what you mean. Try putting this behind the click event of
your button:

While Not Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice - Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

Wend

That should do the trick for ya...

hth

--
Maurice Ausum


:

I decided maybe more explanation was required.

FrmLease calculates lease payments. Normally people pay
certain costs up
front, but some times I need to roll those costs in to the Sale
Price. When
I add to the Sale Price it changes the TotalDue. I need to run
the code
(recalculate) 4 times to make amount added to the Sale Price =
the TotalDue.
I hope this helps some kind soul make sense out of what I am
trying to do.
Thank you


--
Gary in Michigan, USA


:

Hello -Using Access 2003 I have the following code in the
click event of a
command button.

If Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice -
Me.txtSalePrice Then

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

End If

Basically I need the code to run several times until the
condition is met (a
loop?). I've tried several if then statements together but
it seems the
condition only gets checked once per click, as clicking
several times
achieves the result. I know this can be accomplished with
some sort of loop,
but I don't understand how to do it. Any help would be
greatly appreciated.

Gary in Michigan, USA
Click to expand...
Click to expand...
Click to expand...
 
Hi Graham,

Yes, TotalDue is a Calculated field, composed of the first months payment, a
Security Deposit, which is equal to the first months payment but rounded to
the next highest $25 increment, and tax on any cash down payment or rebates
that are used to reduce the selling price, and finally a Documentary Fee.
For this particular scenario I've listed the fees.

First Payment (Calculated =Payment) $505.40
Security Deposit (Calculated) $525.00
Documentary Fee (always the same) $75.00 (Calculated)
Tax on Doc Fee (always the same) $4.50 (Calculated)
----------
TotalDue $1109.90

So you can see, when you add this $1109.90 to the SalePrice it changes the
First Payment, and could change the Security Deposit.

Thanks for your interest, and any help you can provide.
--
Gary in Michigan, USA


Graham R Seach said:
Gary,
Ok, I've trying to avoid all the sordid details for simplification
purposes.
Click to expand...
To do this right, you can't avoid it.

So where did you get the $1109.90 from. Is it calcuated? If so, how?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


GaryS said:
Hi Maurice,

Ok, I've trying to avoid all the sordid details for simplification
purposes.
I actually have to forms here, frmLease, which has worked well for me for
years and does the lease payment calculations and gives me the TotalDue,
and
a new unbound popup form called frmRoll. On frmRoll I have an unbound
txtbox, txtSalePrice, which captures the SalePrice as it's default value
from
frmLease. I also have txtLicenseFee as an unbound textbox, with default
value of 0, and then the command button. The idea is to have and save the
original saleprice from frmLease for comparrison purposes.
Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally deducting
the
saleprice again from itself is that correct?
Click to expand...

No, at this point SalePrice is already there. What I am doing is adding
the
the amounts from TotalDue and txtLicenseFee to the SalePrice.
Give me some numbers and of what you are testing with so I can try to
mimic
it.
Click to expand...

Ok, SalePrice $33000
TotalDue $1109.90

First click of command button ads $1109.90 to SalePrice, changing it to
$34109.90, but then TotalDue changes to $1167.63, mostly because the first
payment changed (first payment is part of the 1109.90, and now part of the
$1167.63). Notice that the difference between the first TotalDue is
$57.73.
Clicking on the command button a 2nd time adds that amount to the
SalePrice.
The new difference is 1.71, and clicking on the command button again adds
that amount to the SalePrice. The new difference is .05, and clicking a
4th
time adds that amount to the SalePrice, and finally we have the Amount
that
has been added to the SalePrice equalling the TotalDue amount which is now
$1169.39. SalePrice has gone from the original amount of $33000 to
$34169.39.

What I need is to end up with the amount added to Sale = the final
TotalDue
amount.
Right now I see four fields
Click to expand...

[SalePrice] - bound field? Yes
[txtSalePrice] - bound field? No, unbound and on popup
frmRoll
[TotalDue] - bound field? No, Calculated field on
frmLease
[LicenseFee] - Bound field? No, unbound and on popup
frmRoll

Maybe there is a better mathematical way to do this, but what I have been
is
first adding the TotalDue amount to the SalePrice, then checking how much
more then new TotalDue amount is and then adding the differnce to the
SalePrice, and then checking again for the new TotalDue amount, and then
adding the difference again to the SalePrice. Usually takes doing this
about
4 times to get the amount that is added to the SalePrice to = the TotalDue
amount. Again, remember, all I am trying to do is add the exact amount of
the Up front or TotalDue monies to the SalePrice so that the customer can
avoid coming up with any cash out of pocket.

I hope this explains it. Thanks so much for your help.

--
Gary in Michigan, USA


Maurice said:
Hmm, we will get there. Right now you are in the same situation as where
you
started from.

Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) - Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally deducting
the
saleprice again from itself is that correct?

Give me some numbers and of what you are testing with so I can try to
mimic
it.

Right now I see four fields

[SalePrice] - bound field?
[txtSalePrice] - bound field?
[TotalDue] - bound field?
[LicenseFee] - Bound field?

So give me an example with numbers in your fields at the start and what
the
outcome should be at the end..


--
Maurice Ausum


:

Maurice, your 6 count loop works, but I still have to click the command
button 4 times to get the figures synced, so that the amount added to
the
selling price equals the total due amount. It's like Access cannot
dynamically check the TotalDue field as it runs the loop to see if if
needs
to add more to the Sale Price.
--
Gary in Michigan, USA


:

Gary,

If no conditions are met you are ending up in an endless loop. That's
what
Graham and Marshall are pointing out to you.

if you want your loop to only run six times you could try:


dim iCount as integer

iCount=0
Do While Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice -
Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

icount=icount+1
DoEvents 'Necessary to free up other processes

if icount=6 then exit do
Loop

see of that get's you anywhere. Remember the posts from Graham and
Marshall
stating you rethink tour strategy (what if the next time it should
run for 8
times? you would be changing your code constantly).

btw you also have to do the proper error handling as well in this
situation...

hth
--
Maurice Ausum


:

Hmm, that apparently starts an endless loop. I modefied your code
slightly
by changing the < to <=, but still get lockup, with no change
SalePrice when
the code is executed. Is there a way to only have it loop 6 times?
--
Gary in Michigan, USA


:

Gary,

I see what you mean. Try putting this behind the click event of
your button:

While Not Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice - Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

Wend

That should do the trick for ya...

hth

--
Maurice Ausum


:

I decided maybe more explanation was required.

FrmLease calculates lease payments. Normally people pay
certain costs up
front, but some times I need to roll those costs in to the Sale
Price. When
I add to the Sale Price it changes the TotalDue. I need to run
the code
(recalculate) 4 times to make amount added to the Sale Price =
the TotalDue.
I hope this helps some kind soul make sense out of what I am
trying to do.
Thank you


--
Gary in Michigan, USA


:

Hello -Using Access 2003 I have the following code in the
click event of a
command button.

If Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice -
Me.txtSalePrice Then

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

End If

Basically I need the code to run several times until the
condition is met (a
loop?). I've tried several if then statements together but
it seems the
condition only gets checked once per click, as clicking
several times
achieves the result. I know this can be accomplished with
some sort of loop,
but I don't understand how to do it. Any help would be
greatly appreciated.

Gary in Michigan, USA
Click to expand...
Click to expand...
Click to expand...
 
Gary,

We're getting closer, but we're not there yet.

1. How is the first payment calculated?
2. How is the security deposit calculated?
3. How is the tax calculated?

Remember, we don't know your business rules, and not all of us live in the
same country as you, so we don't know your tax formulae.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


GaryS said:
Hi Graham,

Yes, TotalDue is a Calculated field, composed of the first months payment,
a
Security Deposit, which is equal to the first months payment but rounded
to
the next highest $25 increment, and tax on any cash down payment or
rebates
that are used to reduce the selling price, and finally a Documentary Fee.
For this particular scenario I've listed the fees.

First Payment (Calculated =Payment) $505.40
Security Deposit (Calculated) $525.00
Documentary Fee (always the same) $75.00 (Calculated)
Tax on Doc Fee (always the same) $4.50 (Calculated)
----------
TotalDue $1109.90

So you can see, when you add this $1109.90 to the SalePrice it changes the
First Payment, and could change the Security Deposit.

Thanks for your interest, and any help you can provide.
--
Gary in Michigan, USA


Graham R Seach said:
Gary,
Ok, I've trying to avoid all the sordid details for simplification
purposes.
Click to expand...
To do this right, you can't avoid it.

So where did you get the $1109.90 from. Is it calcuated? If so, how?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


GaryS said:
Hi Maurice,

Ok, I've trying to avoid all the sordid details for simplification
purposes.
I actually have to forms here, frmLease, which has worked well for me
for
years and does the lease payment calculations and gives me the
TotalDue,
and
a new unbound popup form called frmRoll. On frmRoll I have an unbound
txtbox, txtSalePrice, which captures the SalePrice as it's default
value
from
frmLease. I also have txtLicenseFee as an unbound textbox, with
default
value of 0, and then the command button. The idea is to have and save
the
original saleprice from frmLease for comparrison purposes.

Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally
deducting
the
saleprice again from itself is that correct?

No, at this point SalePrice is already there. What I am doing is
adding
the
the amounts from TotalDue and txtLicenseFee to the SalePrice.

Give me some numbers and of what you are testing with so I can try to
mimic
it.

Ok, SalePrice $33000
TotalDue $1109.90

First click of command button ads $1109.90 to SalePrice, changing it to
$34109.90, but then TotalDue changes to $1167.63, mostly because the
first
payment changed (first payment is part of the 1109.90, and now part of
the
$1167.63). Notice that the difference between the first TotalDue is
$57.73.
Clicking on the command button a 2nd time adds that amount to the
SalePrice.
The new difference is 1.71, and clicking on the command button again
adds
that amount to the SalePrice. The new difference is .05, and clicking
a
4th
time adds that amount to the SalePrice, and finally we have the Amount
that
has been added to the SalePrice equalling the TotalDue amount which is
now
$1169.39. SalePrice has gone from the original amount of $33000 to
$34169.39.

What I need is to end up with the amount added to Sale = the final
TotalDue
amount.

Right now I see four fields

[SalePrice] - bound field? Yes
[txtSalePrice] - bound field? No, unbound and on
popup
frmRoll
[TotalDue] - bound field? No, Calculated field
on
frmLease
[LicenseFee] - Bound field? No, unbound and on
popup
frmRoll

Maybe there is a better mathematical way to do this, but what I have
been
is
first adding the TotalDue amount to the SalePrice, then checking how
much
more then new TotalDue amount is and then adding the differnce to the
SalePrice, and then checking again for the new TotalDue amount, and
then
adding the difference again to the SalePrice. Usually takes doing this
about
4 times to get the amount that is added to the SalePrice to = the
TotalDue
amount. Again, remember, all I am trying to do is add the exact amount
of
the Up front or TotalDue monies to the SalePrice so that the customer
can
avoid coming up with any cash out of pocket.

I hope this explains it. Thanks so much for your help.

--
Gary in Michigan, USA


:

Hmm, we will get there. Right now you are in the same situation as
where
you
started from.

Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally
deducting
the
saleprice again from itself is that correct?

Give me some numbers and of what you are testing with so I can try to
mimic
it.

Right now I see four fields

[SalePrice] - bound field?
[txtSalePrice] - bound field?
[TotalDue] - bound field?
[LicenseFee] - Bound field?

So give me an example with numbers in your fields at the start and
what
the
outcome should be at the end..


--
Maurice Ausum


:

Maurice, your 6 count loop works, but I still have to click the
command
button 4 times to get the figures synced, so that the amount added
to
the
selling price equals the total due amount. It's like Access cannot
dynamically check the TotalDue field as it runs the loop to see if
if
needs
to add more to the Sale Price.
--
Gary in Michigan, USA


:

Gary,

If no conditions are met you are ending up in an endless loop.
That's
what
Graham and Marshall are pointing out to you.

if you want your loop to only run six times you could try:


dim iCount as integer

iCount=0
Do While Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice -
Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

icount=icount+1
DoEvents 'Necessary to free up other processes

if icount=6 then exit do
Loop

see of that get's you anywhere. Remember the posts from Graham and
Marshall
stating you rethink tour strategy (what if the next time it should
run for 8
times? you would be changing your code constantly).

btw you also have to do the proper error handling as well in this
situation...

hth
--
Maurice Ausum


:

Hmm, that apparently starts an endless loop. I modefied your
code
slightly
by changing the < to <=, but still get lockup, with no change
SalePrice when
the code is executed. Is there a way to only have it loop 6
times?
--
Gary in Michigan, USA


:

Gary,

I see what you mean. Try putting this behind the click event
of
your button:

While Not Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice - Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

Wend

That should do the trick for ya...

hth

--
Maurice Ausum


:

I decided maybe more explanation was required.

FrmLease calculates lease payments. Normally people pay
certain costs up
front, but some times I need to roll those costs in to the
Sale
Price. When
I add to the Sale Price it changes the TotalDue. I need to
run
the code
(recalculate) 4 times to make amount added to the Sale Price
=
the TotalDue.
I hope this helps some kind soul make sense out of what I am
trying to do.
Thank you


--
Gary in Michigan, USA


:

Hello -Using Access 2003 I have the following code in the
click event of a
command button.

If Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice -
Me.txtSalePrice Then

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

End If

Basically I need the code to run several times until the
condition is met (a
loop?). I've tried several if then statements together
but
it seems the
condition only gets checked once per click, as clicking
several times
achieves the result. I know this can be accomplished with
some sort of loop,
but I don't understand how to do it. Any help would be
greatly appreciated.

Gary in Michigan, USA
Click to expand...
Click to expand...
Click to expand...
 
Hi Graham,

Well I found a less than elegant solution and have it working fine. No one
of course knows it's not exactly the right way to do it, except me, so I
guess I'll live with it. Thanks so much for your help.
--
Gary in Michigan, USA


Graham R Seach said:
Gary,

We're getting closer, but we're not there yet.

1. How is the first payment calculated?
2. How is the security deposit calculated?
3. How is the tax calculated?

Remember, we don't know your business rules, and not all of us live in the
same country as you, so we don't know your tax formulae.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


GaryS said:
Hi Graham,

Yes, TotalDue is a Calculated field, composed of the first months payment,
a
Security Deposit, which is equal to the first months payment but rounded
to
the next highest $25 increment, and tax on any cash down payment or
rebates
that are used to reduce the selling price, and finally a Documentary Fee.
For this particular scenario I've listed the fees.

First Payment (Calculated =Payment) $505.40
Security Deposit (Calculated) $525.00
Documentary Fee (always the same) $75.00 (Calculated)
Tax on Doc Fee (always the same) $4.50 (Calculated)
----------
TotalDue $1109.90

So you can see, when you add this $1109.90 to the SalePrice it changes the
First Payment, and could change the Security Deposit.

Thanks for your interest, and any help you can provide.
--
Gary in Michigan, USA


Graham R Seach said:
Gary,

Ok, I've trying to avoid all the sordid details for simplification
purposes.
To do this right, you can't avoid it.

So where did you get the $1109.90 from. Is it calcuated? If so, how?

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia


Hi Maurice,

Ok, I've trying to avoid all the sordid details for simplification
purposes.
I actually have to forms here, frmLease, which has worked well for me
for
years and does the lease payment calculations and gives me the
TotalDue,
and
a new unbound popup form called frmRoll. On frmRoll I have an unbound
txtbox, txtSalePrice, which captures the SalePrice as it's default
value
from
frmLease. I also have txtLicenseFee as an unbound textbox, with
default
value of 0, and then the command button. The idea is to have and save
the
original saleprice from frmLease for comparrison purposes.

Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally
deducting
the
saleprice again from itself is that correct?

No, at this point SalePrice is already there. What I am doing is
adding
the
the amounts from TotalDue and txtLicenseFee to the SalePrice.

Give me some numbers and of what you are testing with so I can try to
mimic
it.

Ok, SalePrice $33000
TotalDue $1109.90

First click of command button ads $1109.90 to SalePrice, changing it to
$34109.90, but then TotalDue changes to $1167.63, mostly because the
first
payment changed (first payment is part of the 1109.90, and now part of
the
$1167.63). Notice that the difference between the first TotalDue is
$57.73.
Clicking on the command button a 2nd time adds that amount to the
SalePrice.
The new difference is 1.71, and clicking on the command button again
adds
that amount to the SalePrice. The new difference is .05, and clicking
a
4th
time adds that amount to the SalePrice, and finally we have the Amount
that
has been added to the SalePrice equalling the TotalDue amount which is
now
$1169.39. SalePrice has gone from the original amount of $33000 to
$34169.39.

What I need is to end up with the amount added to Sale = the final
TotalDue
amount.

Right now I see four fields

[SalePrice] - bound field? Yes
[txtSalePrice] - bound field? No, unbound and on
popup
frmRoll
[TotalDue] - bound field? No, Calculated field
on
frmLease
[LicenseFee] - Bound field? No, unbound and on
popup
frmRoll

Maybe there is a better mathematical way to do this, but what I have
been
is
first adding the TotalDue amount to the SalePrice, then checking how
much
more then new TotalDue amount is and then adding the differnce to the
SalePrice, and then checking again for the new TotalDue amount, and
then
adding the difference again to the SalePrice. Usually takes doing this
about
4 times to get the amount that is added to the SalePrice to = the
TotalDue
amount. Again, remember, all I am trying to do is add the exact amount
of
the Up front or TotalDue monies to the SalePrice so that the customer
can
avoid coming up with any cash out of pocket.

I hope this explains it. Thanks so much for your help.

--
Gary in Michigan, USA


:

Hmm, we will get there. Right now you are in the same situation as
where
you
started from.

Let's clear up some things first:

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

What you are doing here is calculating a saleprice and finally
deducting
the
saleprice again from itself is that correct?

Give me some numbers and of what you are testing with so I can try to
mimic
it.

Right now I see four fields

[SalePrice] - bound field?
[txtSalePrice] - bound field?
[TotalDue] - bound field?
[LicenseFee] - Bound field?

So give me an example with numbers in your fields at the start and
what
the
outcome should be at the end..


--
Maurice Ausum


:

Maurice, your 6 count loop works, but I still have to click the
command
button 4 times to get the figures synced, so that the amount added
to
the
selling price equals the total due amount. It's like Access cannot
dynamically check the TotalDue field as it runs the loop to see if
if
needs
to add more to the Sale Price.
--
Gary in Michigan, USA


:

Gary,

If no conditions are met you are ending up in an endless loop.
That's
what
Graham and Marshall are pointing out to you.

if you want your loop to only run six times you could try:


dim iCount as integer

iCount=0
Do While Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice -
Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

icount=icount+1
DoEvents 'Necessary to free up other processes

if icount=6 then exit do
Loop

see of that get's you anywhere. Remember the posts from Graham and
Marshall
stating you rethink tour strategy (what if the next time it should
run for 8
times? you would be changing your code constantly).

btw you also have to do the proper error handling as well in this
situation...

hth
--
Maurice Ausum


:

Hmm, that apparently starts an endless loop. I modefied your
code
slightly
by changing the < to <=, but still get lockup, with no change
SalePrice when
the code is executed. Is there a way to only have it loop 6
times?
--
Gary in Michigan, USA


:

Gary,

I see what you mean. Try putting this behind the click event
of
your button:

While Not Forms!frmlease!TotalDue + Me.txtLicenseFee <
Forms!frmlease!SalePrice - Me.txtSalePrice

Forms!frmlease!SalePrice = Forms!frmlease!SalePrice +
((Forms!frmlease!TotalDue + Me.txtLicenseFee) -
Forms!frmlease!SalePrice)

Wend

That should do the trick for ya...

hth

--
Click to expand...
Click to expand...
Click to expand...
 
Back
Top