VBA Syntax needed For/Do..Until/With - not sure

[ARW17]

I have the following which checks to see if the user has a personal day scheduled on the date the user input.

If txtRmvP <> [SFPersDaySch].[Form]![EventDate] Then
MsgBox "You do not have a Personal Day scheduled on this date."
End If

Right now, it’s checking the first instance and then concluding that the user does not have a Personal Day scheduled on that date. The user may have more than 1 personal days scheduled. How do I modify this to check all of the scheduled event dates before concluding that there isn’t one scheduled on that day?

 

[RickXL]

Hi,

This would be the way I might do it:

Sub Test()
    Dim i       As Long
    Dim found   As Boolean
    
    With ThisWorkbook.Worksheets("Sheet1")
        found = False
        For i = 1 To .Cells(.Rows.Count, "A").End(xlUp).Row
            If [<condition><condition>condition] Then found = True
        Next
        If Not found Then MsgBox "You do not have a Personal Day scheduled on this date."
    End With
End Sub

Note: [condition] <condition>needs to be replaced with your actual code. Also, I assumed that the data is in column A of Sheet1

Basically, you create a variable (found) and set it to True if something s found. You can then use that later on. An option would be to add one to a count every time a match is found then you could report how many matches there had been.


I hope this helps,</condition></condition></condition>

 

[ARW17]

Thanks for the response, RickXL, but I'm doing this in Access.

 

[RickXL]

Oh dear.

Note to self, I really must concentrate on the forum heading before posting!

I was doing quite well, I had not given an Excel answer to an Access question for some time.

Apologies,