I'm a beginner and my problem is the following:

I have to check if the date selected by the user matches to the tblFestivity of the field Festivity_Date. If these to matches for now I want a display to the user a normale Message Box.

In this image you can see the field where the user insert the date:

enter image description here

In this one you can see the table tblFestivity and the field Festivity_Date:

enter image description here

Here you can see that it works for the first record: enter image description here

The code for now is this one:

Private Sub Date_Flow_Exit(Cancel As Integer)
If Date_Flow = DLookup("[Festivity_Date]", "tblFestivity", "[Date_Flow]=Form![Date_Flow]") Then
    MsgBox "è un giorno festivo"
End If 
End Sub

This code show me the message box I want but only for the first record, it doesn't check the others records in the table tblFestivity.

Date_Flow is the name of the textBox in the first immage.

My question is: how can I check al the occurrence instrad of only the first one?  Hope you can understand what I'm asking, thanks in advice.

CodePudding user response：

If I understand correctly, you want to display a message for each occurrence of the date entered by the user?

If that is the case, then I would suggest you to open recordset with results and iterate through each one and display message.

Private Sub Date_Flow_Exit(Cancel As Integer)
Dim rs As Recordset
Dim dt As Date
dt = Me.Date_Flow
Set rs = CurrentDb.OpenRecordset("SELECT tblFestivity.Festivity_Date FROM tblFestivity WHERE (((tblFestivity.Festivity_Date) =#" & dt & "#));") 'Opens recordset only with dates entered in textbox

With rs
    If .RecordCount > 0 Then
        .MoveFirst
        Do While Not .EOF
            MsgBox ("è un giorno festivo")
            .MoveNext
        Loop
    Else
        MsgBox ("There are no festivities on this day")
    End If
End With
End Sub

Let me know it that is what you wanted?

CodePudding user response：

It's not finding match because WHERE CONDITION syntax is wrong and not using field name in criteria. Correct syntax for referencing field or control on form is Forms!formname!fieldORcontrol name. Criteria should use name of field matching to value on form.

If Not IsNull(DLookup("[Festivity_Date]", "tblFestivity", "[Festivity_Date] = Forms!formname!Date_Flow")) Then

Or

If DCount("*", "tblFestivity", "[Festivity_Date]=Forms!formname!Date_Flow") > 0 Then