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]

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

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]

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.

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 :)

[Bigdaddrock]

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

As much as I appreciate your assistance, I am disappointed to report that when I added the code, exactly as written above I still fail to get any change in the control "ShipTot". Note, that TotShipWt is a control in the subform entitled SubFormA. Does that have to be introduced in some way?
The reason I need to store this figure in the Table, is that if the shipping costs associated with the various weights change at a later date, I DO NOT want the values stored previously to change.
Thanks again for your continuing assistance.

[NeoPa]

I'm assuming that :

1. [TotShipWt] is a control on a form that is used as a subform for another form.
2. [ShipTot] is a control on the other form.

The code is associated with [TotShipWt] and the code refers to [ShipTot] via Me.Parent, which is a pointer to the form that the current form is a subform of.

I would expect this to work. If you see nothing wrong with my assumptions yet the code fails to work you may attach a copy of your database to your next post and I'll look at it for you.

When attaching your work please follow the following steps first :

1. Remove anything not relevant to the problem. This is not necessary in all circumstances but some databases can be very bulky and some things do not effect the actual problem at all.
2. Likewise, not entirely necessary in all cases, but consider saving your database in a version not later than 2003 as many of our experts don't use Access 2007. Largely they don't want to, but some also don't have access to it. Personally I will wait until I'm forced to before using it.
3. If the process depends on any linked tables then make local copies in your database to replace the linked tables.
4. If you've done anything in steps 1 to 3 then make sure that the problem you're experiencing is still evident in the updated version.
5. Compile the database (From the Visual Basic Editor select Debug / Compile {Project Name}).
6. Compact the database (Tools / Database Utilities / Compact and Repair Database...).
7. Compress the database into a ZIP file.
8. When posting, scroll down the page and select Manage Attachments (Pressing on that leads you to a page where you can add or remove your attachments. It also lists the maximum file sizes for each of the allowed file types.) and add this new ZIP file.

It's also a good idea to include some instructions that enable us to find the issue you'd like help with. Maybe some instructions of what to select, click on, enter etc that ensures we'll see what you see and have the same problems.

[Bigdaddrock]

Will comply, but am concerned you are making the issue more complicated than it is. I am only using a single form which contains the control ShipTot and the SubformA is included in that form. SubformA contains the control TotShipWt. I merely want to set the Value of ShipTot based on the latest value of TotShipWt.
Is that clear?

[NeoPa]

I was rather hoping you'd confirm my stated assumption rather than trying to re-explain the situation.

From what I understand of your explanation, there is no difference between that and what I stated, but I'm the one who needs to check with you (who should know) that I understand what you're saying.

My confirming what you say matches what I'm thinking (which I'm happy to do) doesn't really get us very far as I don't have your problem.

[patjones]

I'm sorry to jump in at this point, but after looking at all the recent posts, I do think that either the two lines of code I provided previously, or the code that NeoPa provided, should function in this situation.

I wonder if the problem is much simpler than we have been making it. Is the parent form a bound form? I ask this because there is a set of properties in a form's property sheet that have a pretty big impact on what you're allowed to do in a bound form. Namely, they are Data Entry, Allow Additions, Allow Deletions, and Allow Edits.

The recordset type setting (Dynaset versus Snapshot) can also impact whether you are allowed to do what you are attempting.

I bring all this up only because we've had a couple questions recently on this forum where these settings played a role in solving the problem.

Pat