Remembering report dates

  • Thread starter Thread starter Ian
  • Start date Start date
I

Ian

I have asome reports that print data between two dates. I ask the user
for the dates with a form.

What is the recommended way of remembering/storing these dates between
uses of the report? i.e. when the user changes the date in one of the
form fields it is stored against the relevant report so the next time the
report is requested the last dates used are the form defaults.

The major twist here is that this form is used for multiple reports.

I have an idea it will need to use the Me.Application.CurrentObjectName
to work out which report to store against and I will need a reports
table.

I suppose I wondering if this is common enough task that it's already
been done before?

Thanks.
 
Presumably your form has a command button that uses OpenReport with a
WhereCondition to specify the limiting dates?

If so, you could use the Click event of the command button to write the
dates into a table with fields such as:
ReportName Text
StartDate Date/Time
End Date Date/Time

Example code:

Dim rs As DAO.Recordset
Dim strSQL As String
Dim strDoc As String

strDoc = "NameOfYourReportHere"
strSQL = "SELECT * FROM tblReportCriteria WHERE ReportName = """ & strDoc &
""";"
Set rs = dbEngine(0)(0).OpenRecordset(strSQL)
With rs
If .RecordCount = 0 Then
.AddNew 'Create a new entry for this report.
Else
.Edit 'Modify the existing entry for this report.
End If
!StartDate = ...
!EndDate = ...
.Update
End With
rs.Close
Set rs = Nothing


Next time the form is opened (or when the user changes the report?) you can
read the values from the report's entry, and assign them to the controls.
 
Actually, if you create the table as Allen suggests, you could just bind
your form to the table. As you navigate through the records in the table,
the last dates and report names should appear.
 
Actually, if you create the table as Allen suggests, you could just bind
your form to the table. As you navigate through the records in the table,
the last dates and report names should appear.
Click to expand...

I thought of that but there are no nav buttons on the form and it's used by
several different reports. If I try that the question then becomes how do
I get the form to choose the correct record in the table it's bound to for
the report to come?

If this was easy then it would be by far the nicest solution.
 
Presumably your form has a command button that uses OpenReport with a
WhereCondition to specify the limiting dates?
Click to expand...

Well there is a query the report is bound to and the dates are used in
that where clause, so yes. The report itself uses a DoCmd to open the
form when it opens.
If so, you could use the Click event of the command button to write
the dates into a table with fields such as:
ReportName Text
StartDate Date/Time
End Date Date/Time
Click to expand...

Yes the form has an button that I could do this on I would think.
Example code:

Dim rs As DAO.Recordset
Dim strSQL As String
Dim strDoc As String

strDoc = "NameOfYourReportHere"
strSQL = "SELECT * FROM tblReportCriteria WHERE ReportName = """ &
strDoc & """;"
Set rs = dbEngine(0)(0).OpenRecordset(strSQL)
With rs
If .RecordCount = 0 Then
.AddNew 'Create a new entry for this report.
Else
.Edit 'Modify the existing entry for this report.
End If
!StartDate = ...
!EndDate = ...
.Update
End With
rs.Close
Set rs = Nothing
Click to expand...

I think this is what I need, thanks Allen.
 
You can use the combo box wizard that finds a record in the current form.
The same can be done with a listbox.
 
You can use the combo box wizard that finds a record in the current form.
The same can be done with a listbox.
Click to expand...

Sorry Duane but I am not sure I follow you?

The form that has the date selection fields is opened by a vb call in the
Report_Open method.

So not entirely sure where your help would fit in. It's early and I am most
likely being dense :)
 
This shouldn't make any difference. When the form opens, you can use the
find record combo box to move to record or possibly open the form to a
particular record.
 
This shouldn't make any difference. When the form opens, you can use the
find record combo box to move to record or possibly open the form to a
particular record.
Click to expand...

Gotcha. Thanks. Told you I was being dense :)
 
Back
Top