Real quick help needed....

[escoyne]

I'm a newbie to Access and I need some quick help. I'm trying to figure out if these two things can be done..

1) I have a check box labeled "Payment Received?". The color of the font is red. What I would like to happen is when the check box is checked, the font color changes to red to black. Is that possible?

2) In my main form, I have a text box that pulls the subtotal from products orders.

Name: txtOrderSubtotal
Control Source: =[Order Details Subform].Form!txtOrderDetailsTotal

I have another text box that I enter my tax rate into.

Name: TaxRate
Control Source: TaxRate (stored in Orders table)

A third text box that calcuates the taxes

Name: Taxes
Control source: =[txtOrderSubtotal]*Nz([Tax Rate])

How can I make the calcuations for the taxes AND store them so I can later print out a sales receipt or invoice?

 

[John Big Booty]

For part one of your question use the following code in the check box's On Click event and also on the Form's On Current event;

    If Me.CheckName.Value = 0 Then
        Me.CheckLabelName.ForeColor = 255
    Else
        Me.CheckLabelName.ForeColor = 0
    End If

To answer your second question, It can be done but it shouldn't be done. The following explanation why this should be avoided is lifted from another forum;

One of the biggest "No-No's" in database development is the storing of calculations in a field. For Example:
Field1 = 2
Field2 = 8
Field3 = 10 (The sum of Field1 and Field2)

The reason why the calculation should not be stored is simple. What happens if Field1 and Field2 need to be increased by 13.6% and 8.3% respectively?

The values would now be:
Field1 = 2.272
Field2 = 8.664
Field3 = 10 (Notice how they don't match now)

Sure you can change Field3 to equal 10.936...... but what if you had 10,000 records, with 150 calculated values stored per record? Yeah, I guess you could write a script that automatically does this, but what if the script was written wrong? Or what if you have to change the values daily, or hourly? Are you going to write a script, or run a script everytime? Keep in mind 10,000 records could take quite some time to update, especially if you have like 150 fields per record to update.

One more thing..... what if the database expanded..... now field3 must equal field1 + field2 + field4 + field5 + field6, and there were 10,000 records?

The calculation should be done at the time you want to display the value. You do this in a Query or Form using an expression, or an unbound textbox whose value is an expression.

In a query, it would be:
Expr1: [Field1]+[Field2]

In a form, in an unbound textbox, the control source under the data tab in properties of the textbox would be:
=[Field1]+[Field2]

The data needed for the calculation should be stored, not the results.....

 

[escoyne]

Thanks John. Got the first part working.

I understand the reasoning on why not to store the value because of the variables.

How do I calculate the taxes and have them print on my invoice? Keep in mind that not all customers pay taxes on their transactions.

 

[John Big Booty]

Your invoice will be a report, I imagine, which is populated by a query. So long as this query is collecting all the information required to calculate the Tax Payable (or not) then you can calculate the tax in the query, or on the report using a similar technique to the one you use on a form.

 

[escoyne]

Thanks for the guidance John. Got everything works like I want it..

 

[John Big Booty]

Excellent, good work