A few comments:
1 - Date is a reserved word in MS Access – it should not be used as a field name (i.e. [Date]), this could case problems with your code.
http://support.microsoft.com/default.aspx?scid=kb;en-us;286335
2 – Since you are executing the DCount with the same arguments, using s variable would increase performance. Instead of the DCount executing 3 times, it would only execute once.
3 – A case statement in place of multiple ElseIf may be easier to follow.
====================================================
Function test()
'If Statment
Dim myCount As Integer
myCount = DCount("[Date] & [Period]", "tblRIC1Bookings", "[Date] = #" & [Date] & "# AND [Period] = " & [Period] & " ")
If myCount = 0 Then
MsgBox ("This booking is acceptable")
DoCmd.GoToRecord , , acNewRec
ElseIf myCount = 1 Then
MsgBox ("This booking is acceptable, but please be aware that there's another class in the RIC at this time.")
DoCmd.GoToRecord , , acNewRec
ElseIf myCount = 2 Then
MsgBox ("Sorry, the RIC is booked out completely, please consider another date.")
End If
End Function
====================================================
Function test2()
'Case Statement
Dim myCount As Integer
myCount = DCount("[Date] & [Period]", "tblRIC1Bookings", "[Date] = #" & [Date] & "# AND [Period] = " & [Period] & " ")
Select Case myCount
Case 0
MsgBox ("This booking is acceptable")
DoCmd.GoToRecord , , acNewRec
Case 1
MsgBox ("This booking is acceptable, but please be aware that there's another class in the RIC at this time.")
DoCmd.GoToRecord , , acNewRec
Case 2
MsgBox ("Sorry, the RIC is booked out completely, please consider another date.")
End Select
End Function
