cbox and tbox help

[ryan.fitzpatrick3]

I have a text box that does a calculation of several text boxes to
give me a delivered price per case. I have an option group with 3
selection check boxes with three text boxes next to it where I input
freight weight. Check box 1 has text box 1 where I enter in full truck
weight, cbox 2 has tbox 2 where I enter in 1/2 truck weight, and 1/3
truck weight for the 3rd cbox and tbox respectively. Make sense thus
far. Now in the summed text box I would like it to calculate which one
of the three weights, full, half or third to use by whichever check
box is clicked. I have this code, but I'm not sure it it's correct.

Check 1 = check52
Check 2 = check54
Check 3 = check56


Private Sub OptGroupFreight_click()
Select Case OptGroupFreight
Case 1
If Check52.Enabled = True Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightFull] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check54.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeighthalf] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check56.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightthird] * Forms!Supplier!
Component.Form!CaseWeight), 0)

Case 2
If Check54.Enabled = True Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeighthalf] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check52.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightFull] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check56.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightthird] * Forms!Supplier!
Component.Form!CaseWeight), 0)

Case 3
If Check56.Enabled = True Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightthird] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check54.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeighthalf] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check52.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightFull] * Forms!Supplier!
Component.Form!CaseWeight), 0)
End Select


End Sub



Any ideas?


Thanks,


Ryan

 

[Dirk Goldgar]

I have a text box that does a calculation of several text boxes to
give me a delivered price per case. I have an option group with 3
selection check boxes with three text boxes next to it where I input
freight weight. Check box 1 has text box 1 where I enter in full truck
weight, cbox 2 has tbox 2 where I enter in 1/2 truck weight, and 1/3
truck weight for the 3rd cbox and tbox respectively. Make sense thus
far. Now in the summed text box I would like it to calculate which one
of the three weights, full, half or third to use by whichever check
box is clicked. I have this code, but I'm not sure it it's correct.

Check 1 = check52
Check 2 = check54
Check 3 = check56


Private Sub OptGroupFreight_click()
Select Case OptGroupFreight
Case 1
If Check52.Enabled = True Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightFull] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check54.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeighthalf] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check56.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightthird] * Forms!Supplier!
Component.Form!CaseWeight), 0)

Case 2
If Check54.Enabled = True Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeighthalf] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check52.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightFull] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check56.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightthird] * Forms!Supplier!
Component.Form!CaseWeight), 0)

Case 3
If Check56.Enabled = True Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightthird] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check54.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeighthalf] * Forms!Supplier!
Component.Form!CaseWeight), 0)
If Check52.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightFull] * Forms!Supplier!
Component.Form!CaseWeight), 0)
End Select

End Sub

That looks wrong to me. If these chack boxes are really members of the
option group, then only one of them can be chacked at a time, and you must
test the option group frame's value to see which one it is. If I understand
your setup, your code should be a lot simpler. Use the AfterUpdate event,
not the Click event, and write something like this:

'----- start of code -----
Private Sub OptGroupFreight_AfterUpdate()

Select Case OptGroupFreight

Case 1
Me.CasePrice = _
Nz(Me.FreightTotalDelCosts / Me.TruckWeightFull _
* Forms!Supplier!Component.Form!CaseWeight, _
0)

Case 2
Me.CasePrice = _
Nz(Me.FreightTotalDelCosts / Me.TruckWeighthalf _
* Forms!Supplier!Component.Form!CaseWeight, _
0)

Case 3
Me.CasePrice = _
Nz(Me.FreightTotalDelCosts / Me.TruckWeightthird _
* Forms!Supplier!Component.Form!CaseWeight, _
0)

End Select

End Sub
'----- end of code -----

Note that the above will raise an error if you end up dividing by zero.

 

[ryan.fitzpatrick3]

Yea only one check box is always checked, I made the code up, I didn't
know what to put really.

It says I can't assign a value to that object then highlights all the
case. Do I need option values 1, 2, 3 in there at all? What do I do?

I have a text box that does a calculation of several text boxes to
give me a delivered price per case. I have an option group with 3
selection check boxes with three text boxes next to it where I input
freight weight. Check box 1 has text box 1 where I enter in full truck
weight, cbox 2 has tbox 2 where I enter in 1/2 truck weight, and 1/3
truck weight for the 3rd cbox and tbox respectively. Make sense thus
far. Now in the summed text box I would like it to calculate which one
of the three weights, full, half or third to use by whichever check
box is clicked. I have this code, but I'm not sure it it's correct.

Check 1 = check52
Check 2 = check54
Check 3 = check56

Private Sub OptGroupFreight_click()
 Select Case OptGroupFreight
  Case 1
    If Check52.Enabled = True Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightFull] * Forms!Supplier!
Component.Form!CaseWeight), 0)
    If Check54.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeighthalf] * Forms!Supplier!
Component.Form!CaseWeight), 0)
    If Check56.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightthird] * Forms!Supplier!
Component.Form!CaseWeight), 0)

 Case 2
    If Check54.Enabled = True Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeighthalf] * Forms!Supplier!
Component.Form!CaseWeight), 0)
    If Check52.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightFull] * Forms!Supplier!
Component.Form!CaseWeight), 0)
    If Check56.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightthird] * Forms!Supplier!
Component.Form!CaseWeight), 0)

 Case 3
    If Check56.Enabled = True Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightthird] * Forms!Supplier!
Component.Form!CaseWeight), 0)
    If Check54.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeighthalf] * Forms!Supplier!
Component.Form!CaseWeight), 0)
    If Check52.Enabled = False Then [CasePrice] =
Nz(([FreightTotalDelCosts] / [TruckWeightFull] * Forms!Supplier!
Component.Form!CaseWeight), 0)
End Select

End Sub

That looks wrong to me.  If these chack boxes are really members of the
option group, then only one of them can be chacked at a time, and you must
test the option group frame's value to see which one it is.  If I understand
your setup, your code should be a lot simpler.  Use the AfterUpdate event,
not the Click event, and write something like this:

'----- start of code -----
Private Sub OptGroupFreight_AfterUpdate()

    Select Case OptGroupFreight

        Case 1
             Me.CasePrice = _
                Nz(Me.FreightTotalDelCosts / Me.TruckWeightFull _
                        * Forms!Supplier!Component.Form!CaseWeight, _
                0)

        Case 2
             Me.CasePrice = _
                Nz(Me.FreightTotalDelCosts / Me.TruckWeighthalf _
                        * Forms!Supplier!Component.Form!CaseWeight, _
                0)

        Case 3
             Me.CasePrice = _
                Nz(Me.FreightTotalDelCosts / Me.TruckWeightthird _
                        * Forms!Supplier!Component.Form!CaseWeight, _
                0)

    End Select

End Sub
'----- end of code -----

Note that the above will raise an error if you end up dividing by zero.

 

[Dirk Goldgar]

Yea only one check box is always checked, I made the code up, I didn't
know what to put really.

It says I can't assign a value to that object then highlights all the
case. Do I need option values 1, 2, 3 in there at all? What do I do?

Depends. I was assuming that 1, 2, and 3 were the Option Values assigned to
the check boxes for Full, Half, and Third weight, respectively. You'd
better make sure that's true.

Also, you'd better make sure that CaseWeight is not a calculated control;
that is, that it doesn't have an expression as its Control Source.

 

[ryan.fitzpatrick3]

CaseWeight is a manual entry weight, whereas caseprice is a formula.

 

[Dirk Goldgar]

(re-posting, as my original reply hasn't appeared)

CaseWeight is a manual entry weight, whereas caseprice is a formula.

Sorry, that was my error. I meant to say that you'd better make sure that
*CasePrice* is not a calculated control. If its controlsource is a an
expression (or formula, if you like that word better), then you can't assign
a value to it, because the expression, and only the expression, calculates
the value.

If the expression is calculating the value correctly, and can do so for
every record and in every circumstance, then you shouldn't be trying to
store the calculated value anywhere, and you don't need this code we've been
talking about to calculate and assign it.

Because of the way that your code (or mine) is calcuating the value of
CasePrice, it seems reasonable to me that you may need to calculate and
store the value of CasePrice at this particular moment, since costs and
CaseWeight may conceivably change over time. If that's so, then CasePrice
should be bound to a field, not to an expression.

 

[ryan.fitzpatrick3]

Is there away to make the caseprice (which is an expression or
formula) populate that result into another text box which goes into a
table for record keeping? Then your vba should work right, if I'm
reading that correctly?

 

[Dirk Goldgar]

Is there away to make the caseprice (which is an expression or
formula) populate that result into another text box which goes into a
table for record keeping? Then your vba should work right, if I'm
reading that correctly?


Yes, there's a way, but first I need to know more about the tables and
fields involved. What is the RecordSource of your form? What fields does
it include? What is the ControlSource of the CasePrice text box? If it's
an expression, why do you have both the expression *and* the code we've been
talking about? Is the option group OptGroupFreight bound to a field?

It never hurts, in cases like these, to explain the overall picture of what
you're trying to achieve.

 

[ryan.fitzpatrick3]

I appreciate your help by the way. I'll try to explain it in detail. I
have a main form that has 3 sub forms, one is ingredient components,
other is packaging and the last one is freight components. These three
sub forms are linked (ref integ) to the component form (main) which
more or less is an item page, that has item #, item name, date
entered, and in the sub forms are details for this item. lets say you
take a Pepsi soda.

Item # 123345
Item Desc: Pepsi
Date entered: 9-10-08
CaseWeight: 28.2 (lbs) - weight of case of product in lbs
Yield % 97 - what yield of total product comes out of
batch
BatchSize 101 - size of the batch
Opeartioncosts $1.10
conversioncosts $2.20
Profit $1.92

fobprice = sum of above 3 costs + ingredient costs + packaging costs

All of the above is on the mail component (item) form

The ingredient component subform would look like this. (All info is
made up)

HFCS 42 40% $4.50
Sugar 10% $5.60
Pepsi Secret 50% $1.19

So in the ingredient components is the components (via combo box
selection - continuous) that list all the ingredients in the Pepsi
with what % and what price they are currently at.

In the packaging components its the same thing as ingredient
components

Aluminum can 1 $4.50
Label 1 $5.60
Alum can opener thing 1 $1.19

(packaging UOM is in units/eaches)

Then the freight component sub form

you have a combo box for division we are selling to i.e Texas,
Arizona, etc etc

and there are basic information (data entry) fuel surcharge, lumpers,
total freight, pallet, case/pallet, etc

There is an option group with 3 check boxes that has 3 text boxes next
to them 1 for full truck 2 for 1/2 truck and 3 for 1/3 truck weight.

At the very bottom of the sub form in the form footer is a total text
box that sums up the total freight i.e. total freight cost is $1100,
and adjacent to that is the CASEPRICE textbox which = $1100/full truck
weight * caseweight to give me a freight case weight of (i.e) $0.71 in
which i add to the fob price. make sense?


My first issue is with this textbox (CASEPRICE) There are three checks
in the option group (with tboxs) if I click the 1/3 truck weight
(because we get only 1/3 of a truck delivery) I want the caseprice to
= $1100/ 1/3 truck weight (not full) * case weight. Or if 1/2 truck
weight checkbox is clicked to = $1100/1/2 truck weight * case weight.

Clicking the different checkboxes will change the price, make sense?
How can I get this to work?

There are 4 forms

frmcomponent = item # main form
frmingredients = ingredient components
frmpackaging = packaging components
frmfreight = freight components

All are ref integ linked.

control source of CASEPRICE is =nz(([FreightTotalDelCosts]/
[TruckWeightFull]*Forms!Supplier!Component.Form!CaseWeight),0)

I hope this shines some light on what I'd like.