Room Availability Query

[kellya via AccessMonster.com]

I have a room availabilty form that uses a calendar control to enter the
requested reservation Date via two combo boxes. I have a command button that
is supposed to check my reservations table for available rooms based on the
two dates. I have an sql query that already searches for collisions:

SELECT *
FROM tblReservations
WHERE Room_Num NOT in (select Room_Num from tblReservations where
RequestedStartDate <= EndBookingDate and RequestedEndDate >= StartBookingDate
);

Question #1
How do I program my button to return the rooms available for reservations
based on the two dates Entered?

Question #2

Can I modify my sql in order to only return reservation that are confirmed?
(note I have a confirmation field in my reservations table)

 

[Guest]

Post the entire code behind the button. I don't know what you're doing with
this SQL statement? Are you building this in VBA or is the from the Query?

To answer question one, I would have the button open another form modal that
displayed the information provided the info wasn't empty.

Question two yes, just add that field and the value of true in your critera.

 

[kellya via AccessMonster.com]

Post the entire code behind the button. I don't know what you're doing with
this SQL statement? Are you building this in VBA or is the from the Query?

To answer question one, I would have the button open another form modal that
displayed the information provided the info wasn't empty.

Question two yes, just add that field and the value of true in your critera.

I have a room availabilty form that uses a calendar control to enter the
requested reservation Date via two combo boxes. I have a command button that

[quoted text clipped - 15 lines]

Can I modify my sql in order to only return reservation that are confirmed?
(note I have a confirmation field in my reservations table)

Sorry, but I'm fairly new at this, could you break that down for me in some
steps?

 

[Guest]

Go into the form that has the button you described and get into the design.
Right click on the button and select properties. In the properties window go
to the Event tab, look for the OnClick option and click the ... next to that
row. You may have to select that row by placing your cursor into the field
to get the ... to appear. Copy all of the code is in that module.

Post the entire code behind the button. I don't know what you're doing with
this SQL statement? Are you building this in VBA or is the from the Query?

To answer question one, I would have the button open another form modal that
displayed the information provided the info wasn't empty.

Question two yes, just add that field and the value of true in your critera.

I have a room availabilty form that uses a calendar control to enter the
requested reservation Date via two combo boxes. I have a command button that

[quoted text clipped - 15 lines]

Can I modify my sql in order to only return reservation that are confirmed?
(note I have a confirmation field in my reservations table)

Sorry, but I'm fairly new at this, could you break that down for me in some
steps?

 

[kellya via AccessMonster.com]

Go into the form that has the button you described and get into the design.
Right click on the button and select properties. In the properties window go
to the Event tab, look for the OnClick option and click the ... next to that
row. You may have to select that row by placing your cursor into the field
to get the ... to appear. Copy all of the code is in that module.

Here's the code so far:

Option Compare Database
Option Explicit
Dim cboOriginator As ComboBox

Private Sub cboStartDate_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
' Note which combo box called the calendar
Set cboOriginator = cboStartDate
' Unhide the calendar and give it the focus
ocxcalendar.Visible = True
ocxcalendar.SetFocus
' Match calendar date to existing date if present or today's date
If Not IsNull(cboOriginator) Then
ocxcalendar.Value = cboOriginator.Value
Else
ocxcalendar.Value = Date
End If
End Sub

Private Sub cboEndDate_MouseDown(Button As Integer, Shift As Integer, X As
Single, Y As Single)
' Note which combo box called the calendar
Set cboOriginator = cboEndDate
' Unhide the calendar and give it the focus
ocxcalendar.Visible = True
ocxcalendar.SetFocus
' Match calendar date to existing date if present or today's date
If Not IsNull(cboOriginator) Then
ocxcalendar.Value = cboOriginator.Value
Else
ocxcalendar.Value = Date
End If
End Sub

Private Sub cmdavailability_Click()

End Sub

Private Sub ocxCalendar_Click()
' Copy chosen date from calendar to originating combo box
cboOriginator.Value = ocxcalendar.Value
' Return the focus to the combo box and hide the calendar and
cboOriginator.SetFocus
ocxcalendar.Visible = False
' Empty the variable
Set cboOriginator = Nothing
End Sub
Private Sub Command13_Click()
On Error GoTo Err_Command13_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command13_Click:
Exit Sub

Err_Command13_Click:
MsgBox Err.Description
Resume Exit_Command13_Click

End Sub
Private Sub Command14_Click()
On Error GoTo Err_Command14_Click


Screen.PreviousControl.SetFocus
DoCmd.DoMenuItem acFormBar, acEditMenu, 10, , acMenuVer70

Exit_Command14_Click:
Exit Sub

Err_Command14_Click:
MsgBox Err.Description
Resume Exit_Command14_Click

End Sub

 

[kellya via AccessMonster.com]

any ideas stiil stuck

 

[kellya via AccessMonster.com]

any ideas still stuck

 

[Guest]

Kelly,

I having a problem understanding how you excute your SQL code. I can see
from this that you have a couple of buttons to set the dates using a calendar
& a find button. But none of these will excute an SQL string... Do you
load the forms info with the dates, then you .... to make the SQL check the
table...

 

[kellya via AccessMonster.com]

Kelly,

I having a problem understanding how you excute your SQL code. I can see
from this that you have a couple of buttons to set the dates using a calendar
& a find button. But none of these will excute an SQL string... Do you
load the forms info with the dates, then you .... to make the SQL check the
table...
[quoted text clipped - 82 lines]

I just needed a button to represent the searc query it could have easily said
ok or execute. My problem is I don't no how to execute the query once the
button is clicked. How will I insert the requested dates the user enters in
to the sql? And how will I display the results

 

[kellya via AccessMonster.com]

Kelly,

I having a problem understanding how you excute your SQL code. I can see
from this that you have a couple of buttons to set the dates using a calendar
& a find button. But none of these will excute an SQL string... Do you
load the forms info with the dates, then you .... to make the SQL check the
table...
[quoted text clipped - 82 lines]

I just needed a button to represent the search query it could have easily
said ok or execute. My problem is I don't know how to execute the query once
the button is clicked. How will I insert the requested dates the user enters
in to the sql? And how will I display the results