Can user override a calculated field?

[Guest]

Is it possible to have a calculated date field that can also be overriden if
user wants to provide a user inputted date instead?
My formula in the control source is currently:
Contract_end_dat
=IIf([optChoose]=1,DateAdd("m",nz([Term_mos]),nz([Acceptance_date]))-1,DateAdd("ww",nz([Term_mos]),nz([Acceptance_date]))-1)

However, is it better to create a separate end_date_override field or simply
have user type over the calculated end date? This calculated
contract_end_date is also used in the subform detail screen.

Thank you!
gg

 

[Marshall Barton]

Is it possible to have a calculated date field that can also be overriden if
user wants to provide a user inputted date instead?
My formula in the control source is currently:
Contract_end_date
=IIf([optChoose]=1,DateAdd("m",nz([Term_mos]),nz([Acceptance_date]))-1,DateAdd("ww",nz([Term_mos]),nz([Acceptance_date]))-1)

However, is it better to create a separate end_date_override field or simply
have user type over the calculated end date? This calculated
contract_end_date is also used in the subform detail screen.

No, you can not modify the value of a calculated control.

When you want to calculate a value based on other user
entered values, you need to do it in the other three
control's AfterUpdate event.

Just so you don't end up with three copies of the same code,
you should create a function to do the calculation:

Function CalcEndDate(wm, term, accept)
If IsNull(wm) Or Isnull(term) Or IsNull(accept) Then
Exit Function
End If
Select Case wm ' 1 - months, 2 - weeks
Case 1
CalcEndDate = DateAdd("m", term, accep)-1
Case 2
CalcEndDate = DateAdd("ww", term, accep)-1
Case Else
MsgBox "Invalid optChoose: " & wm
End Select
End Function

Then the other control's AfterUpdate event procedures would
just be:

If Not IsNull(Me.Contract_end_date) Then
Me.Contract_end_date = _
CalcEndDate(Me.optChoose,Me.Term_mos, _
Me.Acceptance_date)
End If

 

[Guest]

Thank you for your suggestions, Marshall!

I'd like to put all of this code in the Module like you had suggested last
time; however, I don't currently have any code in a Module. Can this be the
only grouping of code I put in one? The rest of my code are under the Event
procedures. Could you please explain when you put it under a regular
function vs. public function vs. a sub function? Thank you!!!

Is it possible to have a calculated date field that can also be overriden if
user wants to provide a user inputted date instead?
My formula in the control source is currently:
Contract_end_date
=IIf([optChoose]=1,DateAdd("m",nz([Term_mos]),nz([Acceptance_date]))-1,DateAdd("ww",nz([Term_mos]),nz([Acceptance_date]))-1)

However, is it better to create a separate end_date_override field or simply
have user type over the calculated end date? This calculated
contract_end_date is also used in the subform detail screen.

No, you can not modify the value of a calculated control.

When you want to calculate a value based on other user
entered values, you need to do it in the other three
control's AfterUpdate event.

Just so you don't end up with three copies of the same code,
you should create a function to do the calculation:

Function CalcEndDate(wm, term, accept)
If IsNull(wm) Or Isnull(term) Or IsNull(accept) Then
Exit Function
End If
Select Case wm ' 1 - months, 2 - weeks
Case 1
CalcEndDate = DateAdd("m", term, accep)-1
Case 2
CalcEndDate = DateAdd("ww", term, accep)-1
Case Else
MsgBox "Invalid optChoose: " & wm
End Select
End Function

Then the other control's AfterUpdate event procedures would
just be:

If Not IsNull(Me.Contract_end_date) Then
Me.Contract_end_date = _
CalcEndDate(Me.optChoose,Me.Term_mos, _
Me.Acceptance_date)
End If

 

[Larry Linson]

If the controls are all on the same form, the code can be put in that form's
module. Functions return a value -- if you need to return a value, as in

dblX = myFunc(dblY, dblZ)

otherwise use a Sub... it can set arguments, but can't be used to return a
value to a variable. Public is used to make the variable, sub, or function
"visible" outside its immediate environment (e.g., from another module).
Public is not needed in Standard Modules (the ones you see under Modules in
the Database window), but it doesn't hurt to use it there. "Private" will
keep the Sub or Function from being visible outside its own module. Private
is default for Form or Report Modules; Public is default for Standard
Modules.

Larry Linson
Microsoft Access MVP

Thank you for your suggestions, Marshall!

I'd like to put all of this code in the Module like you had suggested last
time; however, I don't currently have any code in a Module. Can this be
the
only grouping of code I put in one? The rest of my code are under the
Event
procedures. Could you please explain when you put it under a regular
function vs. public function vs. a sub function? Thank you!!!

Is it possible to have a calculated date field that can also be
overriden if
user wants to provide a user inputted date instead?
My formula in the control source is currently:
Contract_end_date
=IIf([optChoose]=1,DateAdd("m",nz([Term_mos]),nz([Acceptance_date]))-1,DateAdd("ww",nz([Term_mos]),nz([Acceptance_date]))-1)

However, is it better to create a separate end_date_override field or
simply
have user type over the calculated end date? This calculated
contract_end_date is also used in the subform detail screen.

No, you can not modify the value of a calculated control.

When you want to calculate a value based on other user
entered values, you need to do it in the other three
control's AfterUpdate event.

Just so you don't end up with three copies of the same code,
you should create a function to do the calculation:

Function CalcEndDate(wm, term, accept)
If IsNull(wm) Or Isnull(term) Or IsNull(accept) Then
Exit Function
End If
Select Case wm ' 1 - months, 2 - weeks
Case 1
CalcEndDate = DateAdd("m", term, accep)-1
Case 2
CalcEndDate = DateAdd("ww", term, accep)-1
Case Else
MsgBox "Invalid optChoose: " & wm
End Select
End Function

Then the other control's AfterUpdate event procedures would
just be:

If Not IsNull(Me.Contract_end_date) Then
Me.Contract_end_date = _
CalcEndDate(Me.optChoose,Me.Term_mos, _
Me.Acceptance_date)
End If

 

[Marshall Barton]

The form's module is the place where the event procedures
are, so you will definitely have the module.

Since my suggested procedure will not be used anywhere else,
the form's module is the best place to put it.

I think Larry's response answered the remainder of your
question, but if you are still having trouble, post back.
--
Marsh
MVP [MS Access]

I'd like to put all of this code in the Module like you had suggested last
time; however, I don't currently have any code in a Module. Can this be the
only grouping of code I put in one? The rest of my code are under the Event
procedures. Could you please explain when you put it under a regular
function vs. public function vs. a sub function? Thank you!!!

Is it possible to have a calculated date field that can also be overriden if
user wants to provide a user inputted date instead?
My formula in the control source is currently:
Contract_end_date
=IIf([optChoose]=1,DateAdd("m",nz([Term_mos]),nz([Acceptance_date]))-1,DateAdd("ww",nz([Term_mos]),nz([Acceptance_date]))-1)

However, is it better to create a separate end_date_override field or simply
have user type over the calculated end date? This calculated
contract_end_date is also used in the subform detail screen.

No, you can not modify the value of a calculated control.

When you want to calculate a value based on other user
entered values, you need to do it in the other three
control's AfterUpdate event.

Just so you don't end up with three copies of the same code,
you should create a function to do the calculation:

Function CalcEndDate(wm, term, accept)
If IsNull(wm) Or Isnull(term) Or IsNull(accept) Then
Exit Function
End If
Select Case wm ' 1 - months, 2 - weeks
Case 1
CalcEndDate = DateAdd("m", term, accep)-1
Case 2
CalcEndDate = DateAdd("ww", term, accep)-1
Case Else
MsgBox "Invalid optChoose: " & wm
End Select
End Function

Then the other control's AfterUpdate event procedures would
just be:

If Not IsNull(Me.Contract_end_date) Then
Me.Contract_end_date = _
CalcEndDate(Me.optChoose,Me.Term_mos, _
Me.Acceptance_date)
End If

 

[Guest]

I added all of the rows for the Public Function code in the Module. And then
in the form, I am referencing the CalcEndDate function under the Event
procedure as well as under properties in the EndDate control field on my form
in the row source. I get #error or a blank. I think I'm missing a step
somewhere. Help!

Thank you.

The form's module is the place where the event procedures
are, so you will definitely have the module.

Since my suggested procedure will not be used anywhere else,
the form's module is the best place to put it.

I think Larry's response answered the remainder of your
question, but if you are still having trouble, post back.
--
Marsh
MVP [MS Access]

I'd like to put all of this code in the Module like you had suggested last
time; however, I don't currently have any code in a Module. Can this be the
only grouping of code I put in one? The rest of my code are under the Event
procedures. Could you please explain when you put it under a regular
function vs. public function vs. a sub function? Thank you!!!

gg wrote:
Is it possible to have a calculated date field that can also be overriden if
user wants to provide a user inputted date instead?
My formula in the control source is currently:
Contract_end_date
=IIf([optChoose]=1,DateAdd("m",nz([Term_mos]),nz([Acceptance_date]))-1,DateAdd("ww",nz([Term_mos]),nz([Acceptance_date]))-1)

However, is it better to create a separate end_date_override field or simply
have user type over the calculated end date? This calculated
contract_end_date is also used in the subform detail screen.

No, you can not modify the value of a calculated control.

When you want to calculate a value based on other user
entered values, you need to do it in the other three
control's AfterUpdate event.

Just so you don't end up with three copies of the same code,
you should create a function to do the calculation:

Function CalcEndDate(wm, term, accept)
If IsNull(wm) Or Isnull(term) Or IsNull(accept) Then
Exit Function
End If
Select Case wm ' 1 - months, 2 - weeks
Case 1
CalcEndDate = DateAdd("m", term, accep)-1
Case 2
CalcEndDate = DateAdd("ww", term, accep)-1
Case Else
MsgBox "Invalid optChoose: " & wm
End Select
End Function

Then the other control's AfterUpdate event procedures would
just be:

If Not IsNull(Me.Contract_end_date) Then
Me.Contract_end_date = _
CalcEndDate(Me.optChoose,Me.Term_mos, _
Me.Acceptance_date)
End If

 

[Marshall Barton]

Is this a case of " if a little is good, a lot must be
better"? ;-)

The control property sheet is where you specify how to deal
with an event, normally, an event **property** should be set
to [Event Procedure]. The code belongs in the event
**procedure** in the form's module.

If that doesn't clarify things for you, please post the
**form** module's code (use Copy/Paste to avoid adding
typos). It's normally useful to see the names of the
controls and the names of the field in their ControlSource
property.
--
Marsh
MVP [MS Access]

I added all of the rows for the Public Function code in the Module. And then
in the form, I am referencing the CalcEndDate function under the Event
procedure as well as under properties in the EndDate control field on my form
in the row source. I get #error or a blank. I think I'm missing a step
somewhere. Help!

The form's module is the place where the event procedures
are, so you will definitely have the module.

Since my suggested procedure will not be used anywhere else,
the form's module is the best place to put it.

I think Larry's response answered the remainder of your
question, but if you are still having trouble, post back.

I'd like to put all of this code in the Module like you had suggested last
time; however, I don't currently have any code in a Module. Can this be the
only grouping of code I put in one? The rest of my code are under the Event
procedures. Could you please explain when you put it under a regular
function vs. public function vs. a sub function? Thank you!!!


gg wrote:
Is it possible to have a calculated date field that can also be overriden if
user wants to provide a user inputted date instead?
My formula in the control source is currently:
Contract_end_date
=IIf([optChoose]=1,DateAdd("m",nz([Term_mos]),nz([Acceptance_date]))-1,DateAdd("ww",nz([Term_mos]),nz([Acceptance_date]))-1)

However, is it better to create a separate end_date_override field or simply
have user type over the calculated end date? This calculated
contract_end_date is also used in the subform detail screen.


:
No, you can not modify the value of a calculated control.

When you want to calculate a value based on other user
entered values, you need to do it in the other three
control's AfterUpdate event.

Just so you don't end up with three copies of the same code,
you should create a function to do the calculation:

Function CalcEndDate(wm, term, accept)
If IsNull(wm) Or Isnull(term) Or IsNull(accept) Then
Exit Function
End If
Select Case wm ' 1 - months, 2 - weeks
Case 1
CalcEndDate = DateAdd("m", term, accep)-1
Case 2
CalcEndDate = DateAdd("ww", term, accep)-1
Case Else
MsgBox "Invalid optChoose: " & wm
End Select
End Function

Then the other control's AfterUpdate event procedures would
just be:

If Not IsNull(Me.Contract_end_date) Then
Me.Contract_end_date = _
CalcEndDate(Me.optChoose,Me.Term_mos, _
Me.Acceptance_date)
End If