If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
|
#1
|
|||
|
|||
Dates in a listbox connected to a form...
Good morning,
Here is something I'm tryng to do, but can't seem to get a handle on. I am hoping for some direction. I have the following things listed in a listbox: today, this week, this month, last week, last month, custom I want to be able to click on THISWEEK and have all the records for this week to show up on my form. It is a projects table where our employees log in the hours they have spent on a project. I need them to be able to see the data by the choices above. ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some sort of boxes appear where I could type in the 2 dates and everything between those dates would show up on the form. I've tried queries, but I'm just not getting it. I understand the logic behind it, but just not how to get it done. I'm a novice Access user, so please be gentle in your help and explainations !! Thank you so much. LisaB. |
#2
|
|||
|
|||
Lisa,
To begin with, I would add two textboxes on the form (called, say, txtDateFm and txtDateTo, formatted as date - same format as the date field in the table), and set their Visible property to No in form design. These will be used for holding the from and to dates, populated by the listbox (while still invisible) on every other choice, and made visible to be filled manually when custom is selected. The query for the records would reference these two text boxes in its criterion on the date field, like: = Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo] I would use the double click event of the listbox (assumed name List0) to calculate the dates and requery the subform (assumed name: Subform1) with the records. Of course, when custom is selected, the user has to enter the dates and then somehow requery the subform. For this I would use a command button (called Command2 in my sample code, caption something like Refresh or Get Data, also hidden in the form design) which is toggled between visible and invisible together with the textboxes. Here's the code behind the Command2 button: Private Sub Command2_Click() Me.Subform1.Requery End Sub And, finally, here's the code behind the listbox's double click event: Private Sub List0_DblClick(Cancel As Integer) Select Case Me.List0 Case "Today" Me.txtDateFm = Date Me.txtDateTo = Date Me.Subform1.Requery Case "This Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) Me.txtDateTo = dt - Weekday(dt, 3) + 6 Me.Subform1.Requery Case "This Month" Select Case Month(Date) Case 12 y1 = Year(Date) m1 = Month(Date) y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Last Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) - 7 Me.txtDateTo = dt - Weekday(dt, 3) - 1 Me.Subform1.Requery Case "Last Month" Select Case Month(Date) Case 1 y1 = Year(Date) - 1 m1 = 12 y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) - 1 y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Custom" Me.txtDateFm = Null Me.txtDateTo = Null Me.txtDateFm.Visible = True Me.txtDateTo.Visible = True Me.Command2.Visible = True GoTo skip_hide End Select Me.txtDateFm.Visible = False Me.txtDateTo.Visible = False Me.Command2.Visible = False skip_hide: End Sub Don't forget to change the object names to your real names! HTH, Nikos "RusCat" wrote in message ... Good morning, Here is something I'm tryng to do, but can't seem to get a handle on. I am hoping for some direction. I have the following things listed in a listbox: today, this week, this month, last week, last month, custom I want to be able to click on THISWEEK and have all the records for this week to show up on my form. It is a projects table where our employees log in the hours they have spent on a project. I need them to be able to see the data by the choices above. ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some sort of boxes appear where I could type in the 2 dates and everything between those dates would show up on the form. I've tried queries, but I'm just not getting it. I understand the logic behind it, but just not how to get it done. I'm a novice Access user, so please be gentle in your help and explainations !! Thank you so much. LisaB. |
#3
|
|||
|
|||
Nikos,
I am completely flabbergasted at the detail of your reply. Thank you so much for taking the time and effort to help me. I appreciate it very much. To be honest, I don't understand all of it, but I'm going to begin trying to dissect it this evening and see where I get. I hope you will continue to be available for additional questions, which I will undoubtedly have !! Thank you again. :-) LisaB. "Nikos Yannacopoulos" wrote: Lisa, To begin with, I would add two textboxes on the form (called, say, txtDateFm and txtDateTo, formatted as date - same format as the date field in the table), and set their Visible property to No in form design. These will be used for holding the from and to dates, populated by the listbox (while still invisible) on every other choice, and made visible to be filled manually when custom is selected. The query for the records would reference these two text boxes in its criterion on the date field, like: = Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo] I would use the double click event of the listbox (assumed name List0) to calculate the dates and requery the subform (assumed name: Subform1) with the records. Of course, when custom is selected, the user has to enter the dates and then somehow requery the subform. For this I would use a command button (called Command2 in my sample code, caption something like Refresh or Get Data, also hidden in the form design) which is toggled between visible and invisible together with the textboxes. Here's the code behind the Command2 button: Private Sub Command2_Click() Me.Subform1.Requery End Sub And, finally, here's the code behind the listbox's double click event: Private Sub List0_DblClick(Cancel As Integer) Select Case Me.List0 Case "Today" Me.txtDateFm = Date Me.txtDateTo = Date Me.Subform1.Requery Case "This Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) Me.txtDateTo = dt - Weekday(dt, 3) + 6 Me.Subform1.Requery Case "This Month" Select Case Month(Date) Case 12 y1 = Year(Date) m1 = Month(Date) y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Last Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) - 7 Me.txtDateTo = dt - Weekday(dt, 3) - 1 Me.Subform1.Requery Case "Last Month" Select Case Month(Date) Case 1 y1 = Year(Date) - 1 m1 = 12 y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) - 1 y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Custom" Me.txtDateFm = Null Me.txtDateTo = Null Me.txtDateFm.Visible = True Me.txtDateTo.Visible = True Me.Command2.Visible = True GoTo skip_hide End Select Me.txtDateFm.Visible = False Me.txtDateTo.Visible = False Me.Command2.Visible = False skip_hide: End Sub Don't forget to change the object names to your real names! HTH, Nikos "RusCat" wrote in message ... Good morning, Here is something I'm tryng to do, but can't seem to get a handle on. I am hoping for some direction. I have the following things listed in a listbox: today, this week, this month, last week, last month, custom I want to be able to click on THISWEEK and have all the records for this week to show up on my form. It is a projects table where our employees log in the hours they have spent on a project. I need them to be able to see the data by the choices above. ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some sort of boxes appear where I could type in the 2 dates and everything between those dates would show up on the form. I've tried queries, but I'm just not getting it. I understand the logic behind it, but just not how to get it done. I'm a novice Access user, so please be gentle in your help and explainations !! Thank you so much. LisaB. |
#4
|
|||
|
|||
Good evening Nikos,
I just want you to know that my jaw is laying on the floor !! Your help and code did EXACTLY what I was wanting to do. Thank you thank you thank you. I didn't understand most of it, but I spent part of yesterday and most of today figuring it out and it works GREAT !!! It was the query that gave me the most trouble. But then again, I usually have the most trouble with queries. I appreciate you and the many others that frequent this board and help those of us who are lost. I live in rural Maine with no opportunity to take a class in Access, so this board has been a wonderful learning experience. I'm going to see if I can incorporate a drop down calendar on the Custom date from and to. I've researched it here and know that it can be done. Any additional help you can offer will be most appreciated with regards to the calendar. Thank you again for sharing your skills and knowledge. It really means something to me. :-) LisaB "RusCat" wrote: Nikos, I am completely flabbergasted at the detail of your reply. Thank you so much for taking the time and effort to help me. I appreciate it very much. To be honest, I don't understand all of it, but I'm going to begin trying to dissect it this evening and see where I get. I hope you will continue to be available for additional questions, which I will undoubtedly have !! Thank you again. :-) LisaB. "Nikos Yannacopoulos" wrote: Lisa, To begin with, I would add two textboxes on the form (called, say, txtDateFm and txtDateTo, formatted as date - same format as the date field in the table), and set their Visible property to No in form design. These will be used for holding the from and to dates, populated by the listbox (while still invisible) on every other choice, and made visible to be filled manually when custom is selected. The query for the records would reference these two text boxes in its criterion on the date field, like: = Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo] I would use the double click event of the listbox (assumed name List0) to calculate the dates and requery the subform (assumed name: Subform1) with the records. Of course, when custom is selected, the user has to enter the dates and then somehow requery the subform. For this I would use a command button (called Command2 in my sample code, caption something like Refresh or Get Data, also hidden in the form design) which is toggled between visible and invisible together with the textboxes. Here's the code behind the Command2 button: Private Sub Command2_Click() Me.Subform1.Requery End Sub And, finally, here's the code behind the listbox's double click event: Private Sub List0_DblClick(Cancel As Integer) Select Case Me.List0 Case "Today" Me.txtDateFm = Date Me.txtDateTo = Date Me.Subform1.Requery Case "This Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) Me.txtDateTo = dt - Weekday(dt, 3) + 6 Me.Subform1.Requery Case "This Month" Select Case Month(Date) Case 12 y1 = Year(Date) m1 = Month(Date) y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Last Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) - 7 Me.txtDateTo = dt - Weekday(dt, 3) - 1 Me.Subform1.Requery Case "Last Month" Select Case Month(Date) Case 1 y1 = Year(Date) - 1 m1 = 12 y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) - 1 y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Custom" Me.txtDateFm = Null Me.txtDateTo = Null Me.txtDateFm.Visible = True Me.txtDateTo.Visible = True Me.Command2.Visible = True GoTo skip_hide End Select Me.txtDateFm.Visible = False Me.txtDateTo.Visible = False Me.Command2.Visible = False skip_hide: End Sub Don't forget to change the object names to your real names! HTH, Nikos "RusCat" wrote in message ... Good morning, Here is something I'm tryng to do, but can't seem to get a handle on. I am hoping for some direction. I have the following things listed in a listbox: today, this week, this month, last week, last month, custom I want to be able to click on THISWEEK and have all the records for this week to show up on my form. It is a projects table where our employees log in the hours they have spent on a project. I need them to be able to see the data by the choices above. ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some sort of boxes appear where I could type in the 2 dates and everything between those dates would show up on the form. I've tried queries, but I'm just not getting it. I understand the logic behind it, but just not how to get it done. I'm a novice Access user, so please be gentle in your help and explainations !! Thank you so much. LisaB. |
#5
|
|||
|
|||
Lisa,
Glad I could help. Not a big believer in classes, I'm self-taught in most everything I do with a computer (and when I got my first PC back in '87 the internet was practically unheard of!). Anyway... On the drop-down calendar: there are two approaches: 1. You can use the Date and Time Picker active X control instead of textboxes. You can set their value in code just like the textboxes, but you can't set them to Null for Custom; they have to have a value, so you would set them to, say, current date instead, as a basis for the user to start from. Also, because the DTPicker has a time part as well, even though you don't see it, you would have to use an Int() function on the control references in the query criteria, so as to get "clean"dates, without the hour part (date/time in Access is actually a number, 0 being Dec.31, 1899, the integer part being the date, and the decimal part being the time). 2. You can leave the textboxes as they are and use their double-click event to pop-up a separate form with a Calendar active X control on it, to select a date and return it to the textbox on the main form, while the date can still be typed in if desired. Also, the calendar control has no time part, so no bneed to worry about that, and you can still Null the textboxes for Custom. Incidentally, I answered a post on thi this very subject yesterday, I have the "how-to" ready! Here it goes: The calendar control is on a separate form, opened by the double-click (or click?) event of the text boxes. I'll assume the following names in my example, and you'll have to change to the real ones: Form with calendar: frmCalendar Calendar control: ActiveXCtl1 The code behind the double-click event of the text boxes must be something like: Private Sub txtDateFm_DblClick(Cancel As Integer) ctrl = Me.Name & "*" & Me.ActiveControl.Name DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl Forms!frmCalendar!ActiveXCtl1 = Date End Sub (same for txtDateTo) This way, you are passing the form and control names (separated by a *) as an opening argument to frmCalendar. The code behind the click event of the calendar should be something like: Private Sub ActiveXCtl1_Click() sep = InStr(1, OpenArgs, "*") frm = Left(OpenArgs, sep - 1) ctl = Right(OpenArgs, Len(OpenArgs) - sep) Forms(frm).Controls(ctl) = Me.ActiveXCtl1 End Sub This will work with different forms as well! If you go for option 2, play around with the properties on Format tab for the form to get rid of record selector, navigation buttons, control box etc wich are meaningless in this case. In either case, to put an ActiveX control on a form, go Insert ActiveX control. Good luck! Nikos "RusCat" wrote in message ... Good evening Nikos, I just want you to know that my jaw is laying on the floor !! Your help and code did EXACTLY what I was wanting to do. Thank you thank you thank you. I didn't understand most of it, but I spent part of yesterday and most of today figuring it out and it works GREAT !!! It was the query that gave me the most trouble. But then again, I usually have the most trouble with queries. I appreciate you and the many others that frequent this board and help those of us who are lost. I live in rural Maine with no opportunity to take a class in Access, so this board has been a wonderful learning experience. I'm going to see if I can incorporate a drop down calendar on the Custom date from and to. I've researched it here and know that it can be done. Any additional help you can offer will be most appreciated with regards to the calendar. Thank you again for sharing your skills and knowledge. It really means something to me. :-) LisaB "RusCat" wrote: Nikos, I am completely flabbergasted at the detail of your reply. Thank you so much for taking the time and effort to help me. I appreciate it very much. To be honest, I don't understand all of it, but I'm going to begin trying to dissect it this evening and see where I get. I hope you will continue to be available for additional questions, which I will undoubtedly have !! Thank you again. :-) LisaB. "Nikos Yannacopoulos" wrote: Lisa, To begin with, I would add two textboxes on the form (called, say, txtDateFm and txtDateTo, formatted as date - same format as the date field in the table), and set their Visible property to No in form design. These will be used for holding the from and to dates, populated by the listbox (while still invisible) on every other choice, and made visible to be filled manually when custom is selected. The query for the records would reference these two text boxes in its criterion on the date field, like: = Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo] I would use the double click event of the listbox (assumed name List0) to calculate the dates and requery the subform (assumed name: Subform1) with the records. Of course, when custom is selected, the user has to enter the dates and then somehow requery the subform. For this I would use a command button (called Command2 in my sample code, caption something like Refresh or Get Data, also hidden in the form design) which is toggled between visible and invisible together with the textboxes. Here's the code behind the Command2 button: Private Sub Command2_Click() Me.Subform1.Requery End Sub And, finally, here's the code behind the listbox's double click event: Private Sub List0_DblClick(Cancel As Integer) Select Case Me.List0 Case "Today" Me.txtDateFm = Date Me.txtDateTo = Date Me.Subform1.Requery Case "This Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) Me.txtDateTo = dt - Weekday(dt, 3) + 6 Me.Subform1.Requery Case "This Month" Select Case Month(Date) Case 12 y1 = Year(Date) m1 = Month(Date) y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Last Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) - 7 Me.txtDateTo = dt - Weekday(dt, 3) - 1 Me.Subform1.Requery Case "Last Month" Select Case Month(Date) Case 1 y1 = Year(Date) - 1 m1 = 12 y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) - 1 y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Custom" Me.txtDateFm = Null Me.txtDateTo = Null Me.txtDateFm.Visible = True Me.txtDateTo.Visible = True Me.Command2.Visible = True GoTo skip_hide End Select Me.txtDateFm.Visible = False Me.txtDateTo.Visible = False Me.Command2.Visible = False skip_hide: End Sub Don't forget to change the object names to your real names! HTH, Nikos "RusCat" wrote in message ... Good morning, Here is something I'm tryng to do, but can't seem to get a handle on. I am hoping for some direction. I have the following things listed in a listbox: today, this week, this month, last week, last month, custom I want to be able to click on THISWEEK and have all the records for this week to show up on my form. It is a projects table where our employees log in the hours they have spent on a project. I need them to be able to see the data by the choices above. ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some sort of boxes appear where I could type in the 2 dates and everything between those dates would show up on the form. I've tried queries, but I'm just not getting it. I understand the logic behind it, but just not how to get it done. I'm a novice Access user, so please be gentle in your help and explainations !! Thank you so much. LisaB. |
#6
|
|||
|
|||
Wow Nikos !
I used to be self taught, years and years ago. I used to program in dbase and in Clipper. Before the windows environment was around. I did some great stuff and really enjoyed the 'puzzle' aspect of programming. Working to figure out how to make something work. I just taught myself by trial and error. THEN.... I had kids and my brain turned to mush!! Things have changed ALOT in the last 10 or 12 years. I'm enjoying the challenge again now that my youngest is in school all day. This is helping me SO much. Not only is it solving a problem but it is teaching me SO much. Your directions are clear and easy to follow. What you explained is working well. I finally got everything in the right place and it is doing what I wanted it to do !! Mostly. I have some questions about the actual operations: 1. Does it matter whether the code is behind the Click or DoubleClick? One click seems to work best for me, but if that is not the correct way to set things up, then I'll stay with DClick. 2. Can the event for the Listbox also be just Click? 3. I did go with option 2 and played around with the format stuff - very cool. Can I tell it somehow exactly where I want the calendar to appear? 4. When the calendar does appear, I click on a date (which appears in the textbox), but the calendar does not disappear. How do I make it do that? 5. Can I put the exact same code in the OnEnter event for the Listbox? That way you can select the item by clicking or entering? 6. I've done something wrong with the textboxes.... Click the first box, calendar appears, click date, date goes to first textbox. Click second box, calendar appears, click date, date ALSO goes to the first textbox. I checked the code and I have the right names. Where might I be going wrong? Well, 6 problems/questions is enough for right now. thanks again for the help. :-) LisaB. "Nikos Yannacopoulos" wrote: Lisa, Glad I could help. Not a big believer in classes, I'm self-taught in most everything I do with a computer (and when I got my first PC back in '87 the internet was practically unheard of!). Anyway... On the drop-down calendar: there are two approaches: 1. You can use the Date and Time Picker active X control instead of textboxes. You can set their value in code just like the textboxes, but you can't set them to Null for Custom; they have to have a value, so you would set them to, say, current date instead, as a basis for the user to start from. Also, because the DTPicker has a time part as well, even though you don't see it, you would have to use an Int() function on the control references in the query criteria, so as to get "clean"dates, without the hour part (date/time in Access is actually a number, 0 being Dec.31, 1899, the integer part being the date, and the decimal part being the time). 2. You can leave the textboxes as they are and use their double-click event to pop-up a separate form with a Calendar active X control on it, to select a date and return it to the textbox on the main form, while the date can still be typed in if desired. Also, the calendar control has no time part, so no bneed to worry about that, and you can still Null the textboxes for Custom. Incidentally, I answered a post on thi this very subject yesterday, I have the "how-to" ready! Here it goes: The calendar control is on a separate form, opened by the double-click (or click?) event of the text boxes. I'll assume the following names in my example, and you'll have to change to the real ones: Form with calendar: frmCalendar Calendar control: ActiveXCtl1 The code behind the double-click event of the text boxes must be something like: Private Sub txtDateFm_DblClick(Cancel As Integer) ctrl = Me.Name & "*" & Me.ActiveControl.Name DoCmd.OpenForm "frmCalendar", , , stLinkCriteria, , , ctrl Forms!frmCalendar!ActiveXCtl1 = Date End Sub (same for txtDateTo) This way, you are passing the form and control names (separated by a *) as an opening argument to frmCalendar. The code behind the click event of the calendar should be something like: Private Sub ActiveXCtl1_Click() sep = InStr(1, OpenArgs, "*") frm = Left(OpenArgs, sep - 1) ctl = Right(OpenArgs, Len(OpenArgs) - sep) Forms(frm).Controls(ctl) = Me.ActiveXCtl1 End Sub This will work with different forms as well! If you go for option 2, play around with the properties on Format tab for the form to get rid of record selector, navigation buttons, control box etc wich are meaningless in this case. In either case, to put an ActiveX control on a form, go Insert ActiveX control. Good luck! Nikos "RusCat" wrote in message ... Good evening Nikos, I just want you to know that my jaw is laying on the floor !! Your help and code did EXACTLY what I was wanting to do. Thank you thank you thank you. I didn't understand most of it, but I spent part of yesterday and most of today figuring it out and it works GREAT !!! It was the query that gave me the most trouble. But then again, I usually have the most trouble with queries. I appreciate you and the many others that frequent this board and help those of us who are lost. I live in rural Maine with no opportunity to take a class in Access, so this board has been a wonderful learning experience. I'm going to see if I can incorporate a drop down calendar on the Custom date from and to. I've researched it here and know that it can be done. Any additional help you can offer will be most appreciated with regards to the calendar. Thank you again for sharing your skills and knowledge. It really means something to me. :-) LisaB "RusCat" wrote: Nikos, I am completely flabbergasted at the detail of your reply. Thank you so much for taking the time and effort to help me. I appreciate it very much. To be honest, I don't understand all of it, but I'm going to begin trying to dissect it this evening and see where I get. I hope you will continue to be available for additional questions, which I will undoubtedly have !! Thank you again. :-) LisaB. "Nikos Yannacopoulos" wrote: Lisa, To begin with, I would add two textboxes on the form (called, say, txtDateFm and txtDateTo, formatted as date - same format as the date field in the table), and set their Visible property to No in form design. These will be used for holding the from and to dates, populated by the listbox (while still invisible) on every other choice, and made visible to be filled manually when custom is selected. The query for the records would reference these two text boxes in its criterion on the date field, like: = Forms![Form Name]![txtDateFm] and = Forms![Form Name]![txtDateTo] I would use the double click event of the listbox (assumed name List0) to calculate the dates and requery the subform (assumed name: Subform1) with the records. Of course, when custom is selected, the user has to enter the dates and then somehow requery the subform. For this I would use a command button (called Command2 in my sample code, caption something like Refresh or Get Data, also hidden in the form design) which is toggled between visible and invisible together with the textboxes. Here's the code behind the Command2 button: Private Sub Command2_Click() Me.Subform1.Requery End Sub And, finally, here's the code behind the listbox's double click event: Private Sub List0_DblClick(Cancel As Integer) Select Case Me.List0 Case "Today" Me.txtDateFm = Date Me.txtDateTo = Date Me.Subform1.Requery Case "This Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) Me.txtDateTo = dt - Weekday(dt, 3) + 6 Me.Subform1.Requery Case "This Month" Select Case Month(Date) Case 12 y1 = Year(Date) m1 = Month(Date) y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Last Week" dt = Date If Weekday(dt, 3) = 7 Then dt = dt + 7 Me.txtDateFm = dt - Weekday(dt, 3) - 7 Me.txtDateTo = dt - Weekday(dt, 3) - 1 Me.Subform1.Requery Case "Last Month" Select Case Month(Date) Case 1 y1 = Year(Date) - 1 m1 = 12 y2 = y1 + 1 m2 = 1 Case Else y1 = Year(Date) m1 = Month(Date) - 1 y2 = y1 m2 = m1 + 1 End Select Me.txtDateFm = DateSerial(y1, m1, 1) Me.txtDateTo = DateSerial(y2, m2, 1) - 1 Me.Subform1.Requery Case "Custom" Me.txtDateFm = Null Me.txtDateTo = Null Me.txtDateFm.Visible = True Me.txtDateTo.Visible = True Me.Command2.Visible = True GoTo skip_hide End Select Me.txtDateFm.Visible = False Me.txtDateTo.Visible = False Me.Command2.Visible = False skip_hide: End Sub Don't forget to change the object names to your real names! HTH, Nikos "RusCat" wrote in message ... Good morning, Here is something I'm tryng to do, but can't seem to get a handle on. I am hoping for some direction. I have the following things listed in a listbox: today, this week, this month, last week, last month, custom I want to be able to click on THISWEEK and have all the records for this week to show up on my form. It is a projects table where our employees log in the hours they have spent on a project. I need them to be able to see the data by the choices above. ALSO: how would I create a CUSTOM set of data? Click on CUSTOM, some sort of boxes appear where I could type in the 2 dates and everything between those dates would show up on the form. I've tried queries, but I'm just not getting it. I understand the logic behind it, but just not how to get it done. I'm a novice Access user, so please be gentle in your help and explainations !! Thank you so much. LisaB. |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
Cursor Positioning | Colin Hammond | General Discussion | 3 | November 2nd, 2004 08:42 PM |
Can you move highlighted data from one listbox to another listbox on the same form? | Col. Forbin | General Discussion | 2 | September 24th, 2004 07:51 PM |
Searching Records | Melissa Lambino | New Users | 6 | July 20th, 2004 04:51 AM |
Passing value to modal child form in MS Access using VBA? | Grahammer | Using Forms | 6 | July 4th, 2004 11:53 PM |