Query from unbound form

[Guest]

I have an unbound form [FormA] which lists 12 different criteria in unbound
combo boxes. The CFR Table Query obtains parameters from FormA using the
following expression in the criteria section of the query for each of the 12
combo boxes:

Like "*" & [Forms]![FormA]![Review Name] & "*"
(where Review name is one of the 12 combo boxes)

I run a report where the CFR Table Query is the record source. I get the
expected results the first time a run the report, but if I change any of the
criteria on FormA, the query does not change. It continues to use the output
from the original query. If I close out the database and restart the
process, I can change the criteria, but in only works with one combination.

Any suggestions on what is causing this problem. Thanks.

 

[John Vinson]

I get the
expected results the first time a run the report, but if I change any of the
criteria on FormA, the query does not change. It continues to use the output
from the original query.

Close and reopen the Report. It's convenient to use a command button
on the unbound form to do so.


John W. Vinson[MVP]

 

[Guest]

When I do this, and select different critieria, the report produces the same
result. It looks like the query does change based upon the selection change
in the unbound form combo boxes. If I close down the entire database and
restart with new criteria, the report changes, but only for the initial query.

 

[John Vinson]

When I do this, and select different critieria, the report produces the same
result. It looks like the query does change based upon the selection change
in the unbound form combo boxes. If I close down the entire database and
restart with new criteria, the report changes, but only for the initial query.

Please post the SQL of your query, the Recordsource property of the
report, and the code you're using to open the report.

Note that you do not need to - and indeed should not - "run" the
Query. It should simply be the Recordsource of the report.

John W. Vinson[MVP]

 

[Guest]

SQL for CFR Table Query:

SELECT [CFR Table].Reviewer, [CFR Table].[Review Name], [CFR Table].[Review
Year], [CFR Table].CSC,
[CFR Table].[Claim Number], [CFR Table].[Date of Loss], [CFR Table].[Report
Date], [CFR Table].Suffix,
[CFR Table].[SX Stat Date], [CFR Table].[SX Ent Date], [CFR Table].[Loss
Type], [CFR Table].[Claim
Specialist Code], [CFR Table].[Loss Payments], [CFR Table].[LAE Payments],
[CFR Table].[Total Payments],
[CFR Table].[Accident State], [CFR Table].[Insured State], [CFR
Table].Policy, [CFR Table].[Line of
Business], [CFR Table].Suit, [CFR Table].Subro, [CFR Table].Insured, [CFR
Table].Company, [CFR
Table].Agent, [CFR Table].[Profit Center], [CFR Table].[Accident Year],
[CFR Table].[Claim State Date],
[CFR Table].Supervisor, [CFR Table].[Leakage Year], [CFR Table].[Date of
Review],
FROM [CFR Table]
WHERE ((([CFR Table].Reviewer) Is Not Null) AND (([CFR Table].[Review
Name]) Like "*" &
[Forms]![FormA]![Review Name] & "*") AND (([CFR Table].[Review Year]) Like
"*" &
[Forms]![FormA]![Review Year] & "*") AND (([CFR Table].CSC) Like "*" &
[Forms]![FormA]![CSC] & "*") AND
(([CFR Table].[Loss Type]) Like "*" & [Forms]![FormA]![Reserve Type] & "*"));

Recordsource Property for Report: CFR Table Query

The report is opened using a hyperlink on the unbound form.

Hope this helps.