A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » New Users
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Pass field value selected in Combo Box to SQL Query



 
 
Thread Tools Display Modes
  #1  
Old March 8th, 2010, 01:40 PM posted to microsoft.public.access.gettingstarted
hamsa[_2_]
external usenet poster
 
Posts: 1
Default Pass field value selected in Combo Box to SQL Query

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.

  #2  
Old March 8th, 2010, 03:16 PM posted to microsoft.public.access.gettingstarted
Ed Robichaud
external usenet poster
 
Posts: 90
Default Pass field value selected in Combo Box to SQL Query

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.



  #3  
Old March 8th, 2010, 03:24 PM posted to microsoft.public.access.gettingstarted
John Spencer
external usenet poster
 
Posts: 7,815
Default Pass field value selected in Combo Box to SQL Query

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.

  #4  
Old March 8th, 2010, 07:09 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Pass field value selected in Combo Box to SQL Query

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

  #5  
Old March 9th, 2010, 05:07 AM posted to microsoft.public.access.gettingstarted
Ed Robichaud
external usenet poster
 
Posts: 90
Default Pass field value selected in Combo Box to SQL Query

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



  #6  
Old March 9th, 2010, 03:02 PM posted to microsoft.public.access.gettingstarted
hamsa via AccessMonster.com
external usenet poster
 
Posts: 2
Default Pass field value selected in Combo Box to SQL Query

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/For...arted/201003/1

  #7  
Old March 9th, 2010, 05:57 PM posted to microsoft.public.access.gettingstarted
KenSheridan via AccessMonster.com
external usenet poster
 
Posts: 1,610
Default Pass field value selected in Combo Box to SQL Query

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

  #8  
Old March 10th, 2010, 03:19 PM posted to microsoft.public.access.gettingstarted
hamsa via AccessMonster.com
external usenet poster
 
Posts: 2
Default Pass field value selected in Combo Box to SQL Query

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/For...arted/201003/1

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 05:12 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.