Saving data from a form field to a table field

[trixxnixon]

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?

[Kevin Wilcox]

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

Private Sub ctlCombo_AfterUpdate()
 
select case me.ctlCombo
case 1 'top priority
me.txtField = me.txtField + 1 'where txtfield is a date and 1 is 1 day
 
case 2 'normal priority
me.txtField = me.txtField + 7 
 
case 3 'low priority
me.txtField = me.txtField + 28
 
end select
 
end sub

HTH
Kevin

[missinglinq]

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

[trixxnixon]

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

Private Sub ctlCombo_AfterUpdate()
 
select case me.ctlCombo
case 1 'top priority
me.txtField = me.txtField + 1 'where txtfield is a date and 1 is 1 day
 
case 2 'normal priority
me.txtField = me.txtField + 7 
 
case 3 'low priority
me.txtField = me.txtField + 28
 
end select
 
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?

[trixxnixon]

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.