Friends, I have a subform with some date fields.
How do I not let you record a date weekend date or a holiday?
It has some simple code I can use?
Thanks for helping.
Friends, I have a subform with some date fields.
How do I not let you record a date weekend date or a holiday?
It has some simple code I can use?
Thanks for helping.
What date would you want to record? How are dates entered - textbox, date picker? Don't think can prevent entering the weekend date but can use code to validate it.
Easy to check the entered date to see if falls on weekend. Use WeekDay() function to return the day of the week.
Holidays are trickier. Create a table that has records for holiday dates.
If WeekDay([input date]) = 7 Or WeekDay([input date]) = 1 Or Not IsNull(DLookup("HolDate", "Holidays", "HolDate=#" & [input date] & "#")) Then
'do somethng
End If
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
June7, thanks for help. Only DLookup is always null.
I created a table called "Holidays" and a single field called "HolDate" (date / time). I put a record with
date 07/09/2012.
I
typed in my subform field named "Date1" 07/09/2012.
Then I put vba stop in:
I declared a variable
dim test as date
test = DLookup("HolDate", "Holidays", "HolDate=#" & [Me.Date1] & "#")
But the variable is always null in this case the variable test had to have the value: 07/09/2012.
Where am I wrong?
Thank you.
Don't know. I tested this with my db and it works.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
How DLookup was not working for me, I had to do this:
Private Sub Date1_BeforeUpdate(Cancel As Integer)
Dim lookDate As Date
Dim Counter As Integer
Dim Db As Database, rs As Recordset, sql As String
Set Db = CurrentDb
Set rs = Db.OpenRecordset("SELECT HolDate FROM Holidays")
If rs.BOF Then
Exit Sub
Else
rs.MoveFirst
While Not rs.EOF
lookDate = rs.Fields(0)
If lookDate = Me.Date1 Then
Counter = 1
End If
rs.MoveNext
Wend
End If
If Weekday(Me.Date1) = 7 Or Weekday(Me.Date1) = 1 Or Counter = 1 Then
Cancel = True
Me.Date1.Undo
MsgBox "Please enter a working day!"
Exit Sub
End If
End Sub
It worked perfectly, with only several lines of code.
Thanks for helping.