Hi!
Sorry if I missed this in other posts and answers. There are several examples how to create bookings for customers and ressources.
So far so easy to achieve with relations and form-assistant.
What I want to have is to avoid double bookings. A ressource must not be available if the desired timespan is (partially) already booked.
For usability purposes I only want to offer those ressources in the booking form, which are available at the selected FROM and TO range (DateTime).
A Constraint error when you try to save a overlapping booking is not very user friendly. I want to avoid that upfront.
What I have done so far (simplified)
Customers Table
Ressources Table
Bookings Table (with foreign keys to Customer and Ressources)
A query "availableRessources" to search for Ressources which are NOT already reserved (full or overlapping) at a given FROM and TO range. (FROM and TO are parameters to the query)
A bookings Form consisting of
- a header area to search and select the customer who wants to book a ressource and 2 input fields for FROM and TO DateTime (unbound as this is a input field, prefilled with default values)
But now I don't know how to proceed with the main form.
Option 1 - the simple UI: have a subform based on the query "availableRessources" to pick (1 to many) rows. The idea is to insert records in Bookings Table for each selected Row (if mulitselction of rows is possible) using customerID, FROM and TO date from unbound input fields from header.
Issue with that approach: I am not able to pass the FROM and TO dates to the query, I am always prompted to input the date manually.
This may be related to the unbound form?
(its just an empty form where I placed my desired fields which are filled with data based on a pop up form [customerID] and a VBAScript [prefilled FROM and TO date - changeable])
Option 2 - ShopStyle UI: have a VBAScript with query to search for availableRessources and pass parameters FROM and TO to it.
This already works and gives me a list of RecordSets for each available ressource.
Now I need to know how to visualize this in the form dynamically.
Either
2a-Simple) Just a simple List with checkboxes or a table with multi-selectable-rows to pick from (like Option 1).
2b-Fancy) grouped by ressource Type (e.g. to show "10 Bikes of Model X", "2 Bikes of Model Y" and "20 Helmets in size L" are available - either as a list, table or a nice image per RessourceType), where each group may be added up to <maxNumberOfAvailableRessourcesOfType> to the cart (via ADD buttons or input field for number of items).
At the end, with a click on a button I want to write the records to the bookings table (I dont want to have already written data while clicking through the dates or adding items to the cart)
Any idea of how to achieve this?
