Hello,

My form displays a combo box populated by a SQL SELECT statement. The user
will select a row from the combo box. I then want the value in a field of the
row selected in the combo box  to be used as the selection criteria in a SQL
statement. 

The SQL statement is a nested SELECT and the value selected in the combo box
is the selection criteria in the inner select statement.

The problem is how to pass the value selected in the combo box to the SQL
query.

Also, I have the following problem . When I call the SQL query by clicking a
button, a window popups asking me to enter the value for the parameter. I
don't want this popup to appear. What I want to acheive is that after the
user selects a row in the combo box, I want the field value to be passed  to
the SQL statement's WHERE clause and the SQL should execute and display the
rows selected.

I tried to use the ON CHANGE event of the Combo box, but this then goes into
Microsift Visual basic where I need to code a call to the SQL. I don't know
anything about Visual Basic. So, if this is the way to do it, then please
give me the details of how to call the SQL from within Visual basic. If there
is another way to acheive this, that is also fine.

I am new to MS access 2007, so please give me a step by step guidance to do
this.

Thanks in Advance.

And where would you use/display the results of this select query?  You're 
using a combo box on your form to select filter criteria; do you then want 
to show filtered records on that same form or subform, or perhaps run a 
report?

One typical setup is to use an unbound combo box to filter display of 
records based on value selected in the combo.  Use the combo box wizard and 
select the option to filter records.


Another setup is to pass the value from that unbound combo box to your 
query.

In the design grid of your query, put a statement similar to:

Like IIf([forms]![frmMyForm]![cmbMyCombo] Is 
Null,"*",[forms]![frmMyForm]![cmbMyCombo])

in the criteria row of the column you want to filter on.  Also remove the 
parameter statement - this is what causes the input box to display.  Your 
query will now select records based on the value showing in the form's combo 
box, and display all records, if there is no selection.

To run the query directly from your form, you'll need a

DoCmd.RunQuery "MyQuery"

statement that is put in the AfterUpdate event of your combo box, or better 
still on the OnClick event of a command button that you add (there's a 
command button wizard to help you).
-Ed


"hamsa" <u58624@uwe> wrote in message news:a4b36ceaabcbb@uwe...
> Hello,
>
> My form displays a combo box populated by a SQL SELECT statement. The user
> will select a row from the combo box. I then want the value in a field of 
> the
> row selected in the combo box  to be used as the selection criteria in a 
> SQL
> statement.
>
> The SQL statement is a nested SELECT and the value selected in the combo 
> box
> is the selection criteria in the inner select statement.
>
> The problem is how to pass the value selected in the combo box to the SQL
> query.
>
> Also, I have the following problem . When I call the SQL query by clicking 
> a
> button, a window popups asking me to enter the value for the parameter. I
> don't want this popup to appear. What I want to acheive is that after the
> user selects a row in the combo box, I want the field value to be passed 
> to
> the SQL statement's WHERE clause and the SQL should execute and display 
> the
> rows selected.
>
> I tried to use the ON CHANGE event of the Combo box, but this then goes 
> into
> Microsift Visual basic where I need to code a call to the SQL. I don't 
> know
> anything about Visual Basic. So, if this is the way to do it, then please
> give me the details of how to call the SQL from within Visual basic. If 
> there
> is another way to acheive this, that is also fine.
>
> I am new to MS access 2007, so please give me a step by step guidance to 
> do
> this.
>
> Thanks in Advance.
> 

Normally, you would refer to the control in the query.  The form must be open 
when the query executes.  For a combobox that will return the value of the row 
that is selected and the column that you have designated as the bound column.

     Forms![Name of Form]![Name of Combobox]

That may not work for you depending on how you have nested the queries.  If 
you have used a sub-query in the from clause then this may fail.  If you have 
a saved query that you are referencing in the from clause then this should 
work with no problem.

If this does not work for you, then you need to post the SQL of your queries 
so we can see what is happening.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

hamsa wrote:
> Hello,
> 
> My form displays a combo box populated by a SQL SELECT statement. The user
> will select a row from the combo box. I then want the value in a field of the
> row selected in the combo box  to be used as the selection criteria in a SQL
> statement. 
> 
> The SQL statement is a nested SELECT and the value selected in the combo box
> is the selection criteria in the inner select statement.
> 
> The problem is how to pass the value selected in the combo box to the SQL
> query.
> 
> Also, I have the following problem . When I call the SQL query by clicking a
> button, a window popups asking me to enter the value for the parameter. I
> don't want this popup to appear. What I want to acheive is that after the
> user selects a row in the combo box, I want the field value to be passed  to
> the SQL statement's WHERE clause and the SQL should execute and display the
> rows selected.
> 
> I tried to use the ON CHANGE event of the Combo box, but this then goes into
> Microsift Visual basic where I need to code a call to the SQL. I don't know
> anything about Visual Basic. So, if this is the way to do it, then please
> give me the details of how to call the SQL from within Visual basic. If there
> is another way to acheive this, that is also fine.
> 
> I am new to MS access 2007, so please give me a step by step guidance to do
> this.
> 
> Thanks in Advance.
> 

Ed:

That’s not a reliable way of 'optionalizing' a parameter I'm afraid, as any
row with a NULL in the column in question won't be returned as NULL LIKE "*"
evaluates to NULL, neither TRUE nor FALSE.  Testing for the parameter being
NULL in a Boolean OR operation is the best way to do it:

WHERE ([SomeField] = [Forms]![frmMyForm]![cmbMyCombo]
OR [Forms]![frmMyForm]![cmbMyCombo] IS NULL)

If the control is Null the second part of the OR operation will evaluate to
TRUE regardless of the value, or absence of a value, in the SomeField column.

I'm not sure what you mean by "remove the parameter statement - this is what
causes the input box to display".  Are you saying that a PARAMETERS clause in
a query will generate a prompt regardless of whether the parameter is an
accessible object or not?  If so that's not the case.  In fact with certain
parameter types, particularly date/time, its prudent (and in a crosstab query
essential for any parameters) to always declare them to avoid any inadvertent
misinterpretation of the data type, e.g. a date might be interpreted as an
arithmetical expression; which would not cause an error, just give the wrong
results.  The unexpected generation of a prompt is more likely to be the
result either of the referenced object not being currently exposed, or a
simple misspelling of the object name.

Ken Sheridan
Stafford, England

Ed Robichaud wrote:
>And where would you use/display the results of this select query?  You're 
>using a combo box on your form to select filter criteria; do you then want 
>to show filtered records on that same form or subform, or perhaps run a 
>report?
>
>One typical setup is to use an unbound combo box to filter display of 
>records based on value selected in the combo.  Use the combo box wizard and 
>select the option to filter records.
>
>Another setup is to pass the value from that unbound combo box to your 
>query.
>
>In the design grid of your query, put a statement similar to:
>
>Like IIf([forms]![frmMyForm]![cmbMyCombo] Is 
>Null,"*",[forms]![frmMyForm]![cmbMyCombo])
>
>in the criteria row of the column you want to filter on.  Also remove the 
>parameter statement - this is what causes the input box to display.  Your 
>query will now select records based on the value showing in the form's combo 
>box, and display all records, if there is no selection.
>
>To run the query directly from your form, you'll need a
>
>DoCmd.RunQuery "MyQuery"
>
>statement that is put in the AfterUpdate event of your combo box, or better 
>still on the OnClick event of a command button that you add (there's a 
>command button wizard to help you).
>-Ed
>
>> Hello,
>>
>[quoted text clipped - 36 lines]
>>
>> Thanks in Advance.

-- 
Message posted via http://www.accessmonster.com

As usual, I  stand corrected.   I meant to write:

Like IIf([forms]![frmMyForm]![cmbMyCombo] Is
Null,"*",[forms]![frmMyForm]![cmbMyCombo]) or is Null

which should return the intended recordset.  And yes, declared parameters 
are best, and the input box is probably the result of an inaccessable 
control.  Thanks for keeping me sharp(er).

"KenSheridan via AccessMonster.com" <u51882@uwe> wrote in message 
news:a4b64ad75050f@uwe...
> Ed:
>
> That's not a reliable way of 'optionalizing' a parameter I'm afraid, as 
> any
> row with a NULL in the column in question won't be returned as NULL LIKE 
> "*"
> evaluates to NULL, neither TRUE nor FALSE.  Testing for the parameter 
> being
> NULL in a Boolean OR operation is the best way to do it:
>
> WHERE ([SomeField] = [Forms]![frmMyForm]![cmbMyCombo]
> OR [Forms]![frmMyForm]![cmbMyCombo] IS NULL)
>
> If the control is Null the second part of the OR operation will evaluate 
> to
> TRUE regardless of the value, or absence of a value, in the SomeField 
> column.
>
> I'm not sure what you mean by "remove the parameter statement - this is 
> what
> causes the input box to display".  Are you saying that a PARAMETERS clause 
> in
> a query will generate a prompt regardless of whether the parameter is an
> accessible object or not?  If so that's not the case.  In fact with 
> certain
> parameter types, particularly date/time, its prudent (and in a crosstab 
> query
> essential for any parameters) to always declare them to avoid any 
> inadvertent
> misinterpretation of the data type, e.g. a date might be interpreted as an
> arithmetical expression; which would not cause an error, just give the 
> wrong
> results.  The unexpected generation of a prompt is more likely to be the
> result either of the referenced object not being currently exposed, or a
> simple misspelling of the object name.
>
> Ken Sheridan
> Stafford, England
>
> Ed Robichaud wrote:
>>And where would you use/display the results of this select query?  You're
>>using a combo box on your form to select filter criteria; do you then want
>>to show filtered records on that same form or subform, or perhaps run a
>>report?
>>
>>One typical setup is to use an unbound combo box to filter display of
>>records based on value selected in the combo.  Use the combo box wizard 
>>and
>>select the option to filter records.
>>
>>Another setup is to pass the value from that unbound combo box to your
>>query.
>>
>>In the design grid of your query, put a statement similar to:
>>
>>Like IIf([forms]![frmMyForm]![cmbMyCombo] Is
>>Null,"*",[forms]![frmMyForm]![cmbMyCombo])
>>
>>in the criteria row of the column you want to filter on.  Also remove the
>>parameter statement - this is what causes the input box to display.  Your
>>query will now select records based on the value showing in the form's 
>>combo
>>box, and display all records, if there is no selection.
>>
>>To run the query directly from your form, you'll need a
>>
>>DoCmd.RunQuery "MyQuery"
>>
>>statement that is put in the AfterUpdate event of your combo box, or 
>>better
>>still on the OnClick event of a command button that you add (there's a
>>command button wizard to help you).
>>-Ed
>>
>>> Hello,
>>>
>>[quoted text clipped - 36 lines]
>>>
>>> Thanks in Advance.
>
> -- 
> Message posted via http://www.accessmonster.com
> 

Dear All,

Thank You very much for the reply. I will give the code that I am using below:


SELECT Sutras.SutraNo, Index.Properties, Sutras.Sutra
FROM Sutras INNER JOIN [Index] ON Sutras.SutraId=Index.SutraId
WHERE (((Index.TopicId) In (SELECT Topics.TopicId FROM Topics  WHERE Topics.
Topic = [Enter the Dhatu Name  (Ex: - bhü)] )))
ORDER BY Sutras.SutraNo;

In the Inner SELECT statement, I have the code  [Enter the Dhatu Name  (Ex: -
bhü)] after =.
I want to remove this and instead use the value in the column of the row
selected in the combo box.
How do I do that? I can use either a Combo box event like On Change or I can
also have an EXECUTE button which when clicked will pass the value from the
combo box to the Inner SELECT statement of the SQl query. My requirement is
that I have to avoid the parameter popup window as the user has already
selected the row in the combo box.

I retained the present code as I coult not figure out how to pass the value
from the combo box to the SQL query. 

After a row is selected in the combo box, I want the value of the column in
the row selected passed as the selection criteria to the Inner SELECT. 

At present, the COmbo box Control source property is blank and the Bound
Column property is set to 1.




John Spencer wrote:
>Normally, you would refer to the control in the query.  The form must be open 
>when the query executes.  For a combobox that will return the value of the row 
>that is selected and the column that you have designated as the bound column.
>
>     Forms![Name of Form]![Name of Combobox]
>
>That may not work for you depending on how you have nested the queries.  If 
>you have used a sub-query in the from clause then this may fail.  If you have 
>a saved query that you are referencing in the from clause then this should 
>work with no problem.
>
>If this does not work for you, then you need to post the SQL of your queries 
>so we can see what is happening.
>
>(Possibly unneeded instructions follow)
>Open the query
>Select View:Sql from the Menu
>Select all the text
>Copy it
>Paste it into the message
>
>John Spencer
>Access MVP 2002-2005, 2007-2010
>The Hilltop Institute
>University of Maryland Baltimore County
>
>> Hello,
>> 
>[quoted text clipped - 26 lines]
>> 
>> Thanks in Advance.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201003/1

Set the combo box up as follows:

Name:    cboTopic

RowSource:     SELECT TopicID, Topic FROM Topics ORDER BY Topic;

BoundColumn:   1
ColumnCount:    2
ColumnWidths:  0cm;8cm

If your units of measurement are imperial rather than metric Access will
automatically convert them.  The important thing is that the first dimension
is zero to hide the first column and that the second is at least as wide as
the combo box.

The query will then be:

SELECT Sutras.SutraNo, Index.Properties, Sutras.Sutra
FROM Sutras INNER JOIN [Index] ON Sutras.SutraId=Index.SutraId
WHERE Index.TopicId = Forms![YourForm]![cboTopic]
ORDER BY Sutras.SutraNo;

You can then open the query, or better still a form or report based on it,
with code in the AfterUpdate event procedure of the combo box or in the Click
event procedure of a separate button on the form; e.g. to open a form named
frmSutras with the above query as its RowSource you'd something kike the
following code:

Const MESSAGETEXT = "Please select a topic first."

If Not IsNull(Me.[cboTopic]) Then
    DoCmd.OpenForm "frmSutras"
Else
    MsgBox MESSAGETEXT, vbInformation, "Warning"
End If

Ken Sheridan
Stafford, England

hamsa wrote:
>Dear All,
>
>Thank You very much for the reply. I will give the code that I am using below:
>
>SELECT Sutras.SutraNo, Index.Properties, Sutras.Sutra
>FROM Sutras INNER JOIN [Index] ON Sutras.SutraId=Index.SutraId
>WHERE (((Index.TopicId) In (SELECT Topics.TopicId FROM Topics  WHERE Topics.
>Topic = [Enter the Dhatu Name  (Ex: - bhü)] )))
>ORDER BY Sutras.SutraNo;
>
>In the Inner SELECT statement, I have the code  [Enter the Dhatu Name  (Ex: -
>bhü)] after =.
>I want to remove this and instead use the value in the column of the row
>selected in the combo box.
>How do I do that? I can use either a Combo box event like On Change or I can
>also have an EXECUTE button which when clicked will pass the value from the
>combo box to the Inner SELECT statement of the SQl query. My requirement is
>that I have to avoid the parameter popup window as the user has already
>selected the row in the combo box.
>
>I retained the present code as I coult not figure out how to pass the value
>from the combo box to the SQL query. 
>
>After a row is selected in the combo box, I want the value of the column in
>the row selected passed as the selection criteria to the Inner SELECT. 
>
>At present, the COmbo box Control source property is blank and the Bound
>Column property is set to 1.
>
>>Normally, you would refer to the control in the query.  The form must be open 
>>when the query executes.  For a combobox that will return the value of the row 
>[quoted text clipped - 27 lines]
>>> 
>>> Thanks in Advance.

-- 
Message posted via http://www.accessmonster.com

Hello Ken,

Thank you.

After my last post and reading the replies, I got an understanding of how to
pass the value from the combo box to a SQL. I used the macro OPENQUERy and
called my SQl query and used the code
Forms![YourForm]![cboTopic].

It worked perfectly. Now the annoying popup doesn't appear anymore.

Thank You very much. All of you were very helpful and I thank each one of you.


Regards.

KenSheridan wrote:
>Set the combo box up as follows:
>
>Name:    cboTopic
>
>RowSource:     SELECT TopicID, Topic FROM Topics ORDER BY Topic;
>
>BoundColumn:   1
>ColumnCount:    2
>ColumnWidths:  0cm;8cm
>
>If your units of measurement are imperial rather than metric Access will
>automatically convert them.  The important thing is that the first dimension
>is zero to hide the first column and that the second is at least as wide as
>the combo box.
>
>The query will then be:
>
>SELECT Sutras.SutraNo, Index.Properties, Sutras.Sutra
>FROM Sutras INNER JOIN [Index] ON Sutras.SutraId=Index.SutraId
>WHERE Index.TopicId = Forms![YourForm]![cboTopic]
>ORDER BY Sutras.SutraNo;
>
>You can then open the query, or better still a form or report based on it,
>with code in the AfterUpdate event procedure of the combo box or in the Click
>event procedure of a separate button on the form; e.g. to open a form named
>frmSutras with the above query as its RowSource you'd something kike the
>following code:
>
>Const MESSAGETEXT = "Please select a topic first."
>
>If Not IsNull(Me.[cboTopic]) Then
>    DoCmd.OpenForm "frmSutras"
>Else
>    MsgBox MESSAGETEXT, vbInformation, "Warning"
>End If
>
>Ken Sheridan
>Stafford, England
>
>>Dear All,
>>
>[quoted text clipped - 30 lines]
>>>> 
>>>> Thanks in Advance.

-- 
Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/Forums.aspx/access-gettingstarted/201003/1