Updating Multiple Pass-Through Queries through a Form

[ryan05 via AccessMonster.com]

I have 3 SQL PT queries that I'm trying to have a field populate in all 3 in
the where statement through a form. I'm looking for a form that pops up that
says enter in the data and then have all 3 SQL PT queries update the where
statement with the data. I hope this makes sense. Any ideas? Thanks in
advance!

 

[Duane Hookom]

You can use DAO code to modify the SQL property of your saved P-T query. In
its simplest form:
CurrentDb.QueryDefs("qsptYourPT").SQL = "SELECT ....."

 

[ryan05 via AccessMonster.com]

Sorry, I'm a beginner so I don't understand the line below. How can I create
a form to enter in the item #'s which then populates the item_nbr in the
where statement?

You can use DAO code to modify the SQL property of your saved P-T query. In
its simplest form:
CurrentDb.QueryDefs("qsptYourPT").SQL = "SELECT ....."

I have 3 SQL PT queries that I'm trying to have a field populate in all 3
in

[quoted text clipped - 3 lines]

statement with the data. I hope this makes sense. Any ideas? Thanks in
advance!

 

[Duane Hookom]

Can you provide:
-at least one sql view from a pass-through query
-a form and control names (you may need to create these if you don't have
them)
-where in the p-t do you want the values from the controls on your form

--
Duane Hookom
MS Access MVP
--

Sorry, I'm a beginner so I don't understand the line below. How can I
create
a form to enter in the item #'s which then populates the item_nbr in the
where statement?

You can use DAO code to modify the SQL property of your saved P-T query.
In
its simplest form:
CurrentDb.QueryDefs("qsptYourPT").SQL = "SELECT ....."

I have 3 SQL PT queries that I'm trying to have a field populate in all 3
in

[quoted text clipped - 3 lines]

statement with the data. I hope this makes sense. Any ideas? Thanks in
advance!

 

[ryan05 via AccessMonster.com]

Below is a simplified version. I'm trying to have a form where I enter in the
item # (9912345 is 1 example) or multiple item #'s and have it populate the
where statement in the 3 different PT queries. The old_nbr is called item_nbr
in 1 of the 3 PT queries.

select
t2.old_nbr,
t5.whse_nbr,
t2.primary_desc,
t2.vendor_name

from
wm_user.item_desc_sec_indx t1

where
t1.old_nbr in
(1234567)

Can you provide:
-at least one sql view from a pass-through query
-a form and control names (you may need to create these if you don't have
them)
-where in the p-t do you want the values from the controls on your form

Sorry, I'm a beginner so I don't understand the line below. How can I
create

[quoted text clipped - 11 lines]

 

[Duane Hookom]

Assuming a P-T named "qsptMyQuery" and a form with a text box named txtNbrs
where you can enter values like:
1234567
or
1234567,21323456,12395847

Your code in the form would be something like:

Dim strSQL as String
Dim strQueryName as String
strQueryName = "qsptMyQuery"
strSQL = "SELECT t2.old_nbr, t5.whse_nbr, t2.Primary_desc, t2.Vendor_Name "
& _
"FROM wm_user.item_desc_sec_indx t1 .." & _
" WHERE t1.old_nbr In (" & Me.txtNbrs & ")"
Currentdb.QueryDefs(strQueryName).SQL = strSQL

This requires a reference to the DAO object library.

--
Duane Hookom
MS Access MVP
--

Below is a simplified version. I'm trying to have a form where I enter in
the
item # (9912345 is 1 example) or multiple item #'s and have it populate
the
where statement in the 3 different PT queries. The old_nbr is called
item_nbr
in 1 of the 3 PT queries.

select
t2.old_nbr,
t5.whse_nbr,
t2.primary_desc,
t2.vendor_name

from
wm_user.item_desc_sec_indx t1

where
t1.old_nbr in
(1234567)

Can you provide:
-at least one sql view from a pass-through query
-a form and control names (you may need to create these if you don't have
them)
-where in the p-t do you want the values from the controls on your form

Sorry, I'm a beginner so I don't understand the line below. How can I
create

[quoted text clipped - 11 lines]

statement with the data. I hope this makes sense. Any ideas? Thanks in
advance!