pass through query variables

Hi Guys,

I am looking for some help with a pass through query. I have a number of
reports that allow users to select values from combo box's and want to know
how I can pass this data in a pass through query.

I have looked through and searched but seem to just get sites that I have to
register to get some details, but do not wish to register elsewhere to get
some info.

If any has an example or links to any sites that would cover this I would
appreciate it.

An example of what I would want to pass is

[forms]![frmReports].[txtsupplierID]
[forms]![frmReports].[txtproductID] (the syntax might be wrong, just for
example)

Thanks
Si

What are you trying to pass the data to? The same database or another
database? Another table? Another query?
--
Milton Purdy
ACCESS
State of Arkansas


"SimonT" wrote:

Hi Guys,

I am looking for some help with a pass through query. I have a number of
reports that allow users to select values from combo box's and want to know
how I can pass this data in a pass through query.

I have looked through and searched but seem to just get sites that I have to
register to get some details, but do not wish to register elsewhere to get
some info.

If any has an example or links to any sites that would cover this I would
appreciate it.

An example of what I would want to pass is

[forms]![frmReports].[txtsupplierID]
[forms]![frmReports].[txtproductID] (the syntax might be wrong, just for
example)

Thanks
Si

.

On 4/20/2010 1:43 PM, golfinray wrote:
What are you trying to pass the data to? The same database or another
database? Another table? Another query?
I am going through this now. You are going to have to build the
passthrough query on the fly using VB., save it as a stored procedure,
then run it. You use the control's data when you build the SQL string.
When your code is finished, you will not have any references to any of
your controls, just the data which is coded as a constant into the string.

You will soon run into my question, which I now post.

Phil

Cheers Phil,

Many thanks for that, can I ask how you got on?...I have just seen the
response to your question and just trying to get my head around it to see if
I can get this to work.

Regards
Si

"Phil Smith" wrote in message
...
On 4/20/2010 1:43 PM, golfinray wrote:
What are you trying to pass the data to? The same database or another
database? Another table? Another query?
I am going through this now. You are going to have to build the
passthrough query on the fly using VB., save it as a stored procedure,
then run it. You use the control's data when you build the SQL string.
When your code is finished, you will not have any references to any of
your controls, just the data which is coded as a constant into the string.

You will soon run into my question, which I now post.

Phil

You can try to make a "call back":


SELECT *
FROM somewhere
WHERE id IN

(SELECT CustomerID
FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0',
'C:\Program Files\Microsoft
Office\OFFICE11\SAMPLES\Northwind.mdb';
'admin';'',Customers)
)





While this query is a pass-trough query, executed on the MS SQL Server, it
'calls' the Northwind.mdb database to get some information, here the
table/query Customers, with admin logon, using OPENROWSET.

In your case, that would probably call your own mdb file, so it would really
a "call ME back", and instead of the table Customers, it could be a table
where you would have put the parameters. Since that code really call Jet,
not Access, I suspect that


SELECT FORMS!formName!ControlName


would not work, since a brand new Jet instance won't know anything about
your existing Access instance, and that is why you probably need to use a
table to refer to the parameters. You can also use a linked_server, instead
of OPENROWSET.



Note that if you use something else than an mdb, then you have find the
appropriate provider, which is probably NOT : 'Microsoft.Jet.OLEDB.4.0'



Or, as someone has already suggested, you can produce ad hoc code, as a
string to be executed by your pass-trough query.




Vanderghast, Access MVP



"SimonT" wrote in message
...
Hi Guys,

I am looking for some help with a pass through query. I have a number of
reports that allow users to select values from combo box's and want to
know how I can pass this data in a pass through query.

I have looked through and searched but seem to just get sites that I have
to register to get some details, but do not wish to register elsewhere to
get some info.

If any has an example or links to any sites that would cover this I would
appreciate it.

An example of what I would want to pass is

[forms]![frmReports].[txtsupplierID]
[forms]![frmReports].[txtproductID] (the syntax might be wrong, just for
example)

Thanks
Si

So Far So good. You need to use VB to build your sql string. Your SQL
string is the final SQL you will be passing through.

As you build the string, your variables, (form controls, etc.) are put
into the string as values.
if
[forms]![frmReports].[txtsupplierID] = "txt117"
you would have in code
SQLWHERE = "WHERE invoice.Supplier =" & chr(34) &
[forms]![frmReports].[txtsupplierID] & chr(34)

This produces

WHERE invoice.Supplier ="txt117"

You put all of your pieces together into a SQL statement that is ready
to go, you save it out into a premade placeholder query, which you can
then run.

I can give you one of my examples if it will help.






On 4/22/2010 9:51 AM, SimonT wrote:
Cheers Phil,

Many thanks for that, can I ask how you got on?...I have just seen the
response to your question and just trying to get my head around it to
see if I can get this to work.

Regards
Si

"Phil Smith" wrote in message
...
On 4/20/2010 1:43 PM, golfinray wrote:
What are you trying to pass the data to? The same database or another
database? Another table? Another query?
I am going through this now. You are going to have to build the
passthrough query on the fly using VB., save it as a stored procedure,
then run it. You use the control's data when you build the SQL string.
When your code is finished, you will not have any references to any of
your controls, just the data which is coded as a constant into the
string.

You will soon run into my question, which I now post.

Phil

Hi Pete,

If you could send me your example I would really appreciate that thanks

Regards
Si

"Phil Smith" wrote in message
...
So Far So good. You need to use VB to build your sql string. Your SQL
string is the final SQL you will be passing through.

As you build the string, your variables, (form controls, etc.) are put
into the string as values.
if
[forms]![frmReports].[txtsupplierID] = "txt117"
you would have in code
SQLWHERE = "WHERE invoice.Supplier =" & chr(34) &
[forms]![frmReports].[txtsupplierID] & chr(34)

This produces

WHERE invoice.Supplier ="txt117"

You put all of your pieces together into a SQL statement that is ready to
go, you save it out into a premade placeholder query, which you can then
run.

I can give you one of my examples if it will help.






On 4/22/2010 9:51 AM, SimonT wrote:
Cheers Phil,

Many thanks for that, can I ask how you got on?...I have just seen the
response to your question and just trying to get my head around it to
see if I can get this to work.

Regards
Si

"Phil Smith" wrote in message
...
On 4/20/2010 1:43 PM, golfinray wrote:
What are you trying to pass the data to? The same database or another
database? Another table? Another query?
I am going through this now. You are going to have to build the
passthrough query on the fly using VB., save it as a stored procedure,
then run it. You use the control's data when you build the SQL string.
When your code is finished, you will not have any references to any of
your controls, just the data which is coded as a constant into the
string.

You will soon run into my question, which I now post.

Phil