Creating Fields in a Form from a query's SQL data...

[SilverFang]

So, I'm trying to make a hotel database, and am currently trying to create a form so the user can input a "Reservation Start" date, a "Reservation End" date, then click a button to bring up their results in a subform or something similar.



I created a query to look-up available rooms using SQL, having used this thread as a guide: https://www.accessforums.net/showthread.php?t=49891

The only issue I'm having now is that in the form I've now created, the fields taken from the query still ask for the "Reservation Start" and "Reservation End" as pop-ups, as it does in the query. Is there any way I can change these SQL expressions into textboxes (preferably with the date/time format) in the form instead, so a user doesn't have to keep closing and opening the form to input new dates? Or should I use a completely different approach to achieve what I'm trying to do?

If any more information is needed, please let me know.

Thanks in advance.

[ranman256]

in the query, put the cursor in the criteria under the date field column,
click the BUILDER button. (on the toolbar, magic wand with an elipsis)
drill down in the objects: database, forms, loaded forms, your form, your text box
ok

like: forms!myForm!txtStartDate

this should read the form date into the query, but you want something like:
[startDate] >= forms!myForm!txtStartDate and [EndDate] <= forms!myForm!txtEndDate

[ssanfu]

<snip>The only issue I'm having now is that in the form I've now created, the fields taken from the query still ask for the "Reservation Start" and "Reservation End" as pop-ups, as it does in the query. Is there any way I can change these SQL expressions into textboxes (preferably with the date/time format) in the form instead, so a user doesn't have to keep closing and opening the form to input new dates? <snip>

Yes, it is possible to use text boxes on a form. It would help A LOT if you would post your dB so we don't have to guess and can see exactly what you have.

[SilverFang]

This is the database model: (Thinking about deleting tblSnag at a later point)

At present, I only have one completed form, which simply displays everything from tblGuest. My next step is one for tblBooking.

I created a query purely through SQL using the thread I mentioned in the first post. Here is the code:

PARAMETERS [Reservation Start] DateTime, [Reservation End] DateTime;
SELECT tblRoom.Room, tblRoom.[Room Type]
FROM tblRoom
WHERE (((tblRoom.Room) Not In (SELECT DISTINCT Room FROM
(
SELECT tblRoom.Room, tblBooking.[Date of Arrival], tblBooking.[Date of Departure], tblBooking.Unusuable
FROM tblBooking, tblRoom
WHERE
tblBooking.Room = tblRoom.Room
And
(
((([tblBooking].[Date of Arrival]) Between [Reservation Start] AND [Reservation End]))
Or
((([tblBooking].[Date of Departure]) Between [Reservation Start] And [Reservation End]))
Or
((([tblBooking].[Date of Arrival])< [Reservation Start]) And (([tblBooking].[Date of Departure])>[Reservation End]))
Or
((([tblBooking].[Unusuable]) = True))
Or
((([tblBooking].[Date of Departure]) = [Reservation Start]))
Or
((([tblBooking].[Date of Arrival]) = [Reservation End]))
)
)
)));

When the query runs, it'll ask me to input "Reservation Start" and "Reservation End", then will list out all the available rooms within the specified period.

Rather than doing this, I want to change both the "Reservation Start" and "Reservation End" data into fields on a form with the date/time format, so the user manually inputs them into the form before seeing the results in a subform. I haven't created the subform yet; the form I intend to put them in is currently blank.

I hope this makes things a bit clearer this time round.

[isladogs]

I believe your query will be read only. If so, does that matter?

[SilverFang]

I want to try and achieve this in a form rather than a query if need be. It's not very user-friendly to have to manually type the date in a small box like that, so it would be better if I could achieve this letting the user select the date from the mini-calendar display it shows when adjusting a field under the date/time format.

I also want this list of available rooms to be used in the form for new bookings, so a user making a new booking can simply select the room they want out of those that are available.

[isladogs]

Editing data should always be done using forms as users should never have direct access to tables and queries.

I understood, perhaps wrongly, that you were using the screenshot in post #4 as your form record source.
I was asking whether that was read only.

[SilverFang]

The screenshot just shows the tables I'm using. As far as the form goes, I'm trying to figure out what would be better to use as the record source; the query I created (SQL shown in the quote), or try to replicate the query in a new form using tblBooking as the record source. Thing is, I'm not sure which is more practical to do, or how to go about this when selecting the date/time fields, which is why I created this thread.

[isladogs]

I suggest you have a form and subform.
The main part of the form would have a combo for room type, two textboxes for dates (start/end) and a check availability button.
Click the button to show the subform of matching rooms not booked between those dates and where Unusable =False.

However, I believe your query is far more complicated than it needs to be.

If you can post a cut down version of your database with some sample non confidential data, I'll try and simplify it for you.

NOTE I strongly recommend you remove all spaces from your field names