Double Bookings

[iazahoor]

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

[Immy]

On Apr 3, 5:35 pm, iazah...@hotmai l.com wrote:

I use Access 2000 and I'm trying to figure out what VBA code to use to
prevent double bookings. The system I am trying to creating is a booking system for a car park.

Any help on this matter will be greatly

appreciated. Regards Immy

[Jason Lepack]

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...@hotmai l.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

[Immy]

On Apr 3, 5:41 pm, "Immy" <iazah...@hotma il.comwrote:

On Apr 3, 5:35 pm, iazah...@hotmai l.com wrote:

I use Access 2000 and I'm trying to figure out what VBA code to use to
prevent double bookings. The system I am trying to creating is a booking system for a car park.

Any help on this matter will be greatly

appreciated. Regards Immy- Hide quoted text -

- Show quoted text -

I would also like to add that the problem I keep having is how to
prevent two people from booking the same parking space on the same
date. Immy

[Immy]

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...@hotmai l.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 -

Tables are as follows
Customer
- No.
Title, First Name, Surname, House No, Street, Town,
County, Post Code, tel no.
Boat
-No.
-Name
Parking Space
- no. and Location
Bookings
No, Cust no, Boat No, Parking Space no, Date from and
Date to.

Is this of any help?

[Immy]

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...@hotmai l.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_C lick

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.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
cmdNew.SetFocus
' Command26.Enabl ed = False
Calendar2.Enabl ed = False
myDisplayInfoMe ssage = "Booking Confirmed"

Else
myDisplayWarnin gMessage = "This has already been booked" & vbCrLf
_
& "Please choose another"

End If

Exit_Command26_ Click:
Exit Sub

Err_Command26_C lick:
MsgBox Err.Description
Resume Exit_Command26_ Click

End Sub

[Jason Lepack]

'Try this instead:

Private Sub Command26_Click ()
On Error GoTo Err_Command26_C lick

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.OpenR ecordset(strSQL , dbOpenDynaset)
rs.MoveFirst
If rs!C = 0 Then 'slot is free
DoCmd.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
cmdNew.SetFocus
' Command26.Enabl ed = False
Calendar2.Enabl ed = False
myDisplayInfoMe ssage = "Booking Confirmed"
Else
myDisplayWarnin gMessage = "This has already been booked" & vbCrLf
_
& "Please choose another"
End If
Set rs = Nothing
Exit_Command26_ Click:
Exit Sub
Err_Command26_C lick:
MsgBox Err.Description
Resume Exit_Command26_ Click
End Sub

On Apr 3, 12:51 pm, "Immy" <iazah...@hotma il.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...@hotmai l.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_C lick

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.DoMenuIte m acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70
cmdNew.SetFocus
' Command26.Enabl ed = False
Calendar2.Enabl ed = False
myDisplayInfoMe ssage = "Booking Confirmed"

Else
myDisplayWarnin gMessage = "This has already been booked" & vbCrLf
_
& "Please choose another"

End If

Exit_Command26_ Click:
Exit Sub

Err_Command26_C lick:
MsgBox Err.Description
Resume Exit_Command26_ Click

End Sub- Hide quoted text -

- Show quoted text -