One TextBox Not Bound to Table
in [General]
|
Prev: Error "wizard is unable to create your data access page"
Next: Alternative to GoToRecord acNewRec for Access ADP
From: ryguy7272 on 3 Mar 2010 11:20 On a form, I am doing a simple calculation based on values in two Textboxes: =[QUANTITY]*[RATE] This is a commission (TextBox is named COMM). I enter data into several Textboxes, and run a small macro to bind the data to a table. Everythign updates fine, except the COMM TextBox. How do I bind this to the table so all records update appropriately? Thanks! Ryan-- -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''.
From: Jerry Whittle on 3 Mar 2010 11:34 Usually you don't. If you have both the Quantity and Rate data stored in the table, you can compute the COMM as needed. That stops a lot of trouble. For example if someone updates the Rate in the table, then the COMM can be wrong. Another example would be storing the Date of Birth and Age fields. After a year the data in the Age field would be wrong. There are exceptions, such as invoices where you need to see something at a point in time. -- Jerry Whittle, Microsoft Access MVP Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder. "ryguy7272" wrote: > On a form, I am doing a simple calculation based on values in two Textboxes: > =[QUANTITY]*[RATE] > > This is a commission (TextBox is named COMM). I enter data into several > Textboxes, and run a small macro to bind the data to a table. Everythign > updates fine, except the COMM TextBox. How do I bind this to the table so > all records update appropriately? > > Thanks! > Ryan-- > > > -- > Ryan--- > If this information was helpful, please indicate this by clicking ''Yes''.
From: BruceM via AccessMonster.com on 3 Mar 2010 11:37 Update all what records? That calculation will multiply Quantity * Rate for the record being displayed on the form. What other records are involved? Also, are you using an unbound form, then writing the values to a table? If so, why not just bind the form to the table or a query based on the table? Further, most of the time there is no reason to store a calculation. If this is one of those unusual situations where a calculation is stored you will need to be more specific about how you are writing the values to the table. ryguy7272 wrote: >On a form, I am doing a simple calculation based on values in two Textboxes: >=[QUANTITY]*[RATE] > >This is a commission (TextBox is named COMM). I enter data into several >Textboxes, and run a small macro to bind the data to a table. Everythign >updates fine, except the COMM TextBox. How do I bind this to the table so >all records update appropriately? > >Thanks! >Ryan-- > -- Message posted via http://www.accessmonster.com
From: KenSheridan via AccessMonster.com on 3 Mar 2010 11:59 Don't store the commission in a column the table. As Quantity and Rate are stored values in other columns in the same row in the table then there is no need to store the commission in a separate row as it can always be obtained via a commuted control as you are doing, or by a computed column in a query. If you did store it in a separate column in would be functionally dependent on the two non-key columns Quantity and Rate, so the table would not be correctly normalized and at risk of inconsistent data. Non-key columns should be functionally dependent solely on the key of the table - 'The key, the whole key and nothing but the key, so help me Codd' - which Quantity and Rate are, but Comm would not be. I don't see why you need to run a macro here. All you need is a form based on the Sales (or whatever) table with two bound textboxes for Quantity and Rate, and an unbound Comm text box with your expression as its ControlSource property. If you are using an unbound form for any reason, you would insert a row into the table via code or a macro, but you'd only need to insert values into the Quantity and Rate columns, not a Comm column for the reasons described in para 1 above. Ken Sheridan Stafford, England ryguy7272 wrote: >On a form, I am doing a simple calculation based on values in two Textboxes: >=[QUANTITY]*[RATE] > >This is a commission (TextBox is named COMM). I enter data into several >Textboxes, and run a small macro to bind the data to a table. Everythign >updates fine, except the COMM TextBox. How do I bind this to the table so >all records update appropriately? > >Thanks! >Ryan-- > -- Message posted via http://www.accessmonster.com
From: ryguy7272 on 3 Mar 2010 13:26
That works! Thanks guys!! -- Ryan--- If this information was helpful, please indicate this by clicking ''Yes''. "KenSheridan via AccessMonster.com" wrote: > Don't store the commission in a column the table. As Quantity and Rate are > stored values in other columns in the same row in the table then there is no > need to store the commission in a separate row as it can always be obtained > via a commuted control as you are doing, or by a computed column in a query. > If you did store it in a separate column in would be functionally dependent > on the two non-key columns Quantity and Rate, so the table would not be > correctly normalized and at risk of inconsistent data. Non-key columns > should be functionally dependent solely on the key of the table - 'The key, > the whole key and nothing but the key, so help me Codd' - which Quantity and > Rate are, but Comm would not be. > > I don't see why you need to run a macro here. All you need is a form based > on the Sales (or whatever) table with two bound textboxes for Quantity and > Rate, and an unbound Comm text box with your expression as its ControlSource > property. > > If you are using an unbound form for any reason, you would insert a row into > the table via code or a macro, but you'd only need to insert values into the > Quantity and Rate columns, not a Comm column for the reasons described in > para 1 above. > > Ken Sheridan > Stafford, England > > ryguy7272 wrote: > >On a form, I am doing a simple calculation based on values in two Textboxes: > >=[QUANTITY]*[RATE] > > > >This is a commission (TextBox is named COMM). I enter data into several > >Textboxes, and run a small macro to bind the data to a table. Everythign > >updates fine, except the COMM TextBox. How do I bind this to the table so > >all records update appropriately? > > > >Thanks! > >Ryan-- > > > > -- > Message posted via http://www.accessmonster.com > > . > |