Prevent the slection of any date unless it's a Sunday

A

Al Campagna

JK,
Several ways to do that.
Use the Before Update event of your date field...
Private Sub MyDate_BeforeUpdate(Cancel As Integer)
If WeekDay(MyDate) <> 1 Then
MsgBox "Must be a Sunday..."
Cancel = True
MyDate.Undo
End If
End Sub
--
hth
Al Campagna
Microsoft Access MVP
http://home.comcast.net/~cccsolutions/index.html

"Find a job that you love... and you'll never work a day in your life."
 
J

JK

Thx for responding - and this concept would be great if I could get to work -
problem is, I can't.

Maybe you could assist me further?

I have a form [DemandLog] bound to a table [tblDemandLog] using a query
[SQL] (no joined tables.)

On form [DemandLog] I have a tab control with a specific tab for each day of
the week - each day or tab has its own sub form. Each sub form is bound to
the same table [tblDemandLogDetails].

On the main tab I have the user enter a date. Then, when an item is selected
using a combo box in any of the week day sub forms, the control source in the
date field for each new record is (=[Forms]![DemandLog]![txtWeekDate]+1) for
Monday - +2 for Tues, +3 for Wed, etc.

If the user enters anything but a Sunday date on the main tab, all of the
sub form dates will be incorrect.

The date field on the main tab is called txtWeekDate and it's bound to a
field in the tblDemandLog table called WeekDate.

I tried this and a couple other variations but have not been able to get
your example to work. Maybe I'm overlooking something? Maybe I should
approach this situation in a different way? Any help would be appreciated.
Thx so much.

If Weekday(txtWeekDate) <> 1 Then
MsgBox "Must be a Sunday..."
Cancel = True
txtWeekDate.Undo
End If
 
J

John W. Vinson

If the user enters anything but a Sunday date on the main tab, all of the
sub form dates will be incorrect.

The date field on the main tab is called txtWeekDate and it's bound to a
field in the tblDemandLog table called WeekDate.
Click to expand...

This seems like a really complicated form to do something that should be
simpler, but...!

Consider having a Combo Box instead of the textbox txtWeekDate. Base it on a
Sundays table, which you can very easily create using Fill... Series in Excel;
just have every Sunday from now for the next twenty years or so. If the users
will only be filling in future dates, you could base the combo on a query such
as

SELECT SundayDate FROM Sundays
WHERE SundayDate >= Date()
ORDER BY SundayDate;

Or you could have it select some reasonable range of dates. That way the user
can PICK a guaranteed-Sunday date, rather than being allowed to type any date
and getting their hand slapped if they mistype.
 
J

JK

Great idea. Worked perfectly... Thank you!

John W. Vinson said:
This seems like a really complicated form to do something that should be
simpler, but...!

Consider having a Combo Box instead of the textbox txtWeekDate. Base it on a
Sundays table, which you can very easily create using Fill... Series in Excel;
just have every Sunday from now for the next twenty years or so. If the users
will only be filling in future dates, you could base the combo on a query such
as

SELECT SundayDate FROM Sundays
WHERE SundayDate >= Date()
ORDER BY SundayDate;

Or you could have it select some reasonable range of dates. That way the user
can PICK a guaranteed-Sunday date, rather than being allowed to type any date
and getting their hand slapped if they mistype.
Click to expand...
 
Top