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

[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?

Great then!

What table do you want to close? Your database table or datagrid table?

Sorry Steve, I don't know how to show booked equpment in the calendar. Perhaps you could post a new thread regarding this problem.

Thank you.
Nairda

[Steven TK]

Great then!

What table do you want to close? Your database table or datagrid table?

Sorry Steve, I don't know how to show booked equpment in the calendar. Perhaps you could post a new thread regarding this problem.

Thank you.
Nairda

Hi Nairda,

Do you know the search criteria database provided by allen brown? Can you eliminate the last line of the textbox because there is not information record there?

[Steven TK]

Hi Nairda,

Do you know the search criteria database provided by allen brown? Can you eliminate the last line of the textbox because there is not information record there?

Hi Nairda, Do you how to transfer the data from 1 comboBox in the form to another comboBox in another form?

Another Question,

I got several comboBox list for the user to select but i want to save the record after the user fulfill some criteria. It is just like I don't want the comboBox list to be bound first but after the user has fulfill the criteria , they just simply press the save record button and save the record into the table.

[Steven TK]

Hi everyone,

Q1.
My Msgbox show this
"Please confirm the record again"
EmployeeName : 3 (still got the ID Number not the Name)
ToolName : 3 (still got the ID Number not the Name)
StartDate : 7/6/2007
EndDate : 7/6/2007

Can I not show the ID Number but instead the TOOL NAME and EMPLOYEE NAME?

Q2.

For Eg1, the user select Digital Multimeter, Start Date is 17/7/07 and End Date is 19/7/07, However, this record is already been recorded in the table, it should not be allowing to record this data. It works well on Eg1 by preventing the dulipcate record. However, it prompt the run time error. For Eg2, the user select Digital Multimeter, Start Date is 18/7/07 and End Date is 18/7/07, this record also must not be save in the data. It does not work as the criteria stated in Eg2 and the date is in between the 17 July and 19 July.


I started create the brand new table with the ToolID , StartDate and EndDate as a primary key. It indeed does not save the record , however, it still prompt me the run-time error 3022 "The changes you requested to the table were not successful because they would duplicate value in index, primary key or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entry and try again".

I suspect the error is the coding

Set db = CurrentDb
Set rst = db.OpenRecordse t("ReservationT able")
With rst
.AddNew
!EmployeeID = Me.cboEmployeeN ame
!ToolID = Me.cboToolName
!StartDate = Me.txtFromDate
!EndDate = Me.txtToDate
!Status = Me.txtStatus
!UserName = Me.txtApplicati onUserName
!CPUName = Me.txtCPUName
!DateRecord = Now()
.Update
End With
rst.Close

But I don't know how to stop this error.. Need help..