parameter report

[Educo Gent]

I have a report in Access2002. The source of the report is a query on a SQL
server2000.

By opening the report, I want that there appears a dialog box, so the user
can type some text (for example: a title for the report). The typed text has
to appear somewhere in the report.

By using access mdb-files, I make a text box in the report, with a parameter
as source (for example: = [Type a title:]. By using sql-server2000, I can't
do that.

Has anyone a solution?

Thanks,
Hugo L.

 

[Educo Gent]

I have tried the following (without success):
- I make a public parameter (e.g. m_title)
- I use a VBA-procedure in Access with following code:
m_title = inputbox("Type tile")
- I make a text box in the report with source: m_title. Result: error
'invalid column name'.

Question: How can I use a parameter as source of a textbox in a report,
while the source of the report is a query or table of a sqlserver?

 

[J. Clay]

Use a form for entry of the required information. On the form have a button
to open the report. Within the report you can design it so that either
fields or input parameters reference the main form. Some thing like this
(This is psuedo code!): =Forms("ReportEntryForm")("FieldName")

I have done this on several occassions.

Jim

 

[Educo Gent]

Hello,

I've tried and it is OK.

thanks !!

Use a form for entry of the required information. On the form have a
button to open the report. Within the report you can design it so that
either fields or input parameters reference the main form. Some thing
like this (This is psuedo code!): =Forms("ReportEntryForm")("FieldName")

I have done this on several occassions.

Jim

I have a report in Access2002. The source of the report is a query on a
SQL server2000.

By opening the report, I want that there appears a dialog box, so the
user can type some text (for example: a title for the report). The typed
text has to appear somewhere in the report.

By using access mdb-files, I make a text box in the report, with a
parameter as source (for example: = [Type a title:]. By using
sql-server2000, I can't do that.

Has anyone a solution?

Thanks,
Hugo L.

 

[Vadim Rapp]

Hello,
You wrote on Fri, 02 Dec 2005 13:50:15 GMT:

EG> I have a report in Access2002. The source of the report is a query on a
EG> SQL server2000.

EG> By opening the report, I want that there appears a dialog box, so the
EG> user can type some text (for example: a title for the report). The
typed text
EG> has to appear somewhere in the report.

EG> By using access mdb-files, I make a text box in the report, with a
EG> parameter as source (for example: = [Type a title:]. By using
sql-server2000, I can't
EG> do that.

EG> Has anyone a solution?

Declare public variable in the form's module; in form_open event , variable
= inputbox() ; on the form, specify controlsource as =variable .

Regards

 

[Vadim Rapp]

....even easier, specify control source as =inputbox("enter value")

 

[Hugo Lefebvre]

Thanks !! For the moment I can't test what you propose, because, for the
moment, I can't connect to a sql-server. I have tested with a mdb-file and
it seems to work, what you propose.

I've tried also with a public variable, but I've declared the variable, and
assigned a value to the variable, in another module (not the module of the
form or report). Is it possible to use a public variable, declared (and
assigned a value) in another module, as control source of an element on a
report or form? I tried with =modules![module_name]![variable_name], without
success.

Thanks for help.

 

[Vadim Rapp]

HL> I've tried also with a public variable, but I've declared the variable,
HL> and assigned a value to the variable, in another module (not the module
HL> of the form or report). Is it possible to use a public variable,
HL> declared (and assigned a value) in another module, as control source of
HL> an element on a report or form?

if public variable is in a module, then you can use it anywhere by name. For
example, =variable_name as controlsource.

If public variable is in a form, and the form is loaded, then you can use it
as forms("form_name").variable

tried with =modules![module_name]![variable_name], without success.

! is substitute for enumeration. collection!member is equivalent to
collection("member") . In a form, which is a class, variable is a member of
the class, and is represented not by exclamation mark but by period. The
object created from the class is forms("form_name"), so
forms("form_name").[variable_name] would work. Standard module is not class,
it's the object itself, so module_name.variable_name would work - or you can
omit the module and use the variable by name.


Vadim Rapp

 

[Robert Morley]

if public variable is in a module, then you can use it anywhere by name.

For example, =variable_name as controlsource.

Did I misunderstand this or something? I was pretty sure you couldn't do
that, but before I spoke up, I thought I'd test it to be sure. So I created
a new form and put a global variable as the ControlSource for a TextBox. I
tried both =tGlobal (which gets automatically changed to =[tGlobal]) and
=modGlobal.tGlobal (=[modGlobal].[tGlobal]), and either way I get #Name when
I open the form.

Is this maybe a new feature in Access 2003?



Rob

 

[Vadim Rapp]

Hello Robert,
You wrote in conference microsoft.public.access.adp.sqlserver on Wed, 7 Dec
2005 11:07:00 -0500:

RM>> if public variable is in a module, then you can use it anywhere by
RM>> name. For example, =variable_name as controlsource.

RM> Did I misunderstand this or something? I was pretty sure you couldn't
RM> do that, but before I spoke up, I thought I'd test it to be sure. So I
RM> created a new form and put a global variable as the ControlSource for a
RM> TextBox. I tried both =tGlobal (which gets automatically changed to
RM> =[tGlobal]) and =modGlobal.tGlobal (=[modGlobal].[tGlobal]), and either
RM> way I get #Name when I open the form.

I was wrong, thanks for pointing that out. A variable declared in a module
can be used in the code, but not as controlsource.

Vadim Rapp

 

[Educo Gent]

Thanks for the help !! Now I know that only public variables, declared in
the module of the form or the report, can be used as countrolsource of an
element of the same form or report..

 

[Robert Morley]

Actually, I think you can only use functions within a report, not
variables...if I'm wrong, though, let me know. I'm a little busy with other
things right now, and don't have the time to test it.


Rob

Thanks for the help !! Now I know that only public variables, declared in
the module of the form or the report, can be used as countrolsource of an
element of the same form or report..

Hello Robert,
You wrote in conference microsoft.public.access.adp.sqlserver on Wed, 7
Dec 2005 11:07:00 -0500:

RM>> if public variable is in a module, then you can use it anywhere by
RM>> name. For example, =variable_name as controlsource.

RM> Did I misunderstand this or something? I was pretty sure you
couldn't
RM> do that, but before I spoke up, I thought I'd test it to be sure. So
I
RM> created a new form and put a global variable as the ControlSource for
a
RM> TextBox. I tried both =tGlobal (which gets automatically changed to
RM> =[tGlobal]) and =modGlobal.tGlobal (=[modGlobal].[tGlobal]), and
either
RM> way I get #Name when I open the form.

I was wrong, thanks for pointing that out. A variable declared in a
module can be used in the code, but not as controlsource.

Vadim Rapp