Report refresh

[Lifeseeker]

Hi,

I have built a form-based search. On the form, users specify effective dates. Two buttons, "Report" and "Refresh", which refreshes the form only.

What's bothering users is that...every time they put in a new date and generate report, they have to hit the "Refresh All" button at the top of the access ribbon in "Records" group to see the new result.

I thought that if you just hit "Report" on the form, the report itself will automatically refresh....but this is not happening.

Is there a way around this?

comment/help much appreciated.

thank you

 

[Jeanette Cunningham]

Post the code behind the Report and Refresh buttons so we can make suggestions.

 

[Lifeseeker]

Post the code behind the Report and Refresh buttons so we can make suggestions.

This is the code behind the refresh button on the search form.

Private Sub Refresh_Click()
Me.Text0 = ""
Me.Text2 = ""
End Sub

where text0 is the 1st date field and text2 is the 2nd date field.
--------------------------------------------
This is the code behind the report.

Private Sub Report_Load()
Me.Filter = ""
End Sub

I set the filter to Null when the report loads because there seemed to be a filter by default, which was why I was getting 0 record after changing the date parameter, but anyway, those are the codes.

Any thought/comment much appreciated.

 

[Jeanette Cunningham]

I suggest that the Report button has code like this

Private Sub ReportBtnName_Click()
Dim strWHERE As String

Dim lngLen As Long
'Date field example. Use the format string to add the # delimiters and get the right international format.
If Not IsNull(Me.txtStartDate) Then
strWHERE = strWHERE & "([DateFieldName] >= " & SQLDate(Me.txtStartDate) & ") AND "
End If

' Use "less than the next day" since this field has times as well as dates.
If Not IsNull(Me.txtFilterEndDate) Then 'Less than the next day.
strWHERE = strWHERE & "([ DateFieldName] < " & SQLDate(Me.txtEndDate + 1) & ") AND "
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) - 5
If lngLen > 0 Then
strWHERE = Left$(strWHERE, lngLen)
End If
'Debug.Print "strWhere: ", strwhere
DoCmd.OpenReport "[ReportName]", AcViewPreview, , strWhere

End Sub


Function SQLDate(varDate As Variant) As String
'Purpose: Return a delimited string in the date format used natively by JET SQL.
'Argument: A date/time value.
'Note: Returns just the date format if the argument has no time component,
' or a date/time format if it does.
'Author: Allen Browne. al...@allenbrowne.com, June 2006.
On Error GoTo Err_Handler
'Last mod date 13/03/2011

If IsDate(varDate) Then
If DateValue(varDate) = varDate Then
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy\#")
Else
SQLDate = Format$(varDate, "\#mm\/dd\/yyyy hh\:nn\:ss\#")
End If
End If
End Function

For the refresh button, requery the form.

Private Sub Refresh_Click()
Me.Text0 = ""
Me.Text2 = ""
Me.Requery
End Sub

Note:
Remove the code you have in the report's load event.
I have used txtStartDate and txtEndDate - you can use Text0 and Text2 to suit your form.