J
JohnB
John (or whoever else may be able to help).
Some months ago you kindly replied to a post of mine where I asked how I
could limit a Users entry in DateTime fields. The project that I needed that
for stalled but may be just about to start again. So I wonder if you could
help me further. At the time I though of using separate fields for Date and
Time but am now convinced that using a single field is the way to go.
Ive repeated the code you supplied below and, as you can no doubt work out,
my original request was that Users be restricted to entering a StartDateTime
after midnight on the previous Wednesday and an EndDateTime that is before
midnight next Wednesday.
This is now a bit more complex. The project relates to Users who complete a
form which requests the recording of TV and Radio programmes. The Users have
a period of time, called the Request Period, during which they can enter
their requests for the recording of programmes being transmitted during a
Recording Period. Each Request Period runs from 09.00 am on a Tuesday to
12.00 midday on the Thursday immediately afterwards. The corresponding
Recording Period for each Request Period is from 00.01 hrs (ie first thing
in the morning) on the following Saturday, to midnight on the next Friday. I
hope that is clear.
I want to be able to monitor the Users input, to stop them entering a
StartDateTime which is outside the limits for the Recording Period which
corresponds to the Request Period that they are currenty in.
Just to add a further complication, once Ive got this working I wondered if
I could modify it so that the days and times relevant to the Request Period
were adjustable. This could be useful where a public holiday meant that the
window had to be
extended temporarily, say, to midday on the Friday. I was thinking of using
a table to hold the various Reqest Period day and time values and put
DLookups in the code that take the values from the table each time the code
runs.
The Administrator could then use a form to change the day and time values in
the
table, whenever necessary.
Anyway, John, heres your original code and Id be grateful for any further
help you can give.
JohnB
If you use a single field, you can put in the textbox's BeforeUpdate
event:
Private Sub txtStartDateTime_BeforeUpdate(Cancel as Integer)
If txtStartDateTime < DateAdd("d", 1 - Weekday(Date(), vbWednesday), _
Date()) Then
MsgBox "Date must be later that midnight last Wednesday", vbOKOnly
Cancel = True
End If
If txtStartTime > DateAdd("d", 8 - Weekday(Date(), vbWednesday), _
Date()) Then
MsgBox "Date must be before midnight next Wednesday", vbOKOnly
Cancel = True
End If
End Sub
If you insist on having two fields, you must add txtStartDate to
txtStartTime before doing these tests on the sum.
John W. Vinson[MVP]
Some months ago you kindly replied to a post of mine where I asked how I
could limit a Users entry in DateTime fields. The project that I needed that
for stalled but may be just about to start again. So I wonder if you could
help me further. At the time I though of using separate fields for Date and
Time but am now convinced that using a single field is the way to go.
Ive repeated the code you supplied below and, as you can no doubt work out,
my original request was that Users be restricted to entering a StartDateTime
after midnight on the previous Wednesday and an EndDateTime that is before
midnight next Wednesday.
This is now a bit more complex. The project relates to Users who complete a
form which requests the recording of TV and Radio programmes. The Users have
a period of time, called the Request Period, during which they can enter
their requests for the recording of programmes being transmitted during a
Recording Period. Each Request Period runs from 09.00 am on a Tuesday to
12.00 midday on the Thursday immediately afterwards. The corresponding
Recording Period for each Request Period is from 00.01 hrs (ie first thing
in the morning) on the following Saturday, to midnight on the next Friday. I
hope that is clear.
I want to be able to monitor the Users input, to stop them entering a
StartDateTime which is outside the limits for the Recording Period which
corresponds to the Request Period that they are currenty in.
Just to add a further complication, once Ive got this working I wondered if
I could modify it so that the days and times relevant to the Request Period
were adjustable. This could be useful where a public holiday meant that the
window had to be
extended temporarily, say, to midday on the Friday. I was thinking of using
a table to hold the various Reqest Period day and time values and put
DLookups in the code that take the values from the table each time the code
runs.
The Administrator could then use a form to change the day and time values in
the
table, whenever necessary.
Anyway, John, heres your original code and Id be grateful for any further
help you can give.
JohnB
If you use a single field, you can put in the textbox's BeforeUpdate
event:
Private Sub txtStartDateTime_BeforeUpdate(Cancel as Integer)
If txtStartDateTime < DateAdd("d", 1 - Weekday(Date(), vbWednesday), _
Date()) Then
MsgBox "Date must be later that midnight last Wednesday", vbOKOnly
Cancel = True
End If
If txtStartTime > DateAdd("d", 8 - Weekday(Date(), vbWednesday), _
Date()) Then
MsgBox "Date must be before midnight next Wednesday", vbOKOnly
Cancel = True
End If
End Sub
If you insist on having two fields, you must add txtStartDate to
txtStartTime before doing these tests on the sum.
John W. Vinson[MVP]