Form filter to report - error

[Josh]

I have created a database to track our companies
projects. On the Project Form (frmProjects), which takes
data from tblProjects, there is a command button
(printCurrentLookup) that opens a report with the same
filter. The report (rptProjectList) has the same Source
sql statement. In addition, it also has the exact same
text boxes and combo boxes (even though some of them are
invisible), to ensure that any filter from the form can
be transferred over.

The code to open the report is simply:
--------------------
Dim stDocName As String
Dim stFilter As String

stDocName = "rptProjectList"
stFilter = Me.Filter

DoCmd.OpenReport stDocName, acPreview, , stFilter
---------------------

This code works when filtering the majority of the
fields, but here is my problem:

Some of combo boxes allow the user to select text, while
storing a primary key (As in columncount = 2, boundcolumn
= 1). For instance, the "State" combo box is populated
from the tblListStates table (StateID = autonumber, State
= text), if a user selects "NY", the State Variable in
the projects table will store 19, which is the
corresponding primary key.

When I filter by form and select "NY", the filter
property becomes

((Lookup_State.State="NY"))

which I assume is looking up the corresponding StateID
for "NY".

When I click the printCurrentLookup command button, it
gives me an error

Run-time error '3080':
Joined Table 'rptProjectList' not listed in FROM clause.

Help!!! Is there anyway to transfer a filter of this kind
to the report without getting this error?

Thanks for bearing with me on this long question.

Cheers, Josh

 

[Allen Browne]

In order for the filter to work in the report, you need to have the tables
in its RecordSource.

Assuming you have a query for your report, could you include the "state"
table? Alias it as Lookup_State if necessary, and use an outer join if this
is not a required field.