RE: Form calculation based on the date - How to????

I originally typed in the code exactly as you said. When I tried it out, the
total would update correctly, but then I would get this error message, MS
Access can't find the macro 'txtTotalDollarAmount_Bound =
Nz(txtTotalDollarAmount,0)'

After some trial and error, I discovered this......

Typing the code directly into the AFTER UPDATE properties window without the
beginning '=' causes Access to think the line of code is a macro and produces
the error message.

If I use the Expression Builder to enter the code without the '=', Access
automatically adds the extra '=' after closing EB.

With the beginning '=' Access likes it. Without it, Access has problems.

Regardless, the "_Bound" field never gets the total written to it.

BTW, this was done with the simplified code. I took out the extra [Forms]!
etc etc...
And, thanks again for all of your help!!!


"Frank" wrote:

BTW If you type EXACTLY what I suggested (without your extra "=") you can
probably simplify your line back to what I suggested.





"iez44" wrote:

Frank,

I completely understand what you've suggested. I've added the new "_Bound"
text box, named it's control source property as you stated and added the
lines to the "After Update" of the other two text boxes. However, the
"txtTotalDollarAmount_Bound" text box never gets the value from the
"txtTotalDollarAmount" calculated text box.

The calculated box still updates accordingly if I change the # of hours or
date and if I manually type in a value to the "_Bound" box, it will update
the underlying table, so I know it's properly linked.

Please note I had to use this syntax because Access would give me an error
message saying the macro couldn't be found if it typed it in exactly as you
said.

=[Forms]![frmReworkCost]![txtTotalDollarAmount_Bound]=Nz([Forms]![frmReworkCost]![txtTotalDollarAmount],0)

BTW, what is the purpose of using the Nz() function in the line of code?

Thanks,
iez44


"Frank" wrote:

You said ...

"Could it be that the control source for the form's field is now no longer
the same name as the field name in the table?"

I say ...

Exactly ... the text box is no longer "bound" to the field in the underlying
table.

So, create a new text box and call it "txtTotalDollarAmount_Bound" ... set
its ControSource property to the name of the field in your table (so it is
now "bound" to that field).

Now you have to put a value into the new field whenever the value in one of
txtNumberOfHours or txtDate changes do this by putting the following line
into the AfterUpdate event of BOTH these fields -

txtTotalDollarAmount_Bound = Nz(txtTotalDollarAmount,0)

You can't allow an operator to directly enter a value into either
txtTotalDollarAmount or txtTotalDollarAmount_Bound ... for each of these
fields set the Enabled property to "No" and the Locked property to "Yes".
When you have it working properly, set the Visible property to "No" for ONE
of these fields.

Cheers ...

"iez44" wrote:


This solution worked perfectly to calculate and display the correct value
(based on the date) on the form. (THANK YOU!)

However, I now have a problem where this calculated field is never written
to the record in the table where this information is stored. Could it be
that the control source for the form's field is now no longer the same name
as the field name in the table? Regardless of the reason, how do I save this
calculated total in the table?





"Frank" wrote:

Try this.

Change the Combo Box to a Text Box (into which the date will be typed) and
rename it txtDate.

In tblHourlyRates change dtmDateApplicable to dtmEffectiveFrom (ie the date
a new Hourly Rate applies from).

Create a Query named qryHourlyRates with this SQL -
SELECT dtmEffectiveFrom, curHourlyRate
FROM tblHourlyRates
ORDER BY dtmEffectiveFrom DESC

Change ControlSource for txtTotalDollarAmount to this -
=IIf(Not (IsNull([txtNumberOfHours]) Or
IsNull([txtDate])),[txtNumberOfHours]*DLookUp("curHourlyRate","qryHourlyRates","dtmEffectiveFrom <= #" & Format(CDate([txtDate]),"mm/dd/yy\#")))

Cheers ...

"Frank" wrote:

You might like to try (test) this.

Create a Form with these controls -

Text Box: txtNumberOfHours

Combo Box: cboEffectiveDate

Text Box: txtTotalDollarAmount

Create a Table tblHourlyRates with two fields (dtmDateApplicable,
curHourlyRate).

Set RowSource for cboEffectiveDate -
SELECT Format(dtmDateApplicable,"ddd/mm/yy") FROM tblHourlyRates ORDER BY
dtmDateApplicable DESC

Set ControlSource for txtTotalDollarAmount -
=[txtNumberOfHours]*DLookUp("curHourlyRate","tblHourlyRates","dtmDateApplicable = #" & Format(CDate([cboEffectiveDate]),"mm/dd/yy\#"))

Enter two records in tblHourlyRates -
18/1/08 (or American 1/18/08), $20
18/1/09 (or American 1/18/09), $30

Save the form.
Open the form.
Type 10 in txtNumberOfHours
Pick the 2008 date in cboEffectiveDate.
You should see 200 appear in txtTotalDollarAmount.
Change the date in cboEffectiveDate to the 2009 date.
You should see 300 appear in txtTotalDollarAmount.

Note that only NumberOfHours and EffectiveDate should be stored in your
table - TotalDollarAmount will be calculated whenever you display the form
(or a similar Report).

Good Luck!

"iez44" wrote:

Access 2003 issue......

My current form has a field that makes a calculation based on the value of
another field, multiplies it by a fixed number and displays the results in
currency. Basically - # of hours times an hourly rate = total dollar amount.
To accomplish this, I have the field run a macro which performs the calc.
This works just fine.

Now the hourly rate changed in 2008 and by changing my macro to reflect this
new value, I believe the change has recalculated all previous 2007 entries.
(which I don't want!)

So I thought that a possible solution would be to create separate yearly
macros reflecting the correct costing rates, write code to look at the form's
date field and then run the correct macro calculation based on that date.
I'm having a problem with the code. My VB skills are obviously lacking.

Any help would be appreciated!!!!

.

Relevant Pages

  • RE: Form calculation based on the date - How to????
    ... "iez44" wrote: ... "Could it be that the control source for the form's field is now no longer ... txtTotalDollarAmount or txtTotalDollarAmount_Bound ... ... date field and then run the correct macro calculation based on that date. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Form calculation based on the date - How to????
    ... "Frank" wrote: ... Access can't find the macro 'txtTotalDollarAmount_Bound = ... "txtTotalDollarAmount" calculated text box. ... date field and then run the correct macro calculation based on that date. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Form calculation based on the date - How to????
    ... "iez44" wrote: ... "Could it be that the control source for the form's field is now no longer ... txtTotalDollarAmount or txtTotalDollarAmount_Bound ... ... date field and then run the correct macro calculation based on that date. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Form calculation based on the date - How to????
    ... "Could it be that the control source for the form's field is now no longer ... txtTotalDollarAmount or txtTotalDollarAmount_Bound ... ... I have the field run a macro which performs the calc. ... date field and then run the correct macro calculation based on that date. ...
    (microsoft.public.access.modulesdaovba)
  • RE: Form calculation based on the date - How to????
    ... Change ControlSource for txtTotalDollarAmount to this - ... Set RowSource for cboEffectiveDate - ... I have the field run a macro which performs the calc. ... date field and then run the correct macro calculation based on that date. ...
    (microsoft.public.access.modulesdaovba)
Loading