By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,787 Members | 2,251 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,787 IT Pros & Developers. It's quick & easy.

Reservation System Using Microsoft Access

P: 14
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(comboBox as 16/7/07), the Start Date(textbox should show only 16/7/07) and this also apply to the EndDate.

Private Sub cboStartDate_Click()

'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.cboStartDate) 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_Click()

'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.cboEndDate) 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
Jun 14 '07 #1
Share this Question
Share on Google+
23 Replies


P: 39
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(comboBox as 16/7/07), the Start Date(textbox should show only 16/7/07) and this also apply to the EndDate.

Private Sub cboStartDate_Click()

'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.cboStartDate) 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_Click()

'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.cboEndDate) 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

Hi, Steven

I'm not sure about what you want to make here, but I think you want to filter datas in your database using START DATE criteria nad END DATE criteria.
If this's right, I hope these codes can give you a hint about how to filter your data based on a period of time:

1. Instead of using comboboxes as your criteria input, it's easier to use DateTime Picker. (Project > Components > Microsoft Windows Common Control-2 6)

2. Use DT Picker to replace your combobox.

3. Use this SQL code:
Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM GUESTLIST WHERE Date>=#" & DTPicker1.Value & "# AND Date<=#" & DTPicker2.Value & "#"
4.You can change >= to just > and <= to < if you do not want to include the dates

FYI, I have a quite same problem a few days ago and have been solved by the forum. You can check the tread with header: "show report sort by period of time" on this forum.

Nairda
Jun 14 '07 #2

P: 14
Hi, Steven

I'm not sure about what you want to make here, but I think you want to filter datas in your database using START DATE criteria nad END DATE criteria.
If this's right, I hope these codes can give you a hint about how to filter your data based on a period of time:

1. Instead of using comboboxes as your criteria input, it's easier to use DateTime Picker. (Project > Components > Microsoft Windows Common Control-2 6)

2. Use DT Picker to replace your combobox.

3. Use this SQL code:
Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM GUESTLIST WHERE Date>=#" & DTPicker1.Value & "# AND Date<=#" & DTPicker2.Value & "#"
4.You can change >= to just > and <= to < if you do not want to include the dates

FYI, I have a quite same problem a few days ago and have been solved by the forum. You can check the tread with header: "show report sort by period of time" on this forum.

Nairda
Hi Nairda,

Where do you get the Date Time Picker Control in microsoft access
Jun 14 '07 #3

P: 39
Hi Nairda,

Where do you get the Date Time Picker Control in microsoft access
Hi,
Not in Ms.Access. Date Time Picker is a VB Component.
You can find it in Menu Project > Components > give a check sign on Microsoft Windows Common Controls-2.6.0. And then you can put DT Picker from your toolbox to your VB form.
You can use it to subtitute your combobox if you wanted to make a date list.
Give it a try.

Nairda
Jun 14 '07 #4

P: 14
Hi,
Not in Ms.Access. Date Time Picker is a VB Component.
You can find it in Menu Project > Components > give a check sign on Microsoft Windows Common Controls-2.6.0. And then you can put DT Picker from your toolbox to your VB form.
You can use it to subtitute your combobox if you wanted to make a date list.
Give it a try.

Nairda
Hi Nairda, i try go to the component in the microsoft visual basic. I find only the microsoft window common control version 6 (SP 6) but i can't get the date picker as u say after i check the microsoft window common control version 6 (SP 6).
Jun 14 '07 #5

P: 39
Hi Nairda, i try go to the component in the microsoft visual basic. I find only the microsoft window common control version 6 (SP 6) but i can't get the date picker as u say after i check the microsoft window common control version 6 (SP 6).

That's right, you can't find DT Picker if you selected Windows Common Control 6. You have to choose Windows Common Control 2.6.0 (SP4). I think the problem is we have a different version of VB or its Service Pack.
FYI, I used Visual Basic 6.0

In case you can't find DT Picker, you can still use your comboboxes, asl long as you use the same date format between your combo boxes and your database.
So if you use "mm dd yyyy" date format in your database, you have to add the same data format in your combo boxes.
The SQL code is the same (You just need to change DTPicker1.value with combo1.text)
The difference is you have to input many date in your combobox list meanwhile DT Picker has already give a "calendar looks" for you. You just pick the date you wanted.

Nairda
Jun 14 '07 #6

P: 14
That's right, you can't find DT Picker if you selected Windows Common Control 6. You have to choose Windows Common Control 2.6.0 (SP4). I think the problem is we have a different version of VB or its Service Pack.
FYI, I used Visual Basic 6.0

In case you can't find DT Picker, you can still use your comboboxes, asl long as you use the same date format between your combo boxes and your database.
So if you use "mm dd yyyy" date format in your database, you have to add the same data format in your combo boxes.
The SQL code is the same (You just need to change DTPicker1.value with combo1.text)
The difference is you have to input many date in your combobox list meanwhile DT Picker has already give a "calendar looks" for you. You just pick the date you wanted.

Nairda
Hi Nairda,

I have a calendar control, 2 comboBox Date (1 is for the Start date and the other one is the End date). I want the user to choose according to their start date and end date by click the date they want in the calendar. Then the textbox will filter the date according to their needs
Jun 14 '07 #7

P: 39
Hi Nairda,

I have a calendar control, 2 comboBox Date (1 is for the Start date and the other one is the End date). I want the user to choose according to their start date and end date by click the date they want in the calendar. Then the textbox will filter the date according to their needs

OK, I think you can do as I write before. Just be careful with your date format.

Nairda
Jun 14 '07 #8

hariharanmca
100+
P: 1,977
Hi Nairda,

I have a calendar control, 2 comboBox Date (1 is for the Start date and the other one is the End date). I want the user to choose according to their start date and end date by click the date they want in the calendar. Then the textbox will filter the date according to their needs

This is for MS Access :

Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM GUESTLIST WHERE Date>=#" & Format(DTPicker1.Value,"MM/dd/yyyy") & "# AND Date<=#" & Format(DTPicker1.Value,"MM/dd/yyyy")  & "#"
This is for SQL Server :

Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM GUESTLIST WHERE Date>= ' " & Format(DTPicker1.Value,"MM/dd/yyyy") & " ' AND Date<= ' " & Format(DTPicker1.Value,"MM/dd/yyyy")  & " ' "
Make sure that both date format should be Month/Day/Year format

other wise it will select the corresponding date format date only
Jun 14 '07 #9

P: 14
This is for MS Access :

Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM GUESTLIST WHERE Date>=#" & Format(DTPicker1.Value,"MM/dd/yyyy") & "# AND Date<=#" & Format(DTPicker1.Value,"MM/dd/yyyy")  & "#"
This is for SQL Server :

Expand|Select|Wrap|Line Numbers
  1. "SELECT * FROM GUESTLIST WHERE Date>= ' " & Format(DTPicker1.Value,"MM/dd/yyyy") & " ' AND Date<= ' " & Format(DTPicker1.Value,"MM/dd/yyyy")  & " ' "
Make sure that both date format should be Month/Day/Year format

other wise it will select the corresponding date format date only
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.
Jun 14 '07 #10

P: 14
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.
Jun 15 '07 #11

P: 39
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdshow_Click()
  2.     Dim conec As New ADODB.Connection
  3.     Dim rs As New ADODB.Recordset
  4.     conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "\DATABASE.mdb'"
  5.     conec.CursorLocation = adUseClient
  6.     conec.Open
  7.  
  8.     If (Combo1.Text = "") and (Combo2.Text = "") Then
  9.         MsgBox "Please fill the criteria", vbExclamation
  10.         Combo1.SetFocus
  11.         Else
  12.             rs.CursorLocation = adUseClient
  13.             rs.Open "SELECT * FROM RESERV WHERE CHECKINDATE>=#" & CDate(Combo1.Text) & "# AND CHECKOUTDATE<=#" & CDate(Combo2.Text) & "# '", conec, adOpenDynamic, adLockOptimistic
  14.             Set MSHFlexGrid1.DataSource = rs
  15.     End If
  16.     rs.Close
  17. End Sub
It's better if you could find the DT Picker. If you could, the code above will be:
Expand|Select|Wrap|Line Numbers
  1. "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):

Expand|Select|Wrap|Line Numbers
  1. "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
Jun 15 '07 #12

P: 14
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdshow_Click()
  2.     Dim conec As New ADODB.Connection
  3.     Dim rs As New ADODB.Recordset
  4.     conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "\DATABASE.mdb'"
  5.     conec.CursorLocation = adUseClient
  6.     conec.Open
  7.  
  8.     If (Combo1.Text = "") and (Combo2.Text = "") Then
  9.         MsgBox "Please fill the criteria", vbExclamation
  10.         Combo1.SetFocus
  11.         Else
  12.             rs.CursorLocation = adUseClient
  13.             rs.Open "SELECT * FROM RESERV WHERE CHECKINDATE>=#" & CDate(Combo1.Text) & "# AND CHECKOUTDATE<=#" & CDate(Combo2.Text) & "# '", conec, adOpenDynamic, adLockOptimistic
  14.             Set MSHFlexGrid1.DataSource = rs
  15.     End If
  16.     rs.Close
  17. End Sub
It's better if you could find the DT Picker. If you could, the code above will be:
Expand|Select|Wrap|Line Numbers
  1. "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):

Expand|Select|Wrap|Line Numbers
  1. "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 ‘ReservationTable’ is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically.
Jun 15 '07 #13

P: 14
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.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdshow_Click()
  2.     Dim conec As New ADODB.Connection
  3.     Dim rs As New ADODB.Recordset
  4.     conec.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source ='" & App.Path & "\DATABASE.mdb'"
  5.     conec.CursorLocation = adUseClient
  6.     conec.Open
  7.  
  8.     If (Combo1.Text = "") and (Combo2.Text = "") Then
  9.         MsgBox "Please fill the criteria", vbExclamation
  10.         Combo1.SetFocus
  11.         Else
  12.             rs.CursorLocation = adUseClient
  13.             rs.Open "SELECT * FROM RESERV WHERE CHECKINDATE>=#" & CDate(Combo1.Text) & "# AND CHECKOUTDATE<=#" & CDate(Combo2.Text) & "# '", conec, adOpenDynamic, adLockOptimistic
  14.             Set MSHFlexGrid1.DataSource = rs
  15.     End If
  16.     rs.Close
  17. End Sub
It's better if you could find the DT Picker. If you could, the code above will be:
Expand|Select|Wrap|Line Numbers
  1. "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):

Expand|Select|Wrap|Line Numbers
  1. "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 ‘ReservationTable’ is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically. When i press the reserve button, it should go to the reservation form but it show me this error
Jun 15 '07 #14

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

The table ‘ReservationTable’ is already opened exclusively by another user, or it is already open through the user interface and cannot be manipulated programmatically. 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
Jun 15 '07 #15

P: 14
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
Jun 15 '07 #16

P: 39
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
Jun 15 '07 #17

P: 14
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.
Jun 15 '07 #18

P: 39
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
Jun 15 '07 #19

P: 14
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?
Jun 20 '07 #20

P: 39
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
Jun 20 '07 #21

P: 14
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?
Jun 21 '07 #22

P: 14
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.
Jun 29 '07 #23

P: 14
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.OpenRecordset("ReservationTable")
With rst
.AddNew
!EmployeeID = Me.cboEmployeeName
!ToolID = Me.cboToolName
!StartDate = Me.txtFromDate
!EndDate = Me.txtToDate
!Status = Me.txtStatus
!UserName = Me.txtApplicationUserName
!CPUName = Me.txtCPUName
!DateRecord = Now()
.Update
End With
rst.Close

But I don't know how to stop this error.. Need help..
Jul 10 '07 #24

Post your reply

Sign in to post your reply or Sign up for a free account.