QBF

[Larry]

I have the following criteria in the recordsource of a report:

SELECT [Power 1].[Country Name], [Power 1].[Province Name], [Power 1].[Site
Name], [Power 1].Type, [Power 1].Lat, [Power 1].Long, [Power 1].CL, [Power
1].Source, [Power 1].Comments, [Power 1].CreateDate FROM [Power 1] WHERE
((([Power 1].[Country Name])=Forms![DDBox Country Select]!CtyName)) ORDER BY
[Power 1].[Country Name], [Power 1].[Province Name], [Power 1].[Site Name];

The form referenced above in the SQL statement has one combo box named
"CtyName" which has the following row source:

SELECT [Countries and Provinces].[Country Name] FROM [Countries and
Provinces] GROUP BY [Countries and Provinces].[Country Name] ORDER BY
[Countries and Provinces].[Country Name];

The problem is that when the report is run, the form does not open, I only
get a parameter dialog box. What am I doing wrong?

Larry

 

[Wayne Morgan]

If you want to form to open in order to provide the parameter value, you
have to open the form. In the Open event of the report you need to use
DoCmd.OpenForm with the acDialog window mode argument to open the form and
pause the code until you make your selection. Once the selection has been
made, you need to close or hide the form for the code to continue and allow
the report to open. Since the form has to be open for the report to read
from it, hiding the form is what you would do in this case (i.e. set it's
Visible property to False). Close the form in the report's Close event.

 

[Larry]

I was under the impression that using Forms! in the criteria line of the
query would automatically open the form and that reference to the control
would retrieve the parameter. If this had been a query ratther than a report,
there would be no code page to do what your saying.

If you want to form to open in order to provide the parameter value, you
have to open the form. In the Open event of the report you need to use
DoCmd.OpenForm with the acDialog window mode argument to open the form and
pause the code until you make your selection. Once the selection has been
made, you need to close or hide the form for the code to continue and allow
the report to open. Since the form has to be open for the report to read
from it, hiding the form is what you would do in this case (i.e. set it's
Visible property to False). Close the form in the report's Close event.

--
Wayne Morgan
MS Access MVP

I have the following criteria in the recordsource of a report:

SELECT [Power 1].[Country Name], [Power 1].[Province Name], [Power
1].[Site
Name], [Power 1].Type, [Power 1].Lat, [Power 1].Long, [Power 1].CL, [Power
1].Source, [Power 1].Comments, [Power 1].CreateDate FROM [Power 1] WHERE
((([Power 1].[Country Name])=Forms![DDBox Country Select]!CtyName)) ORDER
BY
[Power 1].[Country Name], [Power 1].[Province Name], [Power 1].[Site
Name];

The form referenced above in the SQL statement has one combo box named
"CtyName" which has the following row source:

SELECT [Countries and Provinces].[Country Name] FROM [Countries and
Provinces] GROUP BY [Countries and Provinces].[Country Name] ORDER BY
[Countries and Provinces].[Country Name];

The problem is that when the report is run, the form does not open, I only
get a parameter dialog box. What am I doing wrong?

Larry

 

[Wayne Morgan]

No, that won't automatically open a form in a query either. If you are going
to manually use the query and you want the parameter value to come from a
form, then you'll have to manually open the form first because, you're
right, there is no code option behind a query. For the value of a form to be
seen, the form has to be open. Since you can basically "free type" a
parameter in the query to say just about any thing you want it to say as a
prompt, when the query doesn't see the form, because the form isn't open,
the query assumes that what you entered is a prompt that it should display.