Query based on combo box in Form

  • Thread starter Thread starter erica hale
  • Start date Start date
E

erica hale

Hello! I have form with combo box and want to run query
based on text in combo box. At the suggestion of a person
in newsgroup I put in =Forms!NameofForm!NameofBox as
criteria in query and run query via a macro button on
form; however, I get no records in my query.

Anyone had this problem? Any ideas? I appreciate any
help! Thanks
 
Erica-

What's the Row Source of the combo box? What is the setting in the combo
box for Bound Column? And finally, what is the SQL of the query that
references this combo box as a filter? It's a common error to pick the
wrong column to filter, especially when the row source of the combo has
multiple columns.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Hi John.

My table name is P30_LRun100103110041

The row source for the combo box on the form is SELECT
[P30_LRun100103110041].[ID], [P30_LRun100103110041].
[Policy #] FROM P30_LRun100103110041;

The Bound column setting = 1

and the SQL of the query is . . . FROM P30_LRun100103110041
WHERE (((P30_LRun100103110041.[Policy #])=[forms]!
[frmPolicy]![cboPolicy]));

I have confirmed the form is still open when I run the
query. I appreciate any help. Thanks
 
Exactly as I suspected. You've "bound" the combo to the ID field, but your
query wants to do a search on the Policy # field. Set Bound Column to 2 (as
long as the combo is unbound - has no Control Source), and your query should
work fine. Or, change the query to use the ID field to apply the criteria.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
erica hale said:
Hi John.

My table name is P30_LRun100103110041

The row source for the combo box on the form is SELECT
[P30_LRun100103110041].[ID], [P30_LRun100103110041].
[Policy #] FROM P30_LRun100103110041;

The Bound column setting = 1

and the SQL of the query is . . . FROM P30_LRun100103110041
WHERE (((P30_LRun100103110041.[Policy #])=[forms]!
[frmPolicy]![cboPolicy]));

I have confirmed the form is still open when I run the
query. I appreciate any help. Thanks

-----Original Message-----
Erica-

What's the Row Source of the combo box? What is the setting in the combo
box for Bound Column? And finally, what is the SQL of the query that
references this combo box as a filter? It's a common error to pick the
wrong column to filter, especially when the row source of the combo has
multiple columns.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



.
Click to expand...
Click to expand...
 
Hi John.

I changed Bound Column to 2, but the query didn't work.
How do I use the ID field to apply the criteria? That is
an autonumber assigned by Access. I added the field to
the query - but how do I use that in conjunction with the
text in combo box as criteria? (I'm confused!)

I appreciate your help very much, thanks!
-----Original Message-----
Exactly as I suspected. You've "bound" the combo to the ID field, but your
query wants to do a search on the Policy # field. Set Bound Column to 2 (as
long as the combo is unbound - has no Control Source), and your query should
work fine. Or, change the query to use the ID field to apply the criteria.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
erica hale said:
Hi John.

My table name is P30_LRun100103110041

The row source for the combo box on the form is SELECT
[P30_LRun100103110041].[ID], [P30_LRun100103110041].
[Policy #] FROM P30_LRun100103110041;

The Bound column setting = 1

and the SQL of the query is . . . FROM P30_LRun100103110041
WHERE (((P30_LRun100103110041.[Policy #])=[forms]!
[frmPolicy]![cboPolicy]));

I have confirmed the form is still open when I run the
query. I appreciate any help. Thanks

-----Original Message-----
Erica-

What's the Row Source of the combo box? What is the setting in the combo
box for Bound Column? And finally, what is the SQL of the query that
references this combo box as a filter? It's a common error to pick the
wrong column to filter, especially when the row source
Click to expand...
of
the combo has
multiple columns.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello! I have form with combo box and want to run query
based on text in combo box. At the suggestion of a person
in newsgroup I put in =Forms!NameofForm!NameofBox as
criteria in query and run query via a macro button on
form; however, I get no records in my query.

Anyone had this problem? Any ideas? I appreciate any
help! Thanks


.
Click to expand...
Click to expand...


.
Click to expand...
 
Setting the Bound Column to 2 should have worked. The "value" of the combo
box should be the Policy # when you do that, so your comparison should find
the row(s) that match that Policy # when you have selected a value in the
combo and then run the query. What happens when you run the query? Do you
get prompted for the parameter? Does the query run without prompting but
you get the wrong result?

To test for ID, set the Bound Column back to 1 and change your parameter
query to:

. . FROM P30_LRun100103110041
WHERE (((P30_LRun100103110041.[ID])=[forms]!
[frmPolicy]![cboPolicy]));

In all of this, I'm assuming that the name of the form is frmPolicy and the
name of the combo box is cboPolicy. Right?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
erica said:
Hi John.

I changed Bound Column to 2, but the query didn't work.
How do I use the ID field to apply the criteria? That is
an autonumber assigned by Access. I added the field to
the query - but how do I use that in conjunction with the
text in combo box as criteria? (I'm confused!)

I appreciate your help very much, thanks!
-----Original Message-----
Exactly as I suspected. You've "bound" the combo to the ID field, but your
query wants to do a search on the Policy # field. Set Bound Column to 2 (as
long as the combo is unbound - has no Control Source), and your query should
work fine. Or, change the query to use the ID field to apply the criteria.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
erica hale said:
Hi John.

My table name is P30_LRun100103110041

The row source for the combo box on the form is SELECT
[P30_LRun100103110041].[ID], [P30_LRun100103110041].
[Policy #] FROM P30_LRun100103110041;

The Bound column setting = 1

and the SQL of the query is . . . FROM P30_LRun100103110041
WHERE (((P30_LRun100103110041.[Policy #])=[forms]!
[frmPolicy]![cboPolicy]));

I have confirmed the form is still open when I run the
query. I appreciate any help. Thanks


-----Original Message-----
Erica-

What's the Row Source of the combo box? What is the
setting in the combo
box for Bound Column? And finally, what is the SQL of
the query that
references this combo box as a filter? It's a common
error to pick the
wrong column to filter, especially when the row source of
the combo has
multiple columns.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello! I have form with combo box and want to run query
based on text in combo box. At the suggestion of a
person
in newsgroup I put in =Forms!NameofForm!NameofBox as
criteria in query and run query via a macro button on
form; however, I get no records in my query.

Anyone had this problem? Any ideas? I appreciate any
help! Thanks


.
Click to expand...


.
Click to expand...
Click to expand...
 
Hi John. You were right, setting the bound column to 2
did make the query work! I must not have done that the
first time you suggested it. Sorry for confusing you.

You have helped me so much with this problem -- now I can
move on to the next one. Thanks again! I appreciate it
very much.

-----Original Message-----
Setting the Bound Column to 2 should have worked. The "value" of the combo
box should be the Policy # when you do that, so your comparison should find
the row(s) that match that Policy # when you have selected a value in the
combo and then run the query. What happens when you run the query? Do you
get prompted for the parameter? Does the query run without prompting but
you get the wrong result?

To test for ID, set the Bound Column back to 1 and change your parameter
query to:

. . FROM P30_LRun100103110041
WHERE (((P30_LRun100103110041.[ID])=[forms]!
[frmPolicy]![cboPolicy]));

In all of this, I'm assuming that the name of the form is frmPolicy and the
name of the combo box is cboPolicy. Right?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
erica said:
Hi John.

I changed Bound Column to 2, but the query didn't work.
How do I use the ID field to apply the criteria? That is
an autonumber assigned by Access. I added the field to
the query - but how do I use that in conjunction with the
text in combo box as criteria? (I'm confused!)

I appreciate your help very much, thanks!
-----Original Message-----
Exactly as I suspected. You've "bound" the combo to
Click to expand...
the
ID field, but your
query wants to do a search on the Policy # field. Set Bound Column to 2 (as
long as the combo is unbound - has no Control Source), and your query should
work fine. Or, change the query to use the ID field to apply the criteria.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR? pg=personal&fr_id=1090&px=1434411
Hi John.

My table name is P30_LRun100103110041

The row source for the combo box on the form is SELECT
[P30_LRun100103110041].[ID], [P30_LRun100103110041].
[Policy #] FROM P30_LRun100103110041;

The Bound column setting = 1

and the SQL of the query is . . . FROM P30_LRun100103110041
WHERE (((P30_LRun100103110041.[Policy #])=[forms]!
[frmPolicy]![cboPolicy]));

I have confirmed the form is still open when I run the
query. I appreciate any help. Thanks


-----Original Message-----
Erica-

What's the Row Source of the combo box? What is the
setting in the combo
box for Bound Column? And finally, what is the SQL of
the query that
references this combo box as a filter? It's a common
error to pick the
wrong column to filter, especially when the row
Click to expand...
source
of
the combo has
multiple columns.

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Hello! I have form with combo box and want to run query
based on text in combo box. At the suggestion of a
person
in newsgroup I put in =Forms!NameofForm!NameofBox as
criteria in query and run query via a macro button on
form; however, I get no records in my query.

Anyone had this problem? Any ideas? I appreciate any
help! Thanks


.



.
Click to expand...
Click to expand...


.
Click to expand...
 
John Viescas said:
Setting the Bound Column to 2 should have worked. The "value" of the combo
box should be the Policy # when you do that, so your comparison should find
the row(s) that match that Policy # when you have selected a value in the
combo and then run the query. What happens when you run the query? Do you
get prompted for the parameter? Does the query run without prompting but
you get the wrong result?

To test for ID, set the Bound Column back to 1 and change your parameter
query to:

. . FROM P30_LRun100103110041
WHERE (((P30_LRun100103110041.[ID])=[forms]!
[frmPolicy]![cboPolicy]));

In all of this, I'm assuming that the name of the form is frmPolicy and the
name of the combo box is cboPolicy. Right?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out"
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
http://www.deanforamerica.com/site/TR?pg=personal&fr_id=1090&px=1434411
"erica" <[email protected]> wrote in message
news:[email protected]...
> Hi John.
>
> I changed Bound Column to 2, but the query didn't work.
> How do I use the ID field to apply the criteria? That is
> an autonumber assigned by Access. I added the field to
> the query - but how do I use that in conjunction with the
> text in combo box as criteria? (I'm confused!)
>
> I appreciate your help very much, thanks!
>
> >-----Original Message-----
> >Exactly as I suspected. You've "bound" the combo to the
> ID field, but your
> >query wants to do a search on the Policy # field. Set
> Bound Column to 2 (as
> >long as the combo is unbound - has no Control Source),
> and your query should
> >work fine. Or, change the query to use the ID field to
> apply the criteria.
> >
> >--
> >John Viescas, author
> >"Microsoft Office Access 2003 Inside Out"
> >"Running Microsoft Access 2000"
> >"SQL Queries for Mere Mortals"
> >http://www.viescas.com/
> >(Microsoft Access MVP since 1993)
> >http://www.deanforamerica.com/site/TR?
> pg=personal&fr_id=1090&px=1434411
> >"erica hale" <[email protected]> wrote in message
> >news:[email protected]...
> >> Hi John.
> >>
> >> My table name is P30_LRun100103110041
> >>
> >> The row source for the combo box on the form is SELECT
> >> [P30_LRun100103110041].[ID], [P30_LRun100103110041].
> >> [Policy #] FROM P30_LRun100103110041;
> >>
> >> The Bound column setting = 1
> >>
> >> and the SQL of the query is . . . FROM
> P30_LRun100103110041
> >> WHERE (((P30_LRun100103110041.[Policy #])=[forms]!
> >> [frmPolicy]![cboPolicy]));
> >>
> >> I have confirmed the form is still open when I run the
> >> query. I appreciate any help. Thanks
> >>
> >>
> >> >-----Original Message-----
> >> >Erica-
> >> >
> >> >What's the Row Source of the combo box? What is the
> >> setting in the combo
> >> >box for Bound Column? And finally, what is the SQL of
> >> the query that
> >> >references this combo box as a filter? It's a common
> >> error to pick the
> >> >wrong column to filter, especially when the row source
> of
> >> the combo has
> >> >multiple columns.
> >> >
> >> >--
> >> >John Viescas, author
> >> >"Microsoft Office Access 2003 Inside Out"
> >> >"Running Microsoft Access 2000"
> >> >"SQL Queries for Mere Mortals"
> >> >http://www.viescas.com/
> >> >(Microsoft Access MVP since 1993)
> >> >"erica hale" <[email protected]> wrote in message
> >> >news:[email protected]...
> >> >> Hello! I have form with combo box and want to run
> query
> >> >> based on text in combo box. At the suggestion of a
> >> person
> >> >> in newsgroup I put in =Forms!NameofForm!NameofBox as
> >> >> criteria in query and run query via a macro button on
> >> >> form; however, I get no records in my query.
> >> >>
> >> >> Anyone had this problem? Any ideas? I appreciate any
> >> >> help! Thanks
> >> >
> >> >
> >> >.
> >> >
> >
> >
> >.
> >
Click to expand...
Hi john,
I have the same issue for the selected value from the drop down list in form, The query is not working. Please help me out!!
 
Back
Top