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:

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 Sub

Open in new window




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,
hugonietoAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Gustav BrockCIOCommented:
Setting

  Me!OutDoor_Activities.Form.FilterOn = False

should remove the filter.
Or click the filter button at the bottom of the datasheet.

/gustav
0
hugonietoAuthor Commented:
Gustav,


This line Me!OutDoor_Activities.Form.FilterOn = False worked perfectly to clear out the results of the search!!!!! Thanks a lot!!


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,
0
Gustav BrockCIOCommented:
Yes, use OpenReport with a where statement:

DoCmd.OpenReport "rptYourReportName", , , Me!OutDoor_Activities.Form.Filter

/gustav
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Newly released Acronis True Image 2019

In announcing the release of the 15th Anniversary Edition of Acronis True Image 2019, the company revealed that its artificial intelligence-based anti-ransomware technology – stopped more than 200,000 ransomware attacks on 150,000 customers last year.

ggzfabCommented:
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.
0
hugonietoAuthor Commented:
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.

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 Sub

Open in new window



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!!
0
Gustav BrockCIOCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.