By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
457,710 Members | 1,134 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 457,710 IT Pros & Developers. It's quick & easy.

Double Bookings

P: n/a
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

Apr 3 '07 #1
Share this Question
Share on Google+
6 Replies


P: n/a
On Apr 3, 5: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. 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
Apr 3 '07 #2

P: n/a
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

Apr 3 '07 #3

P: n/a
On Apr 3, 5:41 pm, "Immy" <iazah...@hotmail.comwrote:
On Apr 3, 5: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. 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
Apr 3 '07 #4

P: n/a
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 -
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?

Apr 3 '07 #5

P: n/a
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

Apr 3 '07 #6

P: n/a
'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 -

Apr 3 '07 #7

This discussion thread is closed

Replies have been disabled for this discussion.