Reservation System Using Microsoft Access

[Steven TK]

Hi everyone,

I wonder who can help me on the filter the Start Date and End Date. I still cannot manage to filter it.

Eg. When the user click the StartDate(combo Box as 16/7/07), the Start Date(textbox should show only 16/7/07) and this also apply to the EndDate.

Private Sub cboStartDate_Cl ick()

'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.cboSt artDate) Then
strWhere = strWhere & "(([StartDate] = '" & Me.cboStartDate & "')) OR "
End If


'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.

MsgBox "No criteria", vbInformation, "Nothing to do."

Else 'Yep: there is something there, so remove the " Or " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

Private Sub cboEndDate_Clic k()

'Purpose: Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
'Notes: 1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
we remove the trailing " AND " at the end.
' 2. The date range works like this: _
Both dates = only dates between (both inclusive. _
Start date only = all dates from this one onwards; _
End date only = all dates up to (and including this one).
Dim strWhere As String 'The criteria string.
Dim lngLen As Long 'Length of the criteria string to append to.
Const conJetDate = "\#mm\/dd\/yyyy\#" 'The format expected for dates in a JET query string.

'************** *************** *************** ***** *************** *******
'Look at each search box, and build up the criteria string from the non-blank ones.
'************** *************** *************** ***** *************** *******
'Text field example. Use quotes around the value in the string.

If Not IsNull(Me.cboEn dDate) Then
strWhere = strWhere & "(([EndDate] = '" & Me.cboEndDate & "')) OR "
End If


'************** *************** *************** ***** *************** ******
'Chop off the trailing " AND ", and use the string as the form's Filter.
'************** *************** *************** ***** *************** *******
'See if the string has more than 5 characters (a trailng " AND ") to remove.
lngLen = Len(strWhere) - 4
If lngLen <= 0 Then 'Nah: there was nothing in the string.

MsgBox "No criteria", vbInformation, "Nothing to do."

Else 'Yep: there is something there, so remove the " AND " at the end.
strWhere = Left$(strWhere, lngLen)
'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
Debug.Print strWhere

'Finally, apply the string as the form's Filter.
Me.Filter = strWhere
Me.FilterOn = True
End If
End Sub

[Steven TK]

Sorry Nairda,

I wonder where you put this code in??

I am newbie for MS Access.. Maybe u tell me the procedure that could be better.. Sorry to trouble u.

Thank Nairda,

I know how do it already.

[nairda]

Sorry Nairda,

I wonder where you put this code in??

I am newbie for MS Access.. Maybe u tell me the procedure that could be better.. Sorry to trouble u.

Not at all, Steve. This what the forum is for.
Ok, I'll give you a picture:

I have a hotel reservation Ms. Access table (RESERV.mdb). It contains some columns (CHECKINDATE, CHECKOUTDATE, ROOM_NO, GUESTNAME, etc).
And then I have this report form which I use for setting some criterias to show the report. In this case I want to show list of all guest stayed in my hotel between CHECKINDATE in combo1 and CHECKOUTDATE combo2.
So, I have two combo boxes. Combo1 is to set the beginning of the report (CHECKINDATE) i wanted to show, combo2 is for the ending of the report (CHECHOUTDATE) I wanted to show.
Beside those two combos, I also made a commandbutton (cmdshow) as a trigger for the action and a mshflexgrid to show the data. So, I put the code in cmdshow.

Private Sub cmdshow_Click()
    Dim conec As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "\DATABASE.mdb'"
    conec.CursorLocation = adUseClient
    conec.Open
 
    If (Combo1.Text = "") and (Combo2.Text = "") Then
        MsgBox "Please fill the criteria", vbExclamation
        Combo1.SetFocus
        Else
            rs.CursorLocation = adUseClient
            rs.Open "SELECT * FROM RESERV WHERE CHECKINDATE>=#" & CDate(Combo1.Text) & "# AND CHECKOUTDATE<=#" & CDate(Combo2.Text) & "# '", conec, adOpenDynamic, adLockOptimistic
            Set MSHFlexGrid1.DataSource = rs
    End If
    rs.Close
End Sub

It's better if you could find the DT Picker. If you could, the code above will be:

"SELECT * FROM RESERV WHERE CHECKINDATE>=#" & DTPicker1.Value & "# AND CHECKOUTDATE<=#" & DTPicker2.Value & "# ", conec, adOpenDynamic, adLockOptimistic

You can do a right click on DTPicker to show its properties and change the dateformat to suit your database's dateformat

OR you can use the code sent by hariharanmca (thank you):

"SELECT * FROM GUESTLIST WHERE Date>= ' " & Format(DTPicker1.Value,"MM/dd/yyyy") & " ' AND Date<= ' " & Format(DTPicker1.Value,"MM/dd/yyyy")  & " ' "

To hariharanmca: How to show DT Picker when you don't have "Microsoft Windows Common Control 2.6.0" in your Project Components? Steve don't have it. I have no idea about this, because I've already had it when I installed my VB6.

Thank you n Good luck!

Nairda

[Steven TK]

Not at all, Steve. This what the forum is for.
Ok, I'll give you a picture:

I have a hotel reservation Ms. Access table (RESERV.mdb). It contains some columns (CHECKINDATE, CHECKOUTDATE, ROOM_NO, GUESTNAME, etc).
And then I have this report form which I use for setting some criterias to show the report. In this case I want to show list of all guest stayed in my hotel between CHECKINDATE in combo1 and CHECKOUTDATE combo2.
So, I have two combo boxes. Combo1 is to set the beginning of the report (CHECKINDATE) i wanted to show, combo2 is for the ending of the report (CHECHOUTDATE) I wanted to show.
Beside those two combos, I also made a commandbutton (cmdshow) as a trigger for the action and a mshflexgrid to show the data. So, I put the code in cmdshow.

Private Sub cmdshow_Click()
    Dim conec As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "\DATABASE.mdb'"
    conec.CursorLocation = adUseClient
    conec.Open
 
    If (Combo1.Text = "") and (Combo2.Text = "") Then
        MsgBox "Please fill the criteria", vbExclamation
        Combo1.SetFocus
        Else
            rs.CursorLocation = adUseClient
            rs.Open "SELECT * FROM RESERV WHERE CHECKINDATE>=#" & CDate(Combo1.Text) & "# AND CHECKOUTDATE<=#" & CDate(Combo2.Text) & "# '", conec, adOpenDynamic, adLockOptimistic
            Set MSHFlexGrid1.DataSource = rs
    End If
    rs.Close
End Sub

It's better if you could find the DT Picker. If you could, the code above will be:

"SELECT * FROM RESERV WHERE CHECKINDATE>=#" & DTPicker1.Value & "# AND CHECKOUTDATE<=#" & DTPicker2.Value & "# ", conec, adOpenDynamic, adLockOptimistic

You can do a right click on DTPicker to show its properties and change the dateformat to suit your database's dateformat

OR you can use the code sent by hariharanmca (thank you):

"SELECT * FROM GUESTLIST WHERE Date>= ' " & Format(DTPicker1.Value,"MM/dd/yyyy") & " ' AND Date<= ' " & Format(DTPicker1.Value,"MM/dd/yyyy")  & " ' "

To hariharanmca: How to show DT Picker when you don't have "Microsoft Windows Common Control 2.6.0" in your Project Components? Steve don't have it. I have no idea about this, because I've already had it when I installed my VB6.

Thank you n Good luck!

Nairda

Hi Nairda, I got a question to ask you. Do you this error??

The table ‘ReservationTab le’ is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmaticall y.

[Steven TK]

Not at all, Steve. This what the forum is for.
Ok, I'll give you a picture:

I have a hotel reservation Ms. Access table (RESERV.mdb). It contains some columns (CHECKINDATE, CHECKOUTDATE, ROOM_NO, GUESTNAME, etc).
And then I have this report form which I use for setting some criterias to show the report. In this case I want to show list of all guest stayed in my hotel between CHECKINDATE in combo1 and CHECKOUTDATE combo2.
So, I have two combo boxes. Combo1 is to set the beginning of the report (CHECKINDATE) i wanted to show, combo2 is for the ending of the report (CHECHOUTDATE) I wanted to show.
Beside those two combos, I also made a commandbutton (cmdshow) as a trigger for the action and a mshflexgrid to show the data. So, I put the code in cmdshow.

Private Sub cmdshow_Click()
    Dim conec As New ADODB.Connection
    Dim rs As New ADODB.Recordset
    conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "\DATABASE.mdb'"
    conec.CursorLocation = adUseClient
    conec.Open
 
    If (Combo1.Text = "") and (Combo2.Text = "") Then
        MsgBox "Please fill the criteria", vbExclamation
        Combo1.SetFocus
        Else
            rs.CursorLocation = adUseClient
            rs.Open "SELECT * FROM RESERV WHERE CHECKINDATE>=#" & CDate(Combo1.Text) & "# AND CHECKOUTDATE<=#" & CDate(Combo2.Text) & "# '", conec, adOpenDynamic, adLockOptimistic
            Set MSHFlexGrid1.DataSource = rs
    End If
    rs.Close
End Sub

It's better if you could find the DT Picker. If you could, the code above will be:

"SELECT * FROM RESERV WHERE CHECKINDATE>=#" & DTPicker1.Value & "# AND CHECKOUTDATE<=#" & DTPicker2.Value & "# ", conec, adOpenDynamic, adLockOptimistic

You can do a right click on DTPicker to show its properties and change the dateformat to suit your database's dateformat

OR you can use the code sent by hariharanmca (thank you):

"SELECT * FROM GUESTLIST WHERE Date>= ' " & Format(DTPicker1.Value,"MM/dd/yyyy") & " ' AND Date<= ' " & Format(DTPicker1.Value,"MM/dd/yyyy")  & " ' "

To hariharanmca: How to show DT Picker when you don't have "Microsoft Windows Common Control 2.6.0" in your Project Components? Steve don't have it. I have no idea about this, because I've already had it when I installed my VB6.

Thank you n Good luck!

Nairda

Hi Nairda, I got a question to ask you. Do you this error??

The table ‘ReservationTab le’ is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmaticall y. When i press the reserve button, it should go to the reservation form but it show me this error

[nairda]

Hi Nairda, I got a question to ask you. Do you this error??

The table ‘ReservationTab le’ is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmaticall y. When i press the reserve button, it should go to the reservation form but it show me this error

Hold on, I got confused here.
What's your programs's story exactly? You want to show a report from your guestdatabase OR you want to make a reservation form to fill by guests?

Nairda

[Steven TK]

Hold on, I got confused here.
What's your programs's story exactly? You want to show a report from your guestdatabase OR you want to make a reservation form to fill by guests?

Nairda

Nairda,
I want to make a reservation form to fill by guests and i have a search engine form . The user just go to the search engine then after looking the tool they want then they click the reserve button in search engine form to reserve the tool they want

[nairda]

Nairda,
I want to make a reservation form to fill by guests and i have a search engine form . The user just go to the search engine then after looking the tool they want then they click the reserve button in search engine form to reserve the tool they want

Geez, We've headed to a wrong way then. Sorry.
If it's an input form, why bother filtering your startdate and enddate? Just give the guests some comboboxes to fill the date and month they wanted, and then save it to your reservation database after they clicked a command button.

Nairda

[Steven TK]

Geez, We've headed to a wrong way then. Sorry.
If it's an input form, why bother filtering your startdate and enddate? Just give the guests some comboboxes to fill the date and month they wanted, and then save it to your reservation database after they clicked a command button.

Nairda

Hi Nairda,

Do you know how to filter the date in between the date range?

Eg. Start Date = 7/20/2007 and End Date = 7/22/2007

Then the user select Start Date = 7/20/2007 and End Date = 7/21/2007 , it can show this date Start Date = 7/20/2007 and End Date = 7/22/2007 also.

[nairda]

Hi Nairda,

Do you know how to filter the date in between the date range?

Eg. Start Date = 7/20/2007 and End Date = 7/22/2007

Then the user select Start Date = 7/20/2007 and End Date = 7/21/2007 , it can show this date Start Date = 7/20/2007 and End Date = 7/22/2007 also.

Hi,
I think You can't do that. Because no matter how your database date length, the user only selected date 20 and 21.
Assume that today's date is 7/21/2007.
If user picked 2 dates (1= 7?20/2007, 2= 7/21/2007), You can only show date 20 and 21. Because the 22 is not yet happened.
The point is, if you have a criteria to filter your data, you can't show others datas outside the criteria.

To filter the date between, you can still use the codes I wrote before.

Nairda

[Steven TK]

Hi,
I think You can't do that. Because no matter how your database date length, the user only selected date 20 and 21.
Assume that today's date is 7/21/2007.
If user picked 2 dates (1= 7?20/2007, 2= 7/21/2007), You can only show date 20 and 21. Because the 22 is not yet happened.
The point is, if you have a criteria to filter your data, you can't show others datas outside the criteria.

To filter the date between, you can still use the codes I wrote before.

Nairda

Hi Nairda, I figure the previous problem. Thank anyway.

Do you know how to close the table by writing codes?

By the way, I am wondering how to show in the calendar that can classified which equipment is book or not?