VBA Code - Help required

[chrisgunny]

Hi All,
Really need some help with a simple booking database I have created, its all finished but I am no good when it comes to VBA and how to write code, would somebody be able to write some code for me to prevent double bookings?

Attached is a copy of the database.

The database is a simple booking system, I need some code to do the following:

When a user completes the form "BookingsF" and clicks submit, the code runs and looks up table "BookingsT" to confirm the following:

Asset ID being booked in the form does not already appear as a record in the table, with overlapping requested from date or requested to date (e.g its doesnt allow double booking of the same asset), there is also one other consideration to allow for the code needs to ignore any bookings in the table that have the booking cancelled field ticked.

Any help would be grately appriecated.

 

[billmeye]

Chris, I added code in your BookingsF form to attempt to address your double booking question. I would have just given you some helpful VBA but I also needed to switch the code to send the email from the Macro to VBA so I could control it's firing to prevent it in the case of a double booking. Take a look and let me know if it works for you.

 

[chrisgunny]

Hi Billmeye,
Wow thank you so much, been really struggling with this but you have smashed it like a boss

My team will be pleased they have a system to book equipment now, thanks again

P.s this is my first ever database made from scratch.

 

[billmeye]

Good luck with it. It's always great when the DB start's to match your expectations.

 

[chrisgunny]

Hi Guys,
Could someone take a look at this code below? Billmeye kindly helped me with this last year to prevent duplicate bookings, however I have made some changes to the database and the code below no longer prevents double bookings in between a date range.

For example, the below picture shows how the database lets me book in between a range of the 21st April - 25th April - Interestingly it wont let me book on the 21st April or the 25th April.

Private Sub Form_BeforeUpdate(Cancel As Integer)
If Me.NewRecord Then
If DCount("*", "BookingsT", "[AssetID]=" & Me.Combo87 & " And (([RequestedDateFrom]>=#" & Me.RequestedDateFrom & "# And [RequestedDateFrom]<=#" & Me.RequestedDateTo & "#) Or ([RequestedDateTo]>=#" & Me.RequestedDateFrom & "# And [RequestedDateTo]<=#" & Me.RequestedDateTo & "#)) And [BookingCancelled]=0") > 0 Then
MsgBox "Duplicate entry"
Cancel = 1
StopEmail = 1
Else
StopEmail = 0
End If
Else
'In case you decide to allow editing of past records
If Me.Combo87 <> Me.Combo87.OldValue Or Me.RequestedDateFrom <> Me.RequestedDateFrom.OldValue Or Me.RequestedDateTo <> Me.RequestedDateTldValue Then
If DCount("*", "BookingsT", "[AssetID]=" & Me.Combo87 & " And (([RequestedDateFrom]>=#" & Me.RequestedDateFrom & "# And [RequestedDateFrom]<=#" & Me.RequestedDateTo & "#) Or ([RequestedDateTo]>=#" & Me.RequestedDateFrom & "# And [RequestedDateTo]<=#" & Me.RequestedDateTo & "#)) And [BookingCancelled]=0") > 1 Then
MsgBox "Duplicate entry"
Cancel = 1
StopEmail = 1
Else
StopEmail = 0
End If
Else
StopEmail = 0
End If
End If
If Not IsNull(Me.BookedBy) Then Me.BookedBy = UCase(Me.BookedBy)
End Sub

 

[chrisgunny]

Current database attached

 

[chrisgunny]

Can anyone help?