Hi Wobbles
First, assuming you have named the function "CheckClash", you may not give
the module the same name.
You could name the module "mdlCheckClash", or, because you will undoubtedly
want to add other functions to your application later, you could name it
something like "mdlGeneralCode".
Now, if you haven't done so already, replace the table and field names in my
sample code with your own names. However, first I strongly advise you to
change the names of your fields. "End" is a reserved word in VBA and you
will likely run into problems down the track if you keep that as a field
name. I suggest changing "Start" and "End" to "StartDate" and "EndDate".
You say you don't have room numbers yet, so your OpenRecordset should read:
Set rs = db.OpenRecordset( "Select * from tEvent where " _
& Format(dtStart, "\#mm\/dd\/yyyy\#") & "<[EndDate] and " _
& Format(dtEnd, "\#mm\/dd\/yyyy\#") & ">[StartDate]", _
dbOpenForwardOnly)
This allows for a StartDate to be the same as a previous EndDate, which
would allow a check-out in the morning and a check-in later the same day, as
is the usual practice with hotel rooms. If you can't allow that, then
change the < and > to <= and >=.
You can also drop "lSuite from the function declaration, so it reads:
Function CheckClash( dtStart as Date, dtEnd as Date ) As Boolean
Now, the idea if the function is that you pass it two dates and it displays
a message and returns True if there is a clash.
Because you can enter only one date at a time, you want it to check after
the first date is entered that it does not fall in the middle of an existing
booking. For example, when the StartDate is entered or changed, you want to
check for a clash with StartDate and EndDate, but if EndDate has not yet
been entered you want to treat Enddate as being the same as StartDate.
The Nz function is handy for this. If the first argument is Null (blank) it
returns the second argument:
Nz(EndDate, StartDate)
So after StartDate has been entered you should use:
CheckClash( StartDate, Nz(EndDate, StartDate) )
and after EndDate has been entered you should use:
CheckClash( Nz(StartDate, EndDate), EndDate )
Now, I assumed that you were typing these dates into textboxes, hence my
suggestion that you use the BeforeUpdate event. However, you say you are
using a popup calendar. You must have some code to take the selected date
from the calendar and write it into your textbox or field. It is that code
that needs to call the CheckClash function.
If you need further help, post the code that is doing this part.
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Wobbles said:
Graham i'm being thick (out of my depth really) I have a form called
[Booking
Application] it has fields called [Start] and [End].
I select the date(s) from a pop up calender and upon exiting i am trying
to
run the query. The information you supplied i'm sure does the task but how
do
i implement it the existing bookings are held in a table named [tEvent]
also
with field names of [Start] and [End]. I think i have created a module as
you
instructed and named it [CheckClash].
For the timebeing there is no reference to any suite as only one is
available anyway
Help Please , My hairs falling out
:
Assuming that part of your booking record includes a room number, then
three
criteria must all be met in order for there to be a clash:
- the requested room/suite must match the booked room/suite
- the requested check-in date must be before the booked check-out date
- the requested check-out date must be after the booked check-in date
In terms of some VBA code, this can be written as:
If DCount("*", "Bookings", [SuiteNumber] & "=[SuiteNumber] and " _
& Format([StartDate], "\#mm\/dd\/yyyy\#") & "<[EndDate] and " _
& Format([EndDate], "\#mm\/dd\/yyyy\#") & ">[StartDate]") > 0
then
MsgBox "Sorry - this clashes with an existing booking"
End If
You could write this into a function which actually retrieves the clash
record and gives a more helpful message:
[WARNING: air code - there may be some syntax errors or typos
]
Function CheckClash( lSuite as long, dtStart as date, dtEnd as Date ) _
As boolean
Dim db as DAO.Database
Dim rs as DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset( "Select * from Bookings where " _
& lSuite & "=[SuiteNumber] and " _
& Format(dtStart, "\#mm\/dd\/yyyy\#") & "<[EndDate] and " _
& Format(dtEnd, "\#mm\/dd\/yyyy\#") & ">[StartDate]", _
dbOpenForwardOnly)
With rs
if .RecordCount > 0 then
MsgBox "Sorry, this clashes with an existing booking from " _
& !StartDate & " to " !EndDate
CheckClash = True
End If
.Close
End With
Set rs = nothing
set db = nothing
End Sub
You can then use the BeforeUpdate event of your date textboxes:
Private Sub StartDate_BeforeUpdate(Cancel as Integer)
Cancel = CheckClash( SuiteNumber, StartDate, Nz(EndDate, StartDate) )
End Sub
and
Private Sub EndDate_BeforeUpdate(Cancel as Integer)
Cancel = CheckClash( SuiteNumber, Nz(StartDate, EndDate), EndDate )
End Sub
--
Good Luck!
Graham Mandeno [Access MVP]
Auckland, New Zealand
Allen Browne saved my brain yesterday with his date validation query
(see
"Booking out a Guest suite , avoiding double booking in Access" posted
14/02)
his query works without question. I now have a related question. If i
input a
start date i need to check the start date is not part of an already
booked
period. then assuming it isnt i then need to check the dates from that
start
date till the date i enter next being the end date. If i must i can
just
input both and check at the end but how do i send the dates to the
query
and
get either a ok or not ok result
