how to filter a datasheet subform, print the results, and refresh or reload subform Access 2010
Hi guys,
I'm working with a form in Access 2010 that lets the user enter data into the following fields:
Date classroom #of children #of Techers
The user will be entering this data everyday. I already have the form built as a datasheet view form. Also, I have a main window where I have a few tabs having subforms. The above datasheet form is inside of one of these tabs.
Now, I would like to have two text boxes and a search button on the main window for the user to enter a Begin date and a end date so the user can look a date range and filter the datasheet subform.
I tried making these boxes and so far I'm able to filter the datasheet form.... I created a search button with the following code on it:
The line where the BETWEEN code is filters the datasheet form and the lines with .Value codes clears up the text boxes when the user hits the search button. So far at this point the search is doing what I need. However, When I get the date range and I have the datasheet form filtered I can not have this datasheet form reloaded or refreshed. I have to close the form totally and reopen it for me to see everything again and be able to do another date range search.
I would like to create a button so the user can hit it and reload the datasheet subform after having the results from the date range search. This way the user can create another date range search all over again. I just don't know how to code it!! Could you give me a hand with this?
Also, I would like to create a button that when it is hit it will create a report with the results I get from a date range search. For example, Begin date 3/4/2011, end date 6/5/2011 and I get the results in the datasheet subform..... then having the results I would hit the create report button and it will create a report out of the results.
Do you think this might be possible? How would I code it?
Any help will be very appreciate!!
Thanks,
I'm working with a form in Access 2010 that lets the user enter data into the following fields:
Date classroom #of children #of Techers
The user will be entering this data everyday. I already have the form built as a datasheet view form. Also, I have a main window where I have a few tabs having subforms. The above datasheet form is inside of one of these tabs.
Now, I would like to have two text boxes and a search button on the main window for the user to enter a Begin date and a end date so the user can look a date range and filter the datasheet subform.
I tried making these boxes and so far I'm able to filter the datasheet form.... I created a search button with the following code on it:
Private Sub RefreshButton_Click()
Me.OutDoor_Activities.Requery
End Sub
Private Sub Search_Date_Click()
With Me.OutDoor_Activities.Form
.Filter = "[Date] BETWEEN #" & Me.BeginDate & "# AND #" & Me.EndDate & "#"
.FilterOn = True
End With
Me.BeginDate.Value = ""
Me.EndDate.Value = ""
End SubThe line where the BETWEEN code is filters the datasheet form and the lines with .Value codes clears up the text boxes when the user hits the search button. So far at this point the search is doing what I need. However, When I get the date range and I have the datasheet form filtered I can not have this datasheet form reloaded or refreshed. I have to close the form totally and reopen it for me to see everything again and be able to do another date range search.
I would like to create a button so the user can hit it and reload the datasheet subform after having the results from the date range search. This way the user can create another date range search all over again. I just don't know how to code it!! Could you give me a hand with this?
Also, I would like to create a button that when it is hit it will create a report with the results I get from a date range search. For example, Begin date 3/4/2011, end date 6/5/2011 and I get the results in the datasheet subform..... then having the results I would hit the create report button and it will create a report out of the results.
Do you think this might be possible? How would I code it?
Any help will be very appreciate!!
Thanks,
ASKER
Gustav,
This line Me!OutDoor_Activities.Form
Do you have any idea on how to create a report out of the results of a Date range search in the datasheet subform with VBA?
Thanks,
This line Me!OutDoor_Activities.Form
Do you have any idea on how to create a report out of the results of a Date range search in the datasheet subform with VBA?
Thanks,
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Small additional note:
To make clear to the user what filter has been used, I would add the Form.Filter also in the OpenArgs parameter of the OpenReport (the last one) and show that in the report header.
To make clear to the user what filter has been used, I would add the Form.Filter also in the OpenArgs parameter of the OpenReport (the last one) and show that in the report header.
ASKER
Thanks guys!! it is working good. However, the refresh button I created works fine in the form but not in the navigation form. This is how I did the forms.
1. I created a blank form called subformOUTDOOR with a header having the two text boxes to search for dates, the search button, a print results button, and a refresh button. Then, I inserted the datasheet form as a subform in this blank form. So, this whole form with header and subform is working fine.......... the search boxes, the search button, the print results button, and the refresh button are doing what I want them to do.
2. I created a navigation form to place the subformOUTDOOR on it. Looks good with a tab named OUTDOOR which will open the subformOUTDOOR form. I open this navigation form and I start entering data into the subformOUTDOOR. Also, I search for dates and it gives me good results,... I print them out and it works fine.... At this point everything works perfect! BUT when I hit the refresh button to get rid of the filter results from my search it only works part of it..... it does clear the results but It doesn't take me to add a new record.
I coded this button with your suggested options and I also add a line to it so when it clears up the search results it takes me to the last part of the datasheet form or takes me to directly to add a new record.
Here is the code I put in the refresh button.
When I open just the subformOUTDOOR form the line of code I mentioned above is executed perfect!! but it doesn't do it when I have the navigation form open!! Do you have any idea why in the navigation form the refresh button doesn't work??
Thanks guys!!
1. I created a blank form called subformOUTDOOR with a header having the two text boxes to search for dates, the search button, a print results button, and a refresh button. Then, I inserted the datasheet form as a subform in this blank form. So, this whole form with header and subform is working fine.......... the search boxes, the search button, the print results button, and the refresh button are doing what I want them to do.
2. I created a navigation form to place the subformOUTDOOR on it. Looks good with a tab named OUTDOOR which will open the subformOUTDOOR form. I open this navigation form and I start entering data into the subformOUTDOOR. Also, I search for dates and it gives me good results,... I print them out and it works fine.... At this point everything works perfect! BUT when I hit the refresh button to get rid of the filter results from my search it only works part of it..... it does clear the results but It doesn't take me to add a new record.
I coded this button with your suggested options and I also add a line to it so when it clears up the search results it takes me to the last part of the datasheet form or takes me to directly to add a new record.
Here is the code I put in the refresh button.
Private Sub RefreshButton_Click()
Me!OutDoor_Activities.Form.FilterOn = False
Me!OutDoor_Activities.SetFocus
DoCmd.GoToRecord , , acNewRec <---- ACCESS DOESN'T EXECUTE THIS LINE IN THE
NAVIGATION FORM
End SubWhen I open just the subformOUTDOOR form the line of code I mentioned above is executed perfect!! but it doesn't do it when I have the navigation form open!! Do you have any idea why in the navigation form the refresh button doesn't work??
Thanks guys!!
DoCmd.GoToRecord works on the currently selected form, so I guess it would also work from the navigation if you first select the outdoor form:
DoCmd.SelectObject ...
/gustav
DoCmd.SelectObject ...
/gustav
Me!OutDoor_Activities.Form
should remove the filter.
Or click the filter button at the bottom of the datasheet.
/gustav