Add a query based on a variable

Al · Guest

Hi everyone...

I have a word document that runs some VB code which prompts the
user to select an item from a list. Once an item is chosen, a separate
text field is automatically filled in. This works fine. I have also
added a query on the same page that gets info from an access database.
This works fine as well.

What I want to do is have one of the parameters of my query be the
value in the text field that was automatically filled in. That way, if
a user selected a different item on the list, the query would auto-
update. Currently, the query must be reconstructed manually - and it's
not something a regular user knows how to do...

The relevant portion of the field code in which the database info
resides is:

`Source` FROM `MY_TABLE` WHERE ((`Item` = 'my_item')) ORDER BY
`Price`

so instead of 'my_item' I want to say 'the value stored in the text
field'

How do I do that?

Thanks for the help,

Al

Google · Sponsor

Doug Robbins - Word MVP · Guest

It is not clear what type of fields you are using. If it is formfields as
used in a protected document, you can use the .Result property of the field
to get the value that is entered into it.

If the item that was filled in was actually a Docvariable field that was
displaying a value that was saved as a Document Variable, you could have
your query operate on the value of that Document Variable.

--
Hope this helps.

Please reply to the newsgroup unless you wish to avail yourself of my
services on a paid consulting basis.

Doug Robbins - Word MVP

"Al" <bernala@gmail.com> wrote in message
news:1190217819.531844.178730@19g2000hsx.googlegroups.com...

Al · Guest

Doug,

Thanks for the reply. I will try to clarify a bit. As I mentioned, my
word document displays a form with a list of products. The user then
selects a product, clicks OK, and the form closes. The macro then
retrieves the product code from an excel file, and places the value in
a text form field.

Aside from that, I have an independent database form field that
contains a query. This query links to an access database and lists all
customers who use a certain product code. The product code is
specified as a criteria in the query. Here is the field code for the
database field:

Selection.Range.InsertDatabase Format:=16, Style:=191,
LinkToSource:=True, _
Connection:= _
"Provider=Microsoft.Jet.OLEDB.4.0;Password="""";User ID=Admin;Data
Source=c:\Customers.mdb; Mode=Read;Extended Properties="""";Jet
OLEDB:System database="""";Jet OLEDB:Registry Path="""";Jet
OLEDB:Databas" _
, SQLStatement:= _
"SELECT `Customer Name` FROM `List of Customers` WHERE
((`Product_ID` = '123456')) ORDER BY `Price`" _
& "", PasswordDocument:="", PasswordTemplate:="",
WritePasswordDocument _
:="", WritePasswordTemplate:="", DataSource:= _
"c:\Customers.mdb", From:=-1, To:=-1, IncludeFields:=True

So in order to make sure the table displays the correct list of
customers, I have to manually change Product_ID` = '123456' to the
product code displayed in my text field. My question is, how can I
automate this? Since the user has already specified a product at the
beginning, and the corresponding product code is already visible in
the text form field, how do I get the query to read the value in the
text form field and update?

Thanks again.

Al

Tony Strazzeri · Guest

On Sep 21, 12:53 am, Al <bern...@gmail.com> wrote: