Can't quite link a form to a report

[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.

----------------------------------------------------------------------------
----------

Problem: I am getting "#Name?" on the report where the filtered data should
be.

Background: I have 3 tables; tblArticles (Article_ID is the PK),
tblKeywords (Keyword_ID is the PK), and tblJoin (Table_ID is the PK and has
the other PK's as FK's). First 2 tables joined to tblJoin with One-To-Many
relationships.

Created a form using combo boxes looking at the first 2 tables for data.
The user can select any of 4 choices from the 4 combo boxes, press a command
button ("Apply Filter") and SHOULD get a report pop up that has the filtered
results. What I get is the column headers and "#Name?" on a single row for
the results.

The Record Source for the report is blank. When I created the text boxes
the Control Source listed the 3 tables. So I clicked on the "..." and used
Expression Builder to do the following (as an example):
=tblArticles!ArticleDate .

Since this doesn't work, I'm sure I need to re-work my behind-the-scenes
part of the report.

I think I need to define the Form Record Source with a SQL statement and
also do SQL statements for the text box Control Sources, because I am basing
this off of multiple tables vice the single table in the sample DB.

I request your advice and comments on how I should proceed.

TIA,
Mike Webb
Platte River Whooping Crane Maintenance Trust, Inc.
a non-profit 501(c)(3) organization

 

[Blaire Comrie]

Try using the Where parameter of the DoCmd.OpenReport Function

Dim strCriteria as String
Dim stDocName as String

strCriteria = "ThisID=" & Me.Combo1.Column(0) & " And ThatID=" &
Me.Combo2.Column(0) & " And TheOtherID=" & Me.Combo3.Column(0)
stDocName = "rptMyReport"
DoCmd.OpenReport stDocName, acPreview, , strCriteria

this way you don't have to enter any criteria into the Query your report is
based on.

Hope this helps.
Blaire
http://www.codewidgets.com

 

[Mike Webb]

Okay, I cut and pasted the code below and then changed a few names to what I
have, saved it and then tried to run it from the form -- got an error:
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 (with your code inserted)
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?

Mike