423,851 Members | 2,762 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 423,851 IT Pros & Developers. It's quick & easy.

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

P: 67
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
Jul 6 '10 #1

✓ answered by 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:

Expand|Select|Wrap|Line Numbers
  1. Forms![DH ADD ORDERS]!SHIPTOT.SetFocus
  2. 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

Share this Question
Share on Google+
29 Replies


patjones
Expert 100+
P: 931
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
Jul 6 '10 #2

P: 67
@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.
Jul 6 '10 #3

patjones
Expert 100+
P: 931
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
Jul 7 '10 #4

NeoPa
Expert Mod 15k+
P: 31,121
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.
Jul 7 '10 #5

P: 67
@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
Expand|Select|Wrap|Line Numbers
  1. =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) 
  2.  
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.
Jul 7 '10 #6

patjones
Expert 100+
P: 931
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:

Expand|Select|Wrap|Line Numbers
  1. Forms![DH ADD ORDERS]!SHIPTOT.SetFocus
  2. 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
Jul 7 '10 #7

NeoPa
Expert Mod 15k+
P: 31,121
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.
Expand|Select|Wrap|Line Numbers
  1. Private Sub TotShipWt_AfterUpdate()
  2.     With Me
  3.         .Parent.ShipTot = Switch(.TotShipWt > 400, 24.95, _
  4.                                  .TotShipWt > 250, 19.95, _
  5.                                  .TotShipWt > 100, 13.95, _
  6.                                  .TotShipWt > 80, 11.95, _
  7.                                  .TotShipWt > 60, 9.95, _
  8.                                  .TotShipWt > 40, 8.95, _
  9.                                  .TotShipWt > 20, 6.95, _
  10.                                  .TotShipWt > 10, 5.5, _
  11.                                  True, 3.5)
  12.     End With
  13. End Sub
Jul 8 '10 #8

P: 67
Thanks for the explanation. I appreciate your help.
Jul 8 '10 #9

NeoPa
Expert Mod 15k+
P: 31,121
You're very welcome :)
Jul 8 '10 #10

P: 67
@NeoPa
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.
Jul 14 '10 #11

NeoPa
Expert Mod 15k+
P: 31,121
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.
Jul 15 '10 #12

P: 67
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?
Jul 15 '10 #13

NeoPa
Expert Mod 15k+
P: 31,121
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.
Jul 15 '10 #14

patjones
Expert 100+
P: 931
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
Jul 15 '10 #15

P: 67
@zepphead80
By all means, jump right in!!
Yes, the form is Bound, and Yes the settings for Data Entry, Allow Additions, Allow Deletions, and Allow Edits are all set to YES.
Jul 15 '10 #16

P: 67
@Bigdaddrock
I thought I did reply to your assumptions, which were not correct.
You stated:
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.

My answers are:
1. Wrong. [TotShipWt] is a control on the subform of the active form (not used as a subform on another form).
2. Wrong. [ShipTot] is a control on the active form.
Does that clarify things?
And more importantly, would that revise your solution? I hope so, since I am still seeking a solution.
Thanks for your patience and assistance.
Jul 15 '10 #17

NeoPa
Expert Mod 15k+
P: 31,121
I think the confusion here comes from my use of the term "another form". I was intending this to mean other than the form already referred to in the text, rather than a form other than the active form. As such, we do seem to be agreeing. The active form, is referentially blank (it is undefined without context) so it could mean the subform to me (It would indeed as this is where the code is executing), but the main form to you.

I'm afraid that leaves us no further along I'm sorry to say :(

That said, I notice you say that .DataEntry is set to YES. When that is YES then .AllowDeletions and .AllowEdits are ignored and behave as if set to NO. .DataEntry is a special case for allowing only new data to be entered via a form.

I hope this leads in the right direction.
Jul 15 '10 #18

patjones
Expert 100+
P: 931
If you're willing to, post the file and I will take a look at it. Just strip it of any confidential data and follow the other suggestions in NeoPa's posting guidelines.

Sometimes we get into these length back-and-forth threads when the problem could've instead been diagnosed much more easily if it was right in front of us.

Pat
Jul 15 '10 #19

NeoPa
Expert Mod 15k+
P: 31,121
Oh, and just in case I was a little unclear, you last post (#17) was perfect. It indicated clearly exactly where the confusion was. From there it was cleared (unfortunately didn't result in a ready solution but confusion gone).

I didn't want you to take my last post as criticism. It was just explanation.

PS. I'm also happy with Pat's suggestion. If it's easy for you to post a copy of the db then we can look at it for you. I expect the solution will be much easier with it in front of us :)
Jul 15 '10 #20

P: 67
@NeoPa
Okay, I have stripped most of the relevant data out, so here is a file with the form that I have in question. It is called "DH ADD ORDERS w/Tax" Ignore request for LastName, and just use the drop down to select a customer.
Next, you must select an Invoice Number from the drop down (is there a way that I can have that number auto populate from the underlying query?
Next select an item or two for the order, and then, skipping past the discount and coupon fields, and go right to Ship Cost. (Customer has changed this to be based on the Subtotal of items ordered, which is a computed control on the form.) The Ship Cost is now a Combo Box, but I would like to have it auto-populated based on the value of the Subtotal control on the form in conjunction with the Shipping Cost Table, as shown in the currently configured Combo Box.
Hope this is clear. Thanks for your assistance.
Attached Files
File Type: zip Test DB.zip (254.2 KB, 52 views)
Jul 18 '10 #21

patjones
Expert 100+
P: 931
I'll take a look at it later today when I have some free time at work. In the meantime, let me know if anything changes.

Pat
Jul 19 '10 #22

P: 67
@zepphead80
While you are looking at it, please also look at how the Invoice Number must be selected from a drop down list in the Combo Box control "Invoice Number". Seems silly, but I cannot figure a better way to automatically assign the next available Invoice Number.
I have created two queries which lead me to the choice available in the combo box. Is there a way to auto populate that box with the next available Invoice Number?

Thanks for your assistance.
Jul 19 '10 #23

NeoPa
Expert Mod 15k+
P: 31,121
Like Pat, I'll try to look later when I'm home and can be freer about downloading stuff from the net.

There is a better way to assign Invoice numbers. You use the DMax() function to check existing data and then add one to what you dig up. They're the general guidelines anyway.
Jul 19 '10 #24

NeoPa
Expert Mod 15k+
P: 31,121
I'm afraid I found the controls all had different names from the ones you gave us. I also found that the database didn't compile (See point #5 in my instructions).

I got the following code to work by putting it in the AfterUpdate event procedure of the subform. I tested it using a breakpoint and it worked. I then tested it live and it reverted to $3.50. This tells me (along with a submitted database that doesn't compile) that you have something going on elsewhere that is not making sense.

I suggest you need to get your concepts in order before building up the database, as it's so much harder to find problems when you build it all up on the idea it ought to work then have to hunt around for problems. That's just general advice. Maybe this one is too far gone to make that practicable, but I hope you understand what is going on and why the working code is not working when it runs live.

Anyway, the code that I used and that worked for me when breakpointed was :
Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.     With Me
  3.         .Parent.SHIPTOT = Switch(.SumOfShipWeight > 400, 24.95, _
  4.                                  .SumOfShipWeight > 250, 19.95, _
  5.                                  .SumOfShipWeight > 100, 13.95, _
  6.                                  .SumOfShipWeight > 80, 11.95, _
  7.                                  .SumOfShipWeight > 60, 9.95, _
  8.                                  .SumOfShipWeight > 40, 8.95, _
  9.                                  .SumOfShipWeight > 20, 6.95, _
  10.                                  .SumOfShipWeight > 10, 5.5, _
  11.                                  True, 3.5)
  12.     End With
  13. End Sub
Jul 19 '10 #25

NeoPa
Expert Mod 15k+
P: 31,121
I'm looking into this further as I think I've found one of those fairly rare debugging anomalies. It works when in debug mode due to timing issues. Running live it seems the value of Me.SumOfShipWeight is not yet updated by the time it is referred to.
Jul 19 '10 #26

NeoPa
Expert Mod 15k+
P: 31,121
I did some more checking, but kept coming up against problems with code that doesn't compile.

If you want to provide a properly working copy I'll look some more, but I'm just wasting my time on this.
Jul 19 '10 #27

P: 67
@NeoPa
Sorry to cause problems. I tried to strip data from the tables to make it small enough to fit the attachment parameters.
Also, customer redefined how Shipping on Form was to be determined. It is based on the Subtotal Cost of the Subform. However, my question remains.
THe Shipping value on the form is based on the =Sum([Extended Price]) of the subform. (She is charging shipping based on the subtotal of items bought, not weight). This is how the Shipping Costs Table is contructed. How can I have the form do the lookup, rather than having the user do it?
As for the failure to debug, forgive me, but when I tried I could not understand the result.
THanks to all for trying. I will give your AfterUpdate a try tomorrow. Thanks for spending so much time with a rookie.
Jul 19 '10 #28

NeoPa
Expert Mod 15k+
P: 31,121
Bigdaddrock: As for the failure to debug, forgive me, but when I tried I could not understand the result.
I assume you mean to say compile rather than debug here. Never mind. It's a good idea to mention that though. Failure to get it to compile isn't a big problem if you just say what difficulties you have. In your case you are using a call to a procedure which doesn't exist anywhere in your code. I may try to look into that for you tomorrow (too late tonight now). Sometimes though, once one problem has been found and fixed, there is scope to show another one. This is why it is always a good discipline to compile the code, not just for posting, but before any testing.

I'll post here how I get on anyway (at least if I get enough time tomorrow - It's my football day on Tuesdays so I get in quite late).
Jul 20 '10 #29

patjones
Expert 100+
P: 931
I echo most of NeoPa's observations.

Initially, I could not compile the code. It complained about If IsLoaded("Orders Subform") Then, which occurs in three places; I changed this to If Forms![Orders Subform].IsLoaded Then and it compiled fine. I then made a text box called "txtShipping" on the parent form, and for the After Update event of the subform put

Expand|Select|Wrap|Line Numbers
  1. Private Sub Form_AfterUpdate()
  2.  
  3. Forms![DH ADD ORDERS w/Tax].txtShipping = DMin("[SHIPPING COSTS]![ShipCost]", "[SHIPPING COSTS]", "[SHIPPING COSTS]![IF LESS THAN] > " & [Forms]![DH ADD ORDERS w/Tax].[SUBTOTAL])
  4.  
  5. End Sub

It is in this respect that I got the same thing as NeoPa: in breakpoint mode the text box updates correctly. During normal execution, it stops because it does not have a value for [Forms]![DH ADD ORDERS w/Tax].[SUBTOTAL]...which is definitely a timing issue. I believe this is all rooted in [DH Orders Subform B]!ItemCost not updating fast enough.

I did not really look at the rest of the project, but I think this gets to the heart of the immediate issue.

Pat
Jul 20 '10 #30

Post your reply

Sign in to post your reply or Sign up for a free account.