how to give a parameter to OutputTo Query

  • Thread starter Thread starter Alex
  • Start date Start date
A

Alex

I'm using the following to transfer data from MS Access to
Excel:

DoCmd.OutputTo acOutputQuery, _
"Query1", acFormatXLS, _
"ExcelFile.xls"

The Query1 is taking some value the exchange rate for
calculations. Before, I was using a textbox on a form to
give the Query1 this value. When the form is being opened
the fnEchangeRate function is being run and the value is
being assigned to the textbox.

How could I give the value from the function to the Query1
not through the textbox but (if it's possible) through a
parameter before running this DoCmd.OutputTo ...

Thanks
 
Hi Alex,

I assume that Query1 is referencing the textbox value using the
[Forms]![FormName]![TextboxName]
syntax.

IMO the simplest approach is to continue this way: have your code
calculate the value and put it in a textbox, and have the query refer to
the textbox. If desirable you can hide either the textbox or the form by
setting the Visible property to False.
 
Thanks a lot, John.
You're right I'm using this way.
But, They want to use this module from a different app.
This is why I'm thinking how to avoid to give the
parameter to the query through the textbox.
Can you advise anything?

Regards,

Alex
-----Original Message-----
Hi Alex,

I assume that Query1 is referencing the textbox value using the
[Forms]![FormName]![TextboxName]
syntax.

IMO the simplest approach is to continue this way: have your code
calculate the value and put it in a textbox, and have the query refer to
the textbox. If desirable you can hide either the textbox or the form by
setting the Visible property to False.

I'm using the following to transfer data from MS Access to
Excel:

DoCmd.OutputTo acOutputQuery, _
"Query1", acFormatXLS, _
"ExcelFile.xls"

The Query1 is taking some value the exchange rate for
calculations. Before, I was using a textbox on a form to
give the Query1 this value. When the form is being opened
the fnEchangeRate function is being run and the value is
being assigned to the textbox.

How could I give the value from the function to the Query1
not through the textbox but (if it's possible) through a
parameter before running this DoCmd.OutputTo ...

Thanks
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Alex,

In that case you'll probably need to wrap the OutputTo line in a public
procedure that accepts the arguments and modifies a query on the fly,
e.g.

Public Sub ExportToExcel(TheValue As Long)

CurrentDb.QueryDefs("Query1").SQL = _
"SELECT * FROM MyTable WHERE ID=" & TheValue & ";"
DoCmd.OutputTo acOutputQuery, _
"Query1", acFormatXLS, _
"ExcelFile.xls"

End Sub

Because this leaves the modified query, you should create a "dummy"
query just for this rather than use your existing one.



Thanks a lot, John.
You're right I'm using this way.
But, They want to use this module from a different app.
This is why I'm thinking how to avoid to give the
parameter to the query through the textbox.
Can you advise anything?

Regards,

Alex
-----Original Message-----
Hi Alex,

I assume that Query1 is referencing the textbox value using the
[Forms]![FormName]![TextboxName]
syntax.

IMO the simplest approach is to continue this way: have your code
calculate the value and put it in a textbox, and have the query refer to
the textbox. If desirable you can hide either the textbox or the form by
setting the Visible property to False.

I'm using the following to transfer data from MS Access to
Excel:

DoCmd.OutputTo acOutputQuery, _
"Query1", acFormatXLS, _
"ExcelFile.xls"

The Query1 is taking some value the exchange rate for
calculations. Before, I was using a textbox on a form to
give the Query1 this value. When the form is being opened
the fnEchangeRate function is being run and the value is
being assigned to the textbox.

How could I give the value from the function to the Query1
not through the textbox but (if it's possible) through a
parameter before running this DoCmd.OutputTo ...

Thanks
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...
 
Thanks a lot, John.
That's what I need.
-----Original Message-----
Alex,

In that case you'll probably need to wrap the OutputTo line in a public
procedure that accepts the arguments and modifies a query on the fly,
e.g.

Public Sub ExportToExcel(TheValue As Long)

CurrentDb.QueryDefs("Query1").SQL = _
"SELECT * FROM MyTable WHERE ID=" & TheValue & ";"
DoCmd.OutputTo acOutputQuery, _
"Query1", acFormatXLS, _
"ExcelFile.xls"

End Sub

Because this leaves the modified query, you should create a "dummy"
query just for this rather than use your existing one.



Thanks a lot, John.
You're right I'm using this way.
But, They want to use this module from a different app.
This is why I'm thinking how to avoid to give the
parameter to the query through the textbox.
Can you advise anything?

Regards,

Alex
-----Original Message-----
Hi Alex,

I assume that Query1 is referencing the textbox value using the
[Forms]![FormName]![TextboxName]
syntax.

IMO the simplest approach is to continue this way: have your code
calculate the value and put it in a textbox, and have
Click to expand...
the
query refer to
the textbox. If desirable you can hide either the
Click to expand...
textbox
or the form by
setting the Visible property to False.

On Fri, 1 Oct 2004 13:11:30 -0700, "Alex"

I'm using the following to transfer data from MS
Click to expand...
Access
to
Excel:

DoCmd.OutputTo acOutputQuery, _
"Query1", acFormatXLS, _
"ExcelFile.xls"

The Query1 is taking some value the exchange rate for
calculations. Before, I was using a textbox on a form to
give the Query1 this value. When the form is being opened
the fnEchangeRate function is being run and the value is
being assigned to the textbox.

How could I give the value from the function to the Query1
not through the textbox but (if it's possible) through a
parameter before running this DoCmd.OutputTo ...

Thanks

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
Click to expand...

--
John Nurick [Microsoft Access MVP]

Please respond in the newgroup and not by email.
.
Click to expand...
 
Back
Top