Drop Down Selection for Query

[Mark Mesarch]

HI,
I have a database that list publications and articles in
the publications. I use two tables. Table 1 is the one
containing publication names. Table 2 contains the
article names and page numbers and publication name that
the article is in. This pub name comes from Table 1 (a
relationship.) I want to creat a query to use in a
report or form that will list all the articles in ONE
specific publication. I could use a paramater query and
have the user type in the publication name, but I don't
like this for two reasons. One if I use a criteria of
LIKE "*"&[Enter a word in pub name]&"*" it two different
pubs have that word in their name I would get articles
from both pubs. Two if it is exact that is a lot to type
and it has to be perferct.

Is there a way to create a drop down menu from Table 1 so
the user can pick the specific journal title to use as the
criteria in the query?
Thanks.

 

[John Vinson]

Is there a way to create a drop down menu from Table 1 so
the user can pick the specific journal title to use as the
criteria in the query?

Yes; a parameter query can use *a form reference* as a criterion.
Create an unbound Form (let's call it frmCrit) with a combo box
listing the publications (cboPub); use a criterion of

=Forms!frmCrit!cboPub

Have a Form based on this query, and launch the form using a command
button on frmCrit.

Another way to do this is to have the articles form as a Subform on
the publications form, and use a combo box on the publications form to
navigate to the chosen publication; the combo box wizard will help you
set this up.

 

[Mark Mesarch]

I tried your first example and I am pretty close.
I created the first form with the combo box. The wizard
opened and I used this. I do remember it asking at one
point if I wanted to save the value. I do not remember
how I responded. Probably not right that time. I should
have said yes save the value. Long answer things did not
work.

So I started all over and created a new first form with
the combo box. The wizard openned again. But now I
never get the question about saving the value. I think
this is key, but cannot seem to figure out how to get this
values I pick saved from the combo box.

When I do the command button, at one point it asked it I
want to show all data or just some of the data. I would
assume the I should be showing some data, but field listed
on the combo box side do not make any sense...it is not
the name of the field from the combo box that I used.
Should be seeing the same field name?

Thanks.

 

[John Vinson]

I tried your first example and I am pretty close.
I created the first form with the combo box. The wizard
opened and I used this. I do remember it asking at one
point if I wanted to save the value. I do not remember
how I responded. Probably not right that time. I should
have said yes save the value.

Ummm... No. You should NOT save the value if all you're doing is using
the combo to find a record; if you save the value, it will overwrite
whichever value is first displayed on the form. In fact it's best to
make frmCrit an Unbound form with NO table attached - an unbound combo
on an unbound form.

Long answer things did not work.

So I started all over and created a new first form with
the combo box. The wizard openned again. But now I
never get the question about saving the value. I think
this is key, but cannot seem to figure out how to get this
values I pick saved from the combo box.

Again - YOU DON'T WANT TO SAVE IT.

When I do the command button, at one point it asked it I
want to show all data or just some of the data. I would
assume the I should be showing some data, but field listed
on the combo box side do not make any sense...it is not
the name of the field from the combo box that I used.
Should be seeing the same field name?

If you just base the second form on the Query referencing frmCrit,
then you want to show all the data. The query is already limited to
those records which match the combo box.

 

[Guest]

HI,
Ok. I think I understand why I do not want to save that
value.
But I cannot find anything in the help, on-line help or in
my books about an unbound form. WHat is that and how do
I make it.

Would it be helpful for me to attach my database and you
see what I have tried to do?
THanks.

 

[Mark Mesarch]

Sorry if this is a duplicate.

I think I understand about not saving the value.
But I cannot find anything in the help,on-line help and
books I have of what an unbound form is. How do you make
one?

Would it help for me to attach my database?
Thanks

 

[Mark Mesarch]

Please make sure you read the other reply too.

When I use the reference to the form inthe query you said
to use it like the following
=Forms!frmCrit!cboPub

But when I enter it becomes
[Forms]![frmCrit]![cboPub]
yes there is no equal sign at the beginning. Is this the
same thing?
THanks.

 

[John Vinson]

Please make sure you read the other reply too.

When I use the reference to the form inthe query you said
to use it like the following
=Forms!frmCrit!cboPub

But when I enter it becomes
[Forms]![frmCrit]![cboPub]
yes there is no equal sign at the beginning. Is this the
same thing?

Yes, it is.

Sending me the database: No, I'm sorry, I'm a self-employed consultant
donating my time on the newsgroups. I'd only be able to work on your
database with a consulting contract, and I'm pretty much booked at
present.

An Unbound Form is simply a Form with nothing in its Recordsource
property - it's not "bound" to any table.

 

[Mark Mesarch]

I seem to be following all of your steps. And the form was
unbounded.
What I select in the form drop down does not seem to be
received by the qry statement. So when the form from the
query opens it acts as form with one record. Based on
the database and the publication that I am selecting it
should be a form with 4 articles.

Is there a way I can debug this by looking at the =Forms!
frmCrit!cboPub after I select it on the form to make sure
something is getting to the query?

Thanks.

-----Original Message-----

Please make sure you read the other reply too.

When I use the reference to the form inthe query you said
to use it like the following
=Forms!frmCrit!cboPub

But when I enter it becomes
[Forms]![frmCrit]![cboPub]
yes there is no equal sign at the beginning. Is this the
same thing?

Yes, it is.

Sending me the database: No, I'm sorry, I'm a self- employed consultant
donating my time on the newsgroups. I'd only be able to work on your
database with a consulting contract, and I'm pretty much booked at
present.

An Unbound Form is simply a Form with nothing in its Recordsource
property - it's not "bound" to any table.


.

 

[John Vinson]

I seem to be following all of your steps. And the form was
unbounded.
What I select in the form drop down does not seem to be
received by the qry statement. So when the form from the
query opens it acts as form with one record. Based on
the database and the publication that I am selecting it
should be a form with 4 articles.

Is there a way I can debug this by looking at the =Forms!
frmCrit!cboPub after I select it on the form to make sure
something is getting to the query?

Please post the actual SQL of the query, and the RowSource and Bound
Column properties of the combo box. Are you perhaps searching a Lookup
field?

 

[Mark Mesarch]

Hi,
Here is the SQL
SELECT tblpublicationlist.Refnum,
tblpublicationlist.author, tblpublicationlist.journalid
FROM tblpublicationlist
WHERE (((tblpublicationlist.journalid)=[Forms]!
[frmselectpub]![journalid]));

Row Source
SELECT [tbljournalname].[journalid], [tbljournalname].
[journalfullname] FROM tbljournalname;

Bound Column Properties
1

Yes I am using the "look up" in the article table to fill
the journal name column. That way the journals are
exact. I have heard before that Access doesn't do a good
job with the Look up wizard, but did not think that would
be a problem. We have a publication table that has
both the full pub name and accepted abbreviation in a
separate field. I use a jounral id (autonumber) to make
the connection between the two tables.

Thanks.,
Mark