Validation rule for date.

[shiro]

Hi All,
I have a query with a date field.Then I create two more
column, Month:Format([DateField],"mmm") and
Year:Format([DateField],"yyyy").

The criteria of the month and year column are come from
cbo_month and cbo_year of a filter form.So,every time
the main form opened,it just show the records of the
selected month and year.

I want the user be able to add the records to the filtered
records but the date entered must be in range of the
selected month-year.How to do that?

Perhaps I can explain my situation clearly.
Thank's
Rgds,

Shiro

 

[Allen Browne]

You have a filter form where the user selects a month (1 - 12) and a year
(4-digit) in 2 combo boxes. You want to ensure that any value they enter
into your date field matches these combos on the filter form.

Use the BeforeUpdate event procedure of the form where entries are made to
look at the combos on the other form and see if they match. This kind of
thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If Not IsNull(Me.DateField) Then
If CurrentProject.AllForms("Form2").IsLoaded Then
With Forms("Form2")
If Month(Me.DateField) <> !cboMonth Then
Cancel = True
strMsg = strMsg & "Month doesn't match." & vbCrLf
End If
If Year(Me.DateField) <> !cboYear Then
Cancel = True
strMsg = strMsg & "Year doesn't match." & vbCrLf
End If
End With
End If
End If

If (Cancel) And (strMsg <> vbNullString) Then
strMsg = strMsg & vbCrLf & "Fix the problem, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Problem"
End If
End Sub

 

[shiro]

Thank's Mr Allen,
It works with bit modification.
Thank's

Shiro

You have a filter form where the user selects a month (1 - 12) and a year
(4-digit) in 2 combo boxes. You want to ensure that any value they enter
into your date field matches these combos on the filter form.

Use the BeforeUpdate event procedure of the form where entries are made to
look at the combos on the other form and see if they match. This kind of
thing:

Private Sub Form_BeforeUpdate(Cancel As Integer)
Dim strMsg As String

If Not IsNull(Me.DateField) Then
If CurrentProject.AllForms("Form2").IsLoaded Then
With Forms("Form2")
If Month(Me.DateField) <> !cboMonth Then
Cancel = True
strMsg = strMsg & "Month doesn't match." & vbCrLf
End If
If Year(Me.DateField) <> !cboYear Then
Cancel = True
strMsg = strMsg & "Year doesn't match." & vbCrLf
End If
End With
End If
End If

If (Cancel) And (strMsg <> vbNullString) Then
strMsg = strMsg & vbCrLf & "Fix the problem, or press <Esc> to
undo."
MsgBox strMsg, vbExclamation, "Problem"
End If
End Sub
--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

Hi All,
I have a query with a date field.Then I create two more
column, Month:Format([DateField],"mmm") and
Year:Format([DateField],"yyyy").

The criteria of the month and year column are come from
cbo_month and cbo_year of a filter form.So,every time
the main form opened,it just show the records of the
selected month and year.

I want the user be able to add the records to the filtered
records but the date entered must be in range of the
selected month-year.How to do that?