parameter report

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Educo Gent - 02 Dec 2005 13:50 GMT

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.

Reply to this Message

Educo Gent - 02 Dec 2005 14:02 GMT

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?

>I have a report in Access2002. The source of the report is a query on a SQL
>server2000.
[quoted text clipped - 11 lines]
> Thanks,
> Hugo L.

Reply to this Message

J. Clay - 02 Dec 2005 16:49 GMT

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.
[quoted text clipped - 11 lines]
> Thanks,
> Hugo L.

Reply to this Message

Educo Gent - 02 Dec 2005 17:30 GMT

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
[quoted text clipped - 20 lines]
>> Thanks,
>> Hugo L.

Reply to this Message

Vadim Rapp - 03 Dec 2005 13:28 GMT

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

Reply to this Message

Hugo Lefebvre - 04 Dec 2005 21:39 GMT

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.

> Hello,
> You wrote on Fri, 02 Dec 2005 13:50:15 GMT:
[quoted text clipped - 20 lines]
>
> Regards

Reply to this Message

Vadim Rapp - 06 Dec 2005 03:57 GMT

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

Reply to this Message

Robert Morley - 07 Dec 2005 16:07 GMT

> 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

Reply to this Message

Vadim Rapp - 08 Dec 2005 02:51 GMT

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

Reply to this Message

Educo Gent - 08 Dec 2005 08:13 GMT

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
[quoted text clipped - 16 lines]
>
> Vadim Rapp

Reply to this Message

Robert Morley - 08 Dec 2005 17:52 GMT

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
[quoted text clipped - 22 lines]
>>
>> Vadim Rapp

Reply to this Message

Vadim Rapp - 03 Dec 2005 13:30 GMT

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

Reply to this Message