'Try this instead:
Private Sub Command26_Click()
On Error GoTo Err_Command26_Click
Dim strSQL As String
Dim intCount As Integer
Dim rs As Recordset
' This gets a count of all records in Bookings that have the given
parking space number
' and one of it's dates between the two new dates
strSQL = " SELECT count([Booking No]) AS C " _
& "FROM Bookings " _
& "WHERE (DateTo BETWEEN #" & [DateFrom] & "# AND #" &
[DateTo] & "# " _
& " OR DateFrom BETWEEN #" & [DateFrom] & "# AND #" &
[DateTo] & "#) " _
& " AND [Parking Space No] = " & [Parking Space No]
Set rs = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
rs.MoveFirst
If rs!C = 0 Then 'slot is free
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
cmdNew.SetFocus
' Command26.Enabled = False
Calendar2.Enabled = False
myDisplayInfoMessage = "Booking Confirmed"
Else
myDisplayWarningMessage = "This has already been booked" & vbCrLf
_
& "Please choose another"
End If
Set rs = Nothing
Exit_Command26_Click:
Exit Sub
Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click
End Sub
On Apr 3, 12:51 pm, "Immy" <iazah...@hotmail.comwrote:
On Apr 3, 5:43 pm, "Jason Lepack" <jlep...@gmail.comwrote:
Define "double booking". Show the structure of tables. Show sample
input with expected output. Here's some vba that may (probably
not...) help.
public sub JasonsSolution()
msgbox "Give more information!"
end sub
I know it doesn't work but it's the best I could come up with such
limited information.
After you give more info you could be helped.
Cheers,
Jason Lepack
On Apr 3, 12:35 pm, iazah...@hotmail.com wrote:
I use Access 2000 and I'm trying to figure out what VBA code to use to
prevent double bookings. Any help on this matter will be greatly
appreciated. Regards Immy- Hide quoted text -
- Show quoted text -
This is the code I'm using at the min and it dont work:
Private Sub Command26_Click()
On Error GoTo Err_Command26_Click
Dim strSQL As String
Dim intCount As Integer
strSQL = "[Parking Space No] = " & [Parking Space No] & "" _
& "AND [DateFrom] = #" & [DateFrom] & "#" _
& "AND [DateTo] = #" & [DateTo] & "#"
intCount = DCount("[Parking Space No]", "Parking Space", strSQL)
If intCount = 0 Then 'slot is free
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
cmdNew.SetFocus
' Command26.Enabled = False
Calendar2.Enabled = False
myDisplayInfoMessage = "Booking Confirmed"
Else
myDisplayWarningMessage = "This has already been booked" & vbCrLf
_
& "Please choose another"
End If
Exit_Command26_Click:
Exit Sub
Err_Command26_Click:
MsgBox Err.Description
Resume Exit_Command26_Click
End Sub- Hide quoted text -
- Show quoted text -