Add a query based on a variable

Add a query based on a variable

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

date: Wed, 19 Sep 2007 09:03:39 -0700 author: Al

Re: Add a query based on a variable

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"  wrote in message 
news:1190217819.531844.178730@19g2000hsx.googlegroups.com...
> 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
>

date: Thu, 20 Sep 2007 16:22:14 +1000 author: Doug Robbins - Word MVP

Re: Add a query based on a variable

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

date: Thu, 20 Sep 2007 14:53:02 -0000 author: Al

Re: Add a query based on a variable

On Sep 21, 12:53 am, Al  wrote:
> 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

I'm not sure if the ProductID value is in a Userform Textbox control
or a document  text formfield or evan as a variable.
Assuming you can put it into a variable you can modify your SQL query
as shown below.  If the ID is not in a variable use textbox
control's .Text property, ot the formfields .Result property instead
of the variable I have used ("ProductIDvar").

If the value is in a Userform control you will not be able to access
it once you close the form.  The solution is to hide the form (using
yourUserformName.hide).  You can then Unload the userform when you are
finished with it.
BTW if you have moved the userform TextBox value to a variable in the
form you still need to hide the form to gat at the var until you
unload it.

WHERE"((`Product_ID` = '" & ProductIDvar & "')) ORDER BY

Note" there is a single quote before the double quote before the equal
sign and after the second ampersand.

Hope this helps.

Cheers
TonyS.

date: Thu, 20 Sep 2007 18:29:44 -0700 author: Tony Strazzeri