Report not being found

[Guest]

I am using Office 2003 on Windows XP.

I have a report designed and I want to update the recordSource with a new
date. However, when I use a code as in the following, an error says the
report is missing or misspelled, etc.:

Reports("rptHoldChecks").RecordSource = "SELECT ..."

This report exists and is spelled right. What am I doing wrong?

Thanks for your help.

 

[Duane Hookom]

You can only change most report properties if the report is open. If your
report is not open, you will get an error message.

If we understood what you were attempting to do, someone might provide a
great solution.

 

[Guest]

Thanks for posting Duane,

As I said before, I simply want to change a date in the where clause of the
RecordSource property, then run the report using the new date. I am doing
this by replacing the entire SQL string.

How can I do this? If the report is open, then it will not reflect the date
I need to run the report for?

 

[Duane Hookom]

I would remove the criteria from the report's record source. You can then
use code to open the report with a Where Condition in the DoCmd.OpenReport
method. You can also replace the "hard-coded" date with a reference to a
control on a form.

 

[Guest]

Hi Duane,

I think your first suggestion is what I want. So, my SQL string would be
partially entered in the recordSource? I'm not sure what that should look
like. As in the following?:

SELECT f1, f2, f3 FROM t1

Then put a where in the OpenReport?

DoCmd.OpenReport "rptHoldChecks", acViewPreview, , "Where rptDate =
#10-17-2006#"

Does that look right?

 

[Duane Hookom]

close. I would use this:
DoCmd.OpenReport "rptHoldChecks", acViewPreview, , "rptDate =
#10-17-2006#"

Better yet, allow users to enter a date in a text box on the form:
Dim strWhere as String
strWhere = "1=1 "
If Not IsNull(Me.txtRptDate) Then
strWhere = strWhere & " AND rptDate=#" & _
Me.txtRptDate & "# "
End If
DoCmd.OpenReport "rptHoldChecks", acViewPreview, , strWhere