By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,476 Members | 1,553 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,476 IT Pros & Developers. It's quick & easy.

Saving data from a form field to a table field

P: 98
i have a field on a form where a calculation is displayed. The field calculates the number of business days a request is due based on a priority level chosen from a drop down box.

i want to save the data in the calculated field on the form to a new field in the table that i recently created to store this number.

how would could i have this data save to this field when the form is saved?
Nov 12 '08 #1
Share this Question
Share on Google+
4 Replies


P: 68
Hi

use the 'after update' event of the combo to update the destination field. you could actually use that destination field on your form instead of the unbound box, and perform the calculation within vba.

example:

you could use the If ... then... end if method but I prefer to use select case for options like this, apparently it executes more quickly and in any case I find it easier to write


Expand|Select|Wrap|Line Numbers
  1. Private Sub ctlCombo_AfterUpdate()
  2.  
  3. select case me.ctlCombo
  4. case 1 'top priority
  5. me.txtField = me.txtField + 1 'where txtfield is a date and 1 is 1 day
  6.  
  7. case 2 'normal priority
  8. me.txtField = me.txtField + 7 
  9.  
  10. case 3 'low priority
  11. me.txtField = me.txtField + 28
  12.  
  13. end select
  14.  
  15. end sub

HTH
Kevin
Nov 12 '08 #2

missinglinq
Expert 2.5K+
P: 3,532
While there are exceptions to the rule, in most cases calculated fields should not be stored in tables, and this is not a valid exception! Unless you expect the number of days that are assigned to Top, Normal and Low Priorities to change, the value should simply be re-calculated whenever it's needed, not stored in a table. Storage space aside, a calculation such as this will always be faster than retrieving data from disk.

Welcome to Bytes!

Linq ;0)>
Nov 13 '08 #3

P: 98
Hi

use the 'after update' event of the combo to update the destination field. you could actually use that destination field on your form instead of the unbound box, and perform the calculation within vba.

example:

you could use the If ... then... end if method but I prefer to use select case for options like this, apparently it executes more quickly and in any case I find it easier to write


Expand|Select|Wrap|Line Numbers
  1. Private Sub ctlCombo_AfterUpdate()
  2.  
  3. select case me.ctlCombo
  4. case 1 'top priority
  5. me.txtField = me.txtField + 1 'where txtfield is a date and 1 is 1 day
  6.  
  7. case 2 'normal priority
  8. me.txtField = me.txtField + 7 
  9.  
  10. case 3 'low priority
  11. me.txtField = me.txtField + 28
  12.  
  13. end select
  14.  
  15. end sub

HTH
Kevin

The Calculation is not the issue, the issue is saving the number of days that is produced in to a field in the table. For example where "______" represents a text box.

Due In __5___ business days

i want to save the "5" in the table. how do i make the data save along with the rest of the record.

or rather how to i associate the field in the form to a field in the table?
Nov 13 '08 #4

P: 98
While there are exceptions to the rule, in most cases calculated fields should not be stored in tables, and this is not a valid exception! Unless you expect the number of days that are assigned to Top, Normal and Low Priorities to change, the value should simply be re-calculated whenever it's needed, not stored in a table. Storage space aside, a calculation such as this will always be faster than retrieving data from disk.

Welcome to Bytes!

Linq ;0)>

The reason i want the form to store the data is because i am going to have to alter a report to calculate the number of days that the request actuall took to complete and compare it to the number that was required.
Nov 13 '08 #5

Post your reply

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