Refresh a query NOT linked to a form or report

[liddlem]

Hi there
I am making a booking system where a user enters
StartDate, EndDate (Form Header)
House , Room , UserID (Form Footer)

The Header and footer are not linked. The Footer simply displays all the existing bookings for said House/Room/Date combination. (Date being all dates between the StartDate and EndDate)
BUT (surprise, surprise)users don't look at this to check if a booking already exists.

Also - It seems pointless to have the users enter the same data (House, Room, UserID) in 6 times (one for each day that they want to book the room.

So I am trying to automate the process.

Therefore, I append each 'new' record (that the users adds in this session) into a temporary table. (House / Room / UserID / StartDate)....note, no end date because I need a separate record for each day.

I have a query that is supposed to check the EXISTING bookings with the temp table.
My code (paraphrased here for simplicty) says

do while StartDate < EndDate
- if Qry_CheckForClash returns 0 records then '(uses a dlookup)
- append from TEMP to BOOKINGS.
- - else
- compile an alert message to the user (appending the Room No and Date each iteration)
- In Qry_CheckForClash, increase the StartDate by +1
- end if
loop

My problem is that after the last step (increase StartDate by +1) the call to Qry_CheckForClash still returns the initial StartDate. But when I check the TEMP table, the StartDate has changed.

So how do I REFRESH or REQUERY a query that is not associated to a form or report?

Thanks

 

[namliam]

Why create 6 records for 6 days in the first place? Why not create one record with a StartDate and EndDate

 

[liddlem]

This Is A Good Question Which I Have Previously Dismissed As Being Impractical For The Following Reasons.

Our Organisation Has 12 Houses In Remote Communities. Each House Has Up To 3 Rooms. Most Rooms Sleep 2 People, But Some Could House 6 People (in Sleeping Bags) If Neccesary.

Genrally, A Staff Member Books A Room, (But In Extreme Circumstances, They may Be Required To Share A Room.)
If A Booking Is Made For A Room From 14th To The 19th, But Another User Wanted To Book From The 15th To 17th (Or Perhaps The 18th To The 23rd), How Would I Check That The Bookings Dont Clash? Unless I Had A Record For Each Day?

 

[namliam]

In pseudo code:
Where start date new booking < end date old booking
or in your sample
18th < 19th ==>> Conflicting/clashing booking

Depending on the housing situation you may want to book each person seperately or be able to book as groups, assuming you would want to keep groups together when possible
A record would be something like:
Column / value
House 1
Room 1
People 2
StartDate 14th
EndDate 19th

Worst case scenario you can "force" day records using a cartesian product, but in my oppinion shouldnt be needed to "break" a database for this problem

 

[liddlem]

Hi Mailman
I like where you're going with this, but think that there are some practicalities that are forcing me into a record per house/room/date scenario.
I was hoping that by giving only the relevant info in my first post, would simply solve the query refresh issue, but I i am now tempted to consider your suggestion of 1 record per booking. So I guess that I need to explain all the intracacies to give you the big picture.

The organisation is made up of a collection of programs.
These programs service VERY remote communties.
(A community being collection of between 5 and 100 houses. This could be anywhere up to 600km on a dirt track from the nearest fuel stop, let alone police station, store or post office.)

One house may be owned by the Youth program, whereas another may be owned by the Nutrition program.

Up to now, when users have needed to go out, they have simply asked the receptionist if there is still capacity in a given house.
She has kept [a rather confusing] spreadsheet of the bookings.

Some of the issues are:
After traveling down the dirt track for 7 hours in 40C degree temperatures, the first person to arrive usually takes the best room (why wouldnt they.) - but they may have been the last to make a book in. Or someone else, who arrives the day after, may have booked weeks ahead, and perhaps was told that they are the first one in. But now they have to leep in their swag. (obviously things change.) - So everyone is asking for a more structured/formal booking system.

There are some people who are (let us say) a little sensitive about what gender they are sharing a house with. So each time that a booking has been made/requested, I need to send an email to ALL who have booked advising that the gender split is now Mn and Fn.

The proper bedrooms, usually have a double bed. But if 2 people are travelling out, they each want their own room. However, In EXTREME circumstances, some people may choose to share a room.

Then.....Not all sleeping accomodation is in a bedroom!
Some houses may have a large lounge or store room that sometimes gets used to 'swag it'. So i need a way to manage the maximum capacity for such rooms. (Almost a case for assigning BEDS (bed slots) to a booking?)

Usually, people book in from a Monday to Thursday, but some people may book for 4 straight weeks.
I need to send a booking list for the entire house

Billing can be complex.
The owning program gets free accomodation.
Other (internal) programs get a reduced rate.
External guests get a full rate - unless they are there at the request of the owning program.(in which case, it free)

Finally - there is a rule that says: the owning program has the right to 'trump' any other bookings if neccessary.

Now that you have al this info, would you still keep one record per booking?
Or is there a way for force a refresh of my query?

 

[namliam]

Typicaly bookings are made per period per group, rather than per day per person... The only issue you may have is that you might need some way to identify individuals inside the group if you need to email them (or possibly only email the booking person, unless the booking person can list individudual emails and persons)

If you see the need to do it per day, I guess ultimately it is your choice.. though a query + DLookup already looks suspicious... Why not do a dlookup in code only? or only execute a query or run the query for the entire booking period in one go to identify clashes for the entire period in one go?

rst.Requery
Should re-execute your query
Worst case you can rst.Close your recordset and re-open it.

Billing IMHO doesnt look that confusing, but this is comming from someone dealing with thousands of different rates depending on many different factors.

 

[liddlem]

Thanks for your assistance namliam
I think that I will pursue the path of booking by period, however, I cannot bypass the booking by 'guest' as (to quote george orwell) 'some pigs are better than others' and I need a way to deal with them.

 

[gemma-the-husky]

why would a query increment a date counter? I can't see that a query would do this.

what code do you have in place to do this?

 

[liddlem]

Hi Dave
Its A Simple Update Query. (bkgdate = Bkgdate +1)

I Was Asking The User For A Start And End Date, And Then Append Each Day From Start To End.
But It Seems That Namliam Has Set Me On The Right Track.
It Looks Like I Will Have A Solution Shortly.