REPOST - Can't quite link a report to a form

[Mike Webb]

Using Access 2K2; experience level: Novice.

Using sample DB RptSmp97.mdb, based on Microsoft KB article Q145591, How
to Filter a Report Using a Form's Filter

Copied then converted the DB to Access 2K2 format before I started.
===============================================================

I am getting 'Run-time error '2451': "The report name 'rptArticles' you
entered is misspelled or refers to a
report that isn't open or doesn't exist."

The code for the form, which refers to the report is pasted below:

Option Compare Database
Option Explicit
Private Sub cmdApplyFilter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag & "] "
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 4))
'Set the Filter property
Reports![rptArticles].Filter = strSQL
Reports![rptArticles].FilterOn = True
End If


End Sub
Private Sub cmdClear_Click()

Dim intCounter As Integer

For intCounter = 1 To 4
Me("Filter" & intCounter) = ""
Next


End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptArticles"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim strCriteria As String
Dim stDocName As String

strCriteria = "tblArticles.ArticleDate=" & Me.Filter1.Column(0) & "
And tblArticles.ArticleName=" & Me.Filter2.Column(0) & " And
tblArticles.ArticleSource=" & Me.Filter3.Column(0) & " And
tblKeywords.Keyword=" & Me.Filter4.Column(0)
stDocName = "rptArticles"
DoCmd.OpenReport stDocName, acPreview, , strCriteria
End Sub


The highlighted line is Reports![rptArticles].Filter = strSQL and is in the
first Private Sub. Can someone tell me what I need to change?

TIA,
Mike

 

[Graham Mandeno]

Hi Mike

You will get that error message if the report is not open. It seems from
your code that you are opening the report when the form opens (in your
Form_Open procedure you have DoCmd.OpenReport ...). However, either it is
not opening correctly at that point, or it has been closed again by the time
you click your "Apply Filter" command button.

 

[Mike Webb]

Thanks for the comments. I'll go back in and try a few things to see if I
can change the code for the better.

Mike

Hi Mike

You will get that error message if the report is not open. It seems from
your code that you are opening the report when the form opens (in your
Form_Open procedure you have DoCmd.OpenReport ...). However, either it is
not opening correctly at that point, or it has been closed again by the time
you click your "Apply Filter" command button.

--
Good Luck!

Graham Mandeno [Access MVP]
Auckland, New Zealand

Using Access 2K2; experience level: Novice.

Using sample DB RptSmp97.mdb, based on Microsoft KB article Q145591, How
to Filter a Report Using a Form's Filter

Copied then converted the DB to Access 2K2 format before I started.
===============================================================

I am getting 'Run-time error '2451': "The report name 'rptArticles' you
entered is misspelled or refers to a
report that isn't open or doesn't exist."

The code for the form, which refers to the report is pasted below:

Option Compare Database
Option Explicit
Private Sub cmdApplyFilter_Click()

Dim strSQL As String, intCounter As Integer
'Build SQL String
For intCounter = 1 To 4
If Me("Filter" & intCounter) <> "" Then
strSQL = strSQL & "[" & Me("Filter" & intCounter).Tag &

"]

"
& " = " & Chr(34) & Me("Filter" & intCounter) & Chr(34) & " And "
End If
Next

If strSQL <> "" Then
'Strip Last " And "
strSQL = left(strSQL, (Len(strSQL) - 4))
'Set the Filter property
Reports![rptArticles].Filter = strSQL
Reports![rptArticles].FilterOn = True
End If


End Sub
Private Sub cmdClear_Click()

Dim intCounter As Integer

For intCounter = 1 To 4
Me("Filter" & intCounter) = ""
Next


End Sub

Private Sub cmdClose_Click()
DoCmd.Close acForm, Me.Form.Name
End Sub

Private Sub Form_Close()
DoCmd.Close acReport, "rptArticles"
DoCmd.Restore
End Sub

Private Sub Form_Open(Cancel As Integer)
Dim strCriteria As String
Dim stDocName As String

strCriteria = "tblArticles.ArticleDate=" & Me.Filter1.Column(0)

&

"
And tblArticles.ArticleName=" & Me.Filter2.Column(0) & " And
tblArticles.ArticleSource=" & Me.Filter3.Column(0) & " And
tblKeywords.Keyword=" & Me.Filter4.Column(0)
stDocName = "rptArticles"
DoCmd.OpenReport stDocName, acPreview, , strCriteria
End Sub


The highlighted line is Reports![rptArticles].Filter = strSQL and is in the
first Private Sub. Can someone tell me what I need to change?

TIA,
Mike