Base the value of a control in a form on another calculated control of same form

[Bigdaddrock]

I have tried using the SetValue Macro to assign the value of a calculated control to another BOUND Control on the same form but have not been successful. I followed the exact format shown in MS Access help instructions.
The amount appears properly in the unbound text box (derived from data in a subform within the same form), however I cannot get that amount to then appear in a Bound Control's text box using the SetValue macro.
I am only doing it this way, since I do not know how to attach a calculated amount from the subform to directly populate a Bound Control.
(The subform contains a series of items on an invoice. The Main Form is the actual invoice, and I would like to record the total price of each of the item's costs onto a record associated with the Invoice.
I do not do code, and would like to know how I can do this using macros or just keying into a section of the Properties associated with the text Box.
Thanks for the anticipated help.
BigDaddrock

[patjones]

What happens when you attempt to do this? Are you getting an error message of some kind? Or is it just that nothing at all is happening? Does the rest of the main form populate correctly with the data for that customer and invoice?

Pat

[Bigdaddrock]

@zepphead80
I have the SetValue macro taking place at the "At Change" position of the first Text Box (the one that is properly recording the sum amount from the SubForm). However, the destination box remains with no change. That latter one is where I would like to "paint" the sum amount appearing in the first Text Box.

[patjones]

I have to confess that I never use macros, and I am not familiar with the SetValue feature that you are talking about.

What happens when you try to enter something directly into the text box in question? Not that you'll be doing this all the time in practice - I'm just curious to see how it responds.

Pat

[NeoPa]

I'd consider using VBA code to handle this for you. It's fairly basic stuff and shouldn't prove too much to start you off with.

[Bigdaddrock]

@NeoPa
I have determined a "work around", but am willing to learn new tricks. Based on the info provided, could you perhaps provide some kind of VBA code that I might try? Here are some details from my particular form:

Text Box in Subform!DH Orders Subform B is TotShipWt

Text Box in Form!DH ADD ORDERS is SHIPTOT
(This is Bound to the field SHIPTOT in the table supporting the Form)

Calculation I would like to have done to insert to that Text Box is

=Switch([TOTSHIPWT]>400,+24.95,[TOTSHIPWT]>250,+19.95,[TOTSHIPWT]>100,+13.95,[TOTSHIPWT]>80,+11.95,[TOTSHIPWT]>60,+9.95,[TOTSHIPWT]>40,+8.95,[TOTSHIPWT]>20,+6.95,[TOTSHIPWT]>10,+5.5,True,+3.5) 
 

Lastly, I am able to have that last calculation populate an unbound Text Box on my Form. It goes into
Text Box in Form!DH ADD ORDERS entitled SHIPCOST

I would like to be able to directly populate the Text Box SHIPTOT from the subform (eliminating the need for Text Box SHIPCOST. However, I would be willing to populate SHIPTOT from SHIPCOST, if this eases things since they are on the same form.

Thanks for the assistance.

[patjones]

I'll tell you what I think you need to do to solve the immediate problem, then add a disclaimer.

Putting this code in the After Update event of TOTSHIPWT should do it for you:

Forms![DH ADD ORDERS]!SHIPTOT.SetFocus
Forms![DH ADD ORDERS]!SHIPTOT = Switch(Forms![DH ADD ORDERS]![TOTSHIPWT]>400,+24.95,Forms![DH ADD ORDERS]![TOTSHIPWT]>250,+19.95,Forms![DH ADD ORDERS]![TOTSHIPWT]>100,+13.95,Forms![DH ADD ORDERS]![TOTSHIPWT]>80,+11.95,Forms![DH ADD ORDERS]![TOTSHIPWT]>60,+9.95,Forms![DH ADD ORDERS]![TOTSHIPWT]>40,+8.95,Forms![DH ADD ORDERS]![TOTSHIPWT]>20,+6.95,Forms![DH ADD ORDERS]![TOTSHIPWT]>10,+5.5,True,+3.5)

My disclaimer is this. What you are doing here amounts to changing the underlying recordsource, which is not always wrong. But in this situation it doesn't make sense to store this calculation in a table in your database.

Take age as an example. If you have a table of people and dates of birth, you could store age in the table also; however, doing so is actually redundant, because the table already holds date of birth. Whenever age is needed (in a report, for instance), it can simply be calculated at that time. Not to mention that age is a constantly changing quantity...so as soon as the column is updated, many of the values will very soon become out of date.

In your situation, I would store the various weight-to-price conversions in a look-up table instead of using the Switch statement, and then whenever you need to obtain such a conversion for any purpose, you can grab the value from the table.

Pat

[NeoPa]

I very much support Pat's comments on normalised data (Basically not storing the same info more than once but I strongly recommend checking out Normalisation and Table structures for the full story).

That said, as a first step on the path to VBA programming (as opposed to the old macro approach) I would say :

1. Start off by creating the AfterUpdate event procedure for the control [TotShipWt] in your [DH Orders Subform B] form. Select the control and, ensuring properties are visible, set the AfterUpdate property to [Event procedure] from the dropdown.
2. This creates the template shell within which to add your code. Add the code below as an example. This is still not a recommended approach, but will probably help you understand better how things work in the VBA world.

Private Sub TotShipWt_AfterUpdate()
    With Me
        .Parent.ShipTot = Switch(.TotShipWt > 400, 24.95, _
                                 .TotShipWt > 250, 19.95, _
                                 .TotShipWt > 100, 13.95, _
                                 .TotShipWt > 80, 11.95, _
                                 .TotShipWt > 60, 9.95, _
                                 .TotShipWt > 40, 8.95, _
                                 .TotShipWt > 20, 6.95, _
                                 .TotShipWt > 10, 5.5, _
                                 True, 3.5)
    End With
End Sub

[Bigdaddrock]

Thanks for the explanation. I appreciate your help.

[NeoPa]

You're very welcome :)