vbams-accessdlookup

DLookUp to return all occurrences instead of only the first one

I check if the date selected by the user matches the tblFestivity of the field Festivity_Date.
If these match I display a Message Box.

The field where the user inserts the date:
enter image description here

The table tblFestivity and the field Festivity_Date:
enter image description here

It works for the first record:
enter image description here

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 shows the message box but only for the first record, it doesn't check the other records in the table tblFestivity.

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

How can I check all the occurrences instead of only the first one?

Solution

  • 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