Yes, you do need to make the txtenddate text box bound to
the enddate field in the table.
Since this now lloks more like a special purpose procedure,
let's make it a sub more like your original idea. Place the
code in the form's module:
Sub CalcEndDate()
If IsNull(optFrame) Or IsNull(txtTerm) Or _
IsNull(txtStartDate) Or Not IsNull(txtEndDate) Then
Exit Sub
End If
Select Case optFrame
Case 1
txtEndDate = DateAdd("m",txtTerm,txtStartDate)
Case 2
txtEndDate = DateAdd("ww",txtTerm,txtStartDate)
Case Else
MsgBox "invalid option value: " & optFrame
End Select
End Function
Note that optFrame is the name of the option group frame
that contains the option1 and option2 check boxes. The
option 1 check box must have its OptionValue property set to
1 and option2 set to 2. txtTerm, txtStartDate and
txtEndDate are the names of the text boxes bound to the
Term, StartDate and EndDate fields respecitvely. (It is a
good practice for the controls to have a different name from
the field they are bound to.)
Now create AfterUpdate procedures for the optFrame, txtTerm
and txtStartDate controls and insert this line into all
three procedures:
CalcEndDate
This way, the procedure will not do anything if the option,
term and start have not yet been entered. It will also
lleave any value that is already there the way it is.
--
Marsh
MVP [MS Access]
Thank you for your step-by-step process. Appreciate it!
I will remove my current Enddate calc from the "Enddate" field's Control
Source row under Properties->All tab. Then, do I add the Public function
CalcEndDate to the AfterUpdate event procedure in the Properties->All tab
under the Enddate field? Users will have the capability to override this
enddate as an input so I think I have to store it somewhere, correct? (back
into the table??) Also, do I need to declare the optionframe variable
initially before using it?
Marshall Barton said:
What you store in the table depends on other factors.
Generally, you would not save a calculated value such as
enddate. You should just save the values used to calculate
it. On the other hand, if this enddate calculation is being
used as a default date that can be edited by a user, then
you do need to save it.
I think I would create a function even for a calculation
that simple:
Public Function CalcEndDate(optionframe, term, start)
Select Case optionframe
Case 1
CalcEndDate = DateAdd("m",term,start)
Case 2
CalcEndDate = DateAdd("ww",term,start)
Case Else
MsgBox "invalid option value: " & optionframe
End Select
End Function
Then, if you are not going to save the value to a field in
the table, you could use it in the enddate text box's
control source expression:
=CalcEndDate(Frame1, term, startdate)
But if the users can change the value, you should use the
the option, term, and startdate control's AfterUpdate event:
enddate = CalcEndDate(Frame1, term, startdate)
gg wrote:
Could you please start me off? It's not a complicated calc but will depend
if the option1 is selected ->in months or option2->in weeks. My calc is
simply:
if option1, then
enddate=DateAdd("m",[term],[startdate])
else
enddate=DateAdd("ww",[term],[startdate]])
end if
Do I need to store option1 results in a field in a table? Currently it is
just the option button selected.
gg wrote:
If I have 2 option buttons (mos, weeks), 2 fields=term and start date, and
enddate=the field I want to calculate, do I put the Event procedure to calc
the enddate using the results of the 2 option buttons, and the 2 fields in an
After Update event procedure or do I put it all under the Control Source
under properties?
:
Depends on how complex the calculation is. If it's not too
complicated, put it in the control source. If it requires a
VBA procedure, then create a function to do the calculation
and set the control source to =functionname().
You could put it in the AfterUpdate event procedure of
the check and text box controls, but that would mean three
copies of essentially the same code.
