Adding data to a table via a form that contains a function

[Gooter]

Greetings,

I am very much a novice when it comes to Access97. I have never used a database program before and no very little of good database design. Having said that, I'm working on a database project for my company.

It's a very basic database. I set up two tables, one that contains general company information and the other contains the sales information we want to track (sales table). My question is regarding three fields I created in the latter table: 1)Sale Amt 2)Markup/Markdown 3)Total Amt. I also created a data entry form linked to all the fields in my sales table, including the 3 I just mentioned.

Now, in the form design, I created a function in the Total Amt field that adds the data from the Sale Amt field to the Markup/Markdown field so that the user doesn't have to add the totals, it is already done for them. The problem I'm experiencing is data on the form in the Total Amt field does not get stored in the same field in my Sales table. I'm not sure how to set this up. Does anyone have any advice?

Thanks.

 

[LittleSmudge]

Let me get this right
(Sales Amount) + (MarkUp/Down) = (Total Amount)

If you are saving [SaleAmt] in a field in the table
and you are saving [Markup/Down] in a field in the table
then
DO NOT save [TotalAmt]

This is a big no-no of Normalistion Rules ( Codd et al. )
and is one of the very common mistakes that users new to relational databases make - so don't feel bad about it.

Saving "calculated fields" like this as BAD because it
A) Saves totally unnecessary data
B) Is prone to errors - because if (somehow) SalesAmt+MarkUpDown does not equal TotalAmt then ??? Which one is correct ??? - This is a Data Itegrity error and should be avoided.

On the Form you have three controls:-
ControlName ctlSalesAmt - this is the name of the control - it can be the sames as the line below ( field name in table ) but doesn't have to be.
ControlSource SalesAmt - this is the name of the field in the table

ControlName ctlMarkUpDown
ControlSource MarkUpDown

ControlName TotalAmt
ControlSource "= ctlSalesAmt + ctlMarkUpDown" include the = but not the "" in the box on the properties window.


G LS

 

[Gooter]

Ok, that makes sense. I see why I do not need to store the TotalAmt since I am storing the two data fields that comprise the TotalAmt anyway. It's redundant to do so. Also, whenever I would need to output the TotalAmt, I could just use the function "= ctlSalesAmt + ctlMarkUpDown" or something to that effect.

Now, my next question is how do I create the TotalAmt controlname on my data entry form? I created the other ones by opening up the "field list" and clicking/dragging them to the form in the design view.

Thanks again.

 

[allanon]

This one is pretty simple. Add a textbox to your form called txtTotalAmt

In the property list for the text box find the recordsource property and press the '...'.

This will open the expression builder. You form will be selected in the letf list box.

In the center listbox find ctlSalesAmt and double click it. It will move it up top like so:

[ctlSalesAmt]

now type ' + ' after it so you now have:

[ctlSalesAmt] +

then find the ctlMarkUpDown in the center list and double click it. You will then have:

[ctlSalesAmt] + [ctlMarkUpDown]

click OK

View your form.