J
JohnB
Hi John (or whoever else reads this).
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.
I want to change this input window but I cant work out how to do it. I now
want the StartDateTime to be after 09.00 hrs on the previous Tuesday and the
EndDateTime to be earlier than midday next Thursday. I hope that is clear.
Just to add a further complication, once Ive got this working I wondered if
I could modify it so that the relevant days and times were user adjustable.
This could be useful where a public holiday meant that the window had to be
extended temporarily. I was thinking of using a table to hold the various day
and time values and put DLookups in the code that take the values from the
table each time its used.
The Users could then use a form to change the day and time values in the
table, whenever necessary. Anyway, 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.
I want to change this input window but I cant work out how to do it. I now
want the StartDateTime to be after 09.00 hrs on the previous Tuesday and the
EndDateTime to be earlier than midday next Thursday. I hope that is clear.
Just to add a further complication, once Ive got this working I wondered if
I could modify it so that the relevant days and times were user adjustable.
This could be useful where a public holiday meant that the window had to be
extended temporarily. I was thinking of using a table to hold the various day
and time values and put DLookups in the code that take the values from the
table each time its used.
The Users could then use a form to change the day and time values in the
table, whenever necessary. Anyway, 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]