Adding fields in a table

[gilescis]

I have 2 fields in a table 1. called CommAmtDue and the other called CommAmtPd, When the user inputs a value in the CommAmtPd I want the CommAmtDue - CommAmtPd = CommAmtBal

I want this value to be stored in another field in the same table called CommAmtBal

I can do the formula in a query but how can I get the value to be stored in the field to user for later.


Thanks

 

[llkhoutx]

On the CommAmtPd AfterUpdate event

me!CommAmtBal = nz(me!CommAmtDue,0) - nz(me!CommAmtPd,0)

The nz function is used because a Null or empty field will cause te formula to not compute.

 

[gilescis]

Do I attache this event to my Form field ?

This is my actual field names
Comm-Balance
Comm-Amt Pd
Comm-Due

 

[gilescis]

whoops !!!!

Field Names
Com-Balance
Com-Amt Pd
Com-Due

 

[gilescis]

Never mind figured out what I was doing wrong, Thanks for all the help

 

[llkhoutx]

Set the Control Source property of Com-Due to

nz([Com-Balance],0) - nz([Com-Amt Pd],0)


Note, do not use spaces in control or object names, as in "Com-Amt Pd." It makes VBA coding more difficult and will cause considerable confuction, i.e. does it have a space, where is the space, plus you always have to enclose it in brackets. That's 2 extra key strokes. It's just bad practice.

 

[neileg]

You shouldn't be storing this value. As you say, you can do this in a query and any place you want to use a table, you can use a query.

 

[gilescis]

These are the AfterUpdate codes I have. i have one more field called GPS which is gross/totsales....
as you can see from below I already have a totsites_AfterUpdate

Can I have 2 formula's run on the same field afterUpdate ?



Private Sub Comm_Amt_AfterUpdate()
Me!Comm = Nz(Me!commamt, 0) / Nz(Me!Price, 0)
End Sub

Private Sub Dowatclosing_AfterUpdate()
Me!PerDown = Nz(Me!Dowatclosing, 0) / Nz(Me!Price, 0)
End Sub

Private Sub GROSS_AfterUpdate()
Me!IM = Nz(Me!Price, 0) / Nz(Me!GROSS, 0)
End Sub

Private Sub Gross_Profit_AfterUpdate()
Me!GPM = Nz(Me!Price, 0) / Nz(Me!grossprofit, 0)
End Sub

Private Sub TOTSITES_AfterUpdate()
Me!PPS = Nz(Me!Price, 0) / Nz(Me!TOTSITES, 0)
End Sub

 

[llkhoutx]

Dividing by zero will result in an error. Test your denominators for zero; if non-zero divide, else continue.

You can have innumerable lines on code in each subroutine.

 

[gilescis]

So how can I fix this and how do I add another calulation to the same afterUpdate

Thanks

 

[llkhoutx]

Dividing by zero will fire an error. Test for a zero denominator before dividing.

 

[llkhoutx]

Now Pat, calm down.

I don't know what,

no matter what

is. I've had a lot of education, but never took mind reading.

If I post something that is incorrect, there are many who point that out.

I'm a firm believer in "teaching a programmer to program", not programming for him.

 

[neileg]

All Pat is saying is what I have said, you don't store this value.

 

[gilescis]

I guess my question is now, Why is it not good to store the value for use later on ?

 

[gilescis]

BTW there no reason for everyone to get agrivated. Thats what fourms are for is helping heach other out........

 

[JoeCruse]

People here tend to get aggravated when they give you that help and you ignore it and go about the job the wrong way. Two posters have helped you by advising you to use a query for the calculation instead of storing the calculation in a table field. There are REAMS of posts in these forums on why that is bad practice, and unneccessay practice. Go search on this and see why Pat helped you the way she did.

 

[gilescis]

Ok,
Here is my SQL code. I am now not storing calculated data. All works fine except the last statement where my IFF statement is. Can anyone tell me what I am doing wrong to that makes it keep prompting for 4$PerTon & DueCom

Thanks


SELECT elementis_data.DateOrdered, elementis_data.[CustPO#], elementis_data.CustName, elementis_data.SHTOCity, elementis_data.SHTOState, elementis_data.[GilesPO#], elementis_data.[BOL#], elementis_data.[Date Shipped], elementis_data.QtyLbs, elementis_data.UnitPrice, (elementis_data!QtyLbs/2000)*elementis_data!UnitPrice AS BilledAmt, elementis_data.ComRate, ([elementis_data]![QtyLbs]/2000)*4 AS [4$PerTon], [BilledAmt]*0.04 AS DueCom, IIf([4$PerTon:]>[DueCom:],[4$PerTon:],[DueCom:]) AS DueCom2
FROM elementis_data;

 

[gilescis]

Plus I can not get the IFF statement to work the statement returns a blank value

 

[llkhoutx]

[4$PerTon] & [DueCom] are field names built in your query. They cannot be reused in the query in which they are built. Duh!

Your conditional iff

IIf([4$PerTon:]>[DueCom:],[4$PerTon:],[DueCom:])

should be

IIf([4$PerTon:]>[DueCom:],([elementis_data]![QtyLbs]/2000)*4,[BilledAmt]*0.04)

if I read your code right.

 

[gilescis]

I found my issue, yes it was the quotes. I know understand that is is not good pratice to sotre values. Lets move on and quit beating this horse to death. I understand this has to be done in query.

Thanks for all the help