DateAdd function in form

[Guest]

i'm trying to compute a date in the future based upon the following:

a) user input current date
b) interval
c) interval type

using DateAdd function.

thus far, my code is a follows:

Private Sub NewAppntmnt_AfterUpdate()
Me.NewAppntmnt.Requery
Dim CrntAppt As Date
Dim IntervalType As String
Dim Interval As Integer
NewAppntmnt = DateAdd(Me.IntervalType, Me.Interval, Me.CrntAppt)
End Sub

currently, using my copy of the production database, all my variables above
are unbound. ultimately, the would appear in the table underlying the form.

nothing is happening as i input CrntAppt in a text control and select
IntervalType and Interval from their respective comboboxes.

can anybody help this newbie?

thanks for any bandwidth!

 

[tina]

comments inline.

i'm trying to compute a date in the future based upon the following:

a) user input current date
b) interval
c) interval type

using DateAdd function.

thus far, my code is a follows:

Private Sub NewAppntmnt_AfterUpdate()
Me.NewAppntmnt.Requery

why are you requerying this control? is it a calculated control - that is,
is there an expression in the ControlSource property? if so, you can't set
the value this way without first setting the ControlSource property to a
fieldname, or to nothing.

Dim CrntAppt As Date
Dim IntervalType As String
Dim Interval As Integer

why are you creating variables for IntervalType and Interval? you're not
using them in this procedure.

NewAppntmnt = DateAdd(Me.IntervalType, Me.Interval, Me.CrntAppt)

assuming that by "NewAppntmnt", you mean "Me.NewAppntmnt", this line should
work as long as there are values entered in the Me.IntervalType,
Me.Interval, and Me.CrntAppt controls. and that is assuming that you're
controlling what is entered in those controls. if not, i would suggest a
combo box for Me.IntervalType control, and input masks for Me.Interval and
Me.CrntAppt controls. also, suggest you test for values in all three
controls at the beginning of the procedure, to make sure the DateAdd()
function doesn't err out or return an unexpected value.

hth

 

[Guest]

tina,

my code's now:

Private Sub NewAppntmnt_AfterUpdate()
Dim CrntAppt As Date
Me.NewAppntmnt = DateAdd(Me.IntervalType, Me.Interval, CrntAppt)
End Sub

a little trimmer than before.

i've got the essentially same code in the form:

Private Sub Form_Current()
ToggleColor
Dim CrntAppt As Date
Me.NewAppntmnt = DateAdd(Me.IntervalType, Me.Interval, Me.CrntAppt)
End Sub


still, i can't seem to understand why when you pretend to be a user that's
changed their mind about the value of the interval and/or type, the CrntAppt
value doesn't change automatically; i added the three controls (IntervalType,
Interval, CrntApptto into the underlying table of the form's)?

ted

 

[tina]

okay, dummy me. i forgot to ask why you're running this code from the
AfterUpdate event of the control NewAppntmnt. after all, you're updating the
NewAppntmnt control programmatically with this code - when are you updating
the control manually to trigger the AfterUpdate event?

suggest you write a sub to calculate the value of NewAppntmnt, as

Private Sub isNewAppt()

Me.NewAppntmnt = DateAdd(Me.IntervalType, Me.Interval, CrntAppt)

End Sub

(note: you don't need the line
Dim CrntAppt As Date
you're not using that variable in the procedure, unless you left out part of
your procedure code when you posted it.)

it's easier to maintain and debug code if you only write it once, and call
it in multiple procedures, instead of writing it separately in each
procedure. so call the code from the form's Current event, as

Private Sub Form_Current()

ToggleColor
isNewAppt

End Sub

call it the same way from the AfterUpdate event of the IntervalType control,
and the Interval control. that will cause the NewAppntmnt value to be
updated when the user changes his mind about the value of the interval
and/or type.

i'm just wondering what you're ultimately trying to accomplish here. are
your users trying to, for instance, set a new appointment for x number of
day, weeks, months or years in the future - based on the current appointment
date? if that's the case, then i assume the current appointment date is
saved somewhere in a table in the database? and wouldn't you want the new
appointment date saved in a table, also? if so, then you can calculate the
new appointment date "on the fly" without needing to save the interval type
or interval, then save the new date; you shouldn't have to recalculate it
every time the record is displayed (no need to call the calculation
procedure from the form's Current event) - only when the user decides to
change it.

hth

 

[Guest]

okay, dummy me. i forgot to ask why you're running this code from the
AfterUpdate event of the control NewAppntmnt. after all, you're updating the
NewAppntmnt control programmatically with this code - when are you updating
the control manually to trigger the AfterUpdate event?

WELL, I GUESS IT'D BE COOLER NOT TO DO THAT THEN (STILL THE NEWBIE, VBA-ER,
ME)

suggest you write a sub to calculate the value of NewAppntmnt, as

Private Sub isNewAppt()

Me.NewAppntmnt = DateAdd(Me.IntervalType, Me.Interval, CrntAppt)

End Sub

THIS IS LIKE A SUBROUTINE (ONCE UPON A TIME I PROGRAMMED IN AN ANCIENT
PROGRAMMING LINGO CALLED FORTRAN). WHY AREN'T YOU 'ME'-ING THE CRNTAPPT
VARIABLE?

(note: you don't need the line
Dim CrntAppt As Date
you're not using that variable in the procedure, unless you left out part of
your procedure code when you posted it.)

OK

it's easier to maintain and debug code if you only write it once, and call
it in multiple procedures, instead of writing it separately in each
procedure. so call the code from the form's Current event, as

Private Sub Form_Current()

ToggleColor
isNewAppt

End Sub

GOTCHA
call it the same way from the AfterUpdate event of the IntervalType control,
and the Interval control. that will cause the NewAppntmnt value to be
updated when the user changes his mind about the value of the interval
and/or type.

I THINK I GET THAT: IN OTHER WORDS, REMOVE THE CODE FROM THE CRNTAPPT FIELD
AND PUT IT IN THE AFTER UPDATE OF THE TWO INTERVAL/INTERVAL-TYPE FIELDS WHICH
WILL CAUSE THE CRNTAPPT FIELD TO TAKE ON THE DESIRED VALUE; BUT....WOULDN'T
IT BE BETTER TO PUT IT BEHIND JUST THE INTERVAL_TYPE FIELD BY ITSELF AND NOT
THE INTERVAL FIELD AS WELL: THE FIRST TIME THE INTERVAL FIELD WERE COMPLETED,
WOULDN'T THAT ACTIVATE THE CODE AND PRODUCE A SPORATIC RESULT?

i'm just wondering what you're ultimately trying to accomplish here. are
your users trying to, for instance, set a new appointment for x number of
day, weeks, months or years in the future - based on the current appointment
date? if that's the case, then i assume the current appointment date is
saved somewhere in a table in the database? and wouldn't you want the new
appointment date saved in a table, also? if so, then you can calculate the
new appointment date "on the fly" without needing to save the interval type
or interval, then save the new date; you shouldn't have to recalculate it
every time the record is displayed (no need to call the calculation
procedure from the form's Current event) - only when the user decides to
change it.

hth

OK, SO I CAN REMOVE THE INTERVAL AND INTERVAL TYPE CONTROLS FROM THE DESIGN
OF THE UNDERLYING TABLE?

 

[tina]

comments inline.

WELL, I GUESS IT'D BE COOLER NOT TO DO THAT THEN (STILL THE NEWBIE, VBA-ER,

THIS IS LIKE A SUBROUTINE (ONCE UPON A TIME I PROGRAMMED IN AN ANCIENT
PROGRAMMING LINGO CALLED FORTRAN). WHY AREN'T YOU 'ME'-ING THE CRNTAPPT
VARIABLE?

oops, that was sloppy typing. the reference should be Me.CrntAppt
btw, it's not a variable. Me.CrntAppt is a reference to the Value property
of the CrntAppt control (the Value property is the default property, so
referring to Me.CrntAppt is the same as referring to Me.CrntAppt.Value)

I THINK I GET THAT: IN OTHER WORDS, REMOVE THE CODE FROM THE CRNTAPPT FIELD
correct

AND PUT IT IN THE AFTER UPDATE OF THE TWO INTERVAL/INTERVAL-TYPE FIELDS correct

WHICH
WILL CAUSE THE CRNTAPPT FIELD TO TAKE ON THE DESIRED VALUE; correct

BUT....WOULDN'T
IT BE BETTER TO PUT IT BEHIND JUST THE INTERVAL_TYPE FIELD BY ITSELF AND NOT
THE INTERVAL FIELD AS WELL:

no. sounds like the tab order in the form puts the Interval control before
the IntervalType control, correct? consider this: what if the user enters
an Interval value, then enters an IntervalType value - then goes back to the
Interval control and changes the value? at that point you need the procedure
to run again, and recalculate the NewAppntmnt.

THE FIRST TIME THE INTERVAL FIELD WERE COMPLETED,
WOULDN'T THAT ACTIVATE THE CODE AND PRODUCE A SPORATIC RESULT?

that's why i said in the first post "suggest you test for values in all
three
controls at the beginning of the procedure, to make sure the DateAdd()
function doesn't err out or return an unexpected value."

so you could change the procedure to

Private Sub isNewAppt()

With Me
If IsNull(.Interval) And Not IsNull(.IntervalType) Then
Msgbox "To set the new appointment date, please " _
& "enter the interval."
If IsNull(.IntervalType) And Not IsNull(.Interval) Then
Msgbox "To set the new appointment date, please " _
& "enter the interval type."
Else
.NewAppntmnt = DateAdd(.IntervalType, _
.Interval, .CrntAppt)
End If
End With

End Sub

OK, SO I CAN REMOVE THE INTERVAL AND INTERVAL TYPE CONTROLS FROM THE DESIGN
OF THE UNDERLYING TABLE?

if the answers to the questions i asked are Yes, then yes - and remove the
procedure call from the form's Current event, too.

hth

 

[Guest]

gotta thank you for the xtra bandwidth, tina.
still it's a saturday and i have to get to this when i get back to the
office on monday. will be able to try it out not b4 -- wish i could though.

ted

 

[tina]

well, better try this procedure instead. the one i posted last night will
not do it.

Private Sub isNewAppt()

With Me
If IsNull(.Interval) And Not IsNull(.IntervalType) Then
Msgbox "To set the new appointment date, please " _
& "enter the interval."
ElseIf Not IsNull(.Interval) And Not IsNull(.IntervalType) Then
.NewAppntmnt = DateAdd(.IntervalType, _
.Interval, .CrntAppt)
End If
End With

End Sub

the above code assumes that Me.Interval comes before Me.Interval Type in the
tab order. when the user starts out with both controls "empty", and enters
the interval first, nothing happens. when the user then enters the interval
type, the new appointment date is calculated. if the user does not enter an
interval, and enters an interval type, the message box pops up asking for
the interval. when the user enters the interval, the new appointment date is
calculated.

hth

 

[Guest]

those lucky users ---- they don't deserve us

ted

 

[Guest]

hi again,

since i still rank as a vba newbie, i wanted to bring my question to the
forum for you:

where does this code 'go'? perhaps the answer's somewhere is this alphabet
soup of qs and as, so apologies ahead of time.

also, what's not clear from my original posting is that my users will be
'updating' the new appointment control periodically. the new appointment will
come to replace the current appointment as the basis for the new appointment
periodically. so, if your current appointment (call is 'a') is today say, and
your new (call it 'b') one is three wks from today, when your new one arrives
there will be yet another one (call that one 'c') that needs scheduling since
patients are on an ongoing drug regimen....therefore, if say the third
appointment ('c') is say 2 months after the second ('b'), i think it'll mean
having to delete the value of the current appointment ('a') and replace it
with the value of the second one ('b') in order to compute 'c'. this is going
to be a recurring process. perhaps they could use a cmdbutton that actuates a
vba script that does the task of moving the dates when the time comes?

 

[tina]

just what data are you tracking re appointments? i'd think you'd track
information about the current appointment, such as "did the patient show
up?", perhaps "which doctor saw the patient?", etc. i'd think each current
appointment would be a record in a table. the next scheduled appointment
could be a field in that table, or in a separate table. in either case,
you'd pull the "next" appointment (newest date) with a query that looks for
new appointment dates >Date(), meaning "in the future of today".

maybe you should post a description of your table(s), such as

TableName
FieldName (noting which field is the primary key)
FieldName
FieldName

as for where to put the code, you'd put it in the form module with the rest
of the code for that form. if you want to run the "new appointment"
calculation in more than one form, it would probably be better to put that
procedure in a public module - but since you're a VBA newbie, let's don't go
there.

hth

 

[tina]

oh, well, users always deserve the best programming we can offer. but i can
tell you from experience, they won't appreciate it! the better a db is
designed, the easier it is to use, the more it does automatically for the
user, and the more smoothly it runs, the more a user takes it for granted -
which is only natural. after all, you only think about electricity when it
"goes out"! the average user would never begin to guess at the time, effort
(and all too often, frustration) we invest in creating that "simple"
database they use every day. <g and shrug>

 

[Guest]

the application is intended to be a screening log of sorts of patients who
are screened for enrollment in clinical studies. getting the business process
from my users is at times a bit like having to pull teeth....what i know at
this point is that it'd be good if they could somehow make the process of
scheduling future appointments, once an enrollment date is entered into it, a
bit easier. one of them was fiddling with ms a2k and knew not what to do, so
i volunteered my services. when i put the finishing touches on the screening
applicaton, i decided i would upgrade it to enable the scheduling of future
appointments using the enrollment date. with enrollment in a study, there's a
whole slew of visits that are involved and each study can have different
visit intervals relative to the 'current appointment' (which is initially
enrollment date). at this point, i need to get them to part company with
their business process before going forward w/ this.

thanks and stay tuned.....

 

[tina]

well, you're more generous than i am, Ted! anyone who wasn't willing to
invest the time in making me intimately acquainted with their process both
before and during database development, would have to find another
developer.

come on back with future questions; you'll probably want to start a new
thread then, because i only check current threads for a short time, and
because a new thread will draw responses from more people. good luck!