Calculating fields in a form from combo fields

[Jay]

Hey all,
I know the typical response that I will see with this post is build a
query, but in this database, I need to limit the amount of queries I
have, and I know that what I want to do is possible using code, but
can't seem to word it right.

My situation is, I have a table for orders placed, and a table with
the part number and three amounts; shipping cost, wholesale cost, and
markup. On my orders form, I have a combo box that has all of the
fields from the part number table. When someone selects the part from
the list, I want a total cost appear, totalling the bound columns from
the combo box. The reason for this I do not want the person placing
the order to see the markup costs. I have created a code that updates
the cost field with one of the amounts, but when I try to add them all
together. This is what I have:

Private Sub partnumber_AfterUpdate()
Me.totalcost.Value = Me.partnumber.Column(2) +
Me.partnumber.Column(3) + Me.partnumber.Column(4)
End Sub

This takes the total from each field and combines then in the same
field box, i.e.
Shipping Wholesale Markup
$5.00 $349.00 $15.00

and displays it like this:
$534915.00 (all of the amounts side by side)

Any help would be appreciated
Jason

 

[Danny J. Lesandrini]

Well, it's obviously treating them like strings. If they are text values,
you're going to have to convert them to numbers.

Private Sub partnumber_AfterUpdate()
Dim curShipping As Currency
Dim curWholesale As Currency
Dim curMarkup As Currency

curShipping = CCur(Nz(Me.partnumber.Column(2),0))
curWholesale = CCur(Nz(Me.partnumber.Column(3),0))
curMarkup = CCur(Nz(Me.partnumber.Column(4),0))

Me.totalcost.Value = curShipping + curWholesale + curMarkup

End Sub

Now, what happens if the "text" for shipping is "Five Dollars"?
You can't convert this text to a currency. You may even need
to strip off dollar signs and commas, but regardless, all of that
data validation will have to take place here.

 

[Jay]

Hey Danny,
Those fields were all currencies, but this worked beautifully, thank
you so much, I am still relatively new to VB, and I keep forgetting how
important and helpful it is to define your values when initially
writing the code. Thanks again,
Jason