Using Forms to Providre Critieria for Queries

[Mark Mesarch]

I had a question on here about trying to creat a drop down
menu (list box) for a query used for a form or a report
and John Vinson gave me the answer of creating an unbound
list on a unbound form. Then using the following code in
the criteria of the query.
=Forms!frmname!fieldname
Then on the unbound form creat a commond button that
opened the form or report that used the query.

The idea seems to work but I cannot get it to actually
function. His last message asked if I was using the
fieldname as a lookup in my original table. And I am.
Basically I have a two table database listing citations of
publications. TABLE A is has 3 fields: a publication id
(autonumber), a publication full name and a publication
abbreviation. TABLE B has numerous fields about articles
in the publications such as Title, Authors, Page Numbers
also a field that is a look-up of the Publication Full
Name from Table A. Actually the Publication ID is being
stored in Table B...at least I think that is what is
happening.

I want to have list box query the user for a publication
name and then return all the articles that come from that
publications. I could use a regular parameter query but
many publications have the same words in their titles (
e.g. Agricultural Meteorology ...Agriculture and Forest
Meteorology) I don't want the user to have to type in the
full name.

John asked for the SQL of the query and the row source
from the form. See Below

When I run this and get the resulting form I get now
matches...its like the query is not either 1) getting the
right information to match up the information in the query
or its not even getting anything.

Any body have any ideas.
Thanks


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

 

[Ted Allen]

Hi Mark,

The first thing that I would check is that the list box
name is in fact journalid (check the control's name
property). The expression that you have in your query
([Forms]![frmselectpub]![journalid]) is referencing the
control name, not the field name, as the controls
collection is the default collection for the forms
collection.

As you are probably aware the names of your controls may
or may not be the same as the name of your underlying
field name. Many people intentionally name the controls
something different than the field names to help keep
them straight (often using prefixes such as txt for text
box, cbo for combo, etc). Others seem to feel that it
isn't worth all of the extra work to rename all of the
controls. I guess I fall somewhere in the middle. I
often rename controls that I will be referencing in code,
but leave others alone.

If you don't explicitly name your controls, Access often
gives them the field name, but not always (such as cases
where that field name has already been used)

Post back if that isn't the problem.

-Ted Allen

-----Original Message-----
I had a question on here about trying to creat a drop down
menu (list box) for a query used for a form or a report
and John Vinson gave me the answer of creating an unbound
list on a unbound form. Then using the following code in
the criteria of the query.
=Forms!frmname!fieldname
Then on the unbound form creat a commond button that
opened the form or report that used the query.

The idea seems to work but I cannot get it to actually
function. His last message asked if I was using the
fieldname as a lookup in my original table. And I am.
Basically I have a two table database listing citations of
publications. TABLE A is has 3 fields: a publication id
(autonumber), a publication full name and a publication
abbreviation. TABLE B has numerous fields about articles
in the publications such as Title, Authors, Page Numbers
also a field that is a look-up of the Publication Full
Name from Table A. Actually the Publication ID is being
stored in Table B...at least I think that is what is
happening.

I want to have list box query the user for a publication
name and then return all the articles that come from that
publications. I could use a regular parameter query but
many publications have the same words in their titles (
e.g. Agricultural Meteorology ...Agriculture and Forest
Meteorology) I don't want the user to have to type in the
full name.

John asked for the SQL of the query and the row source
from the form. See Below

When I run this and get the resulting form I get now
matches...its like the query is not either 1) getting the
right information to match up the information in the query
or its not even getting anything.

Any body have any ideas.
Thanks


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


.

 

[Mark A. Mesarch]

Thanks you Ted...that did the ticket.

-----Original Message-----
Hi Mark,

The first thing that I would check is that the list box
name is in fact journalid (check the control's name
property). The expression that you have in your query
([Forms]![frmselectpub]![journalid]) is referencing the
control name, not the field name, as the controls
collection is the default collection for the forms
collection.

As you are probably aware the names of your controls may
or may not be the same as the name of your underlying
field name. Many people intentionally name the controls
something different than the field names to help keep
them straight (often using prefixes such as txt for text
box, cbo for combo, etc). Others seem to feel that it
isn't worth all of the extra work to rename all of the
controls. I guess I fall somewhere in the middle. I
often rename controls that I will be referencing in code,
but leave others alone.

If you don't explicitly name your controls, Access often
gives them the field name, but not always (such as cases
where that field name has already been used)

Post back if that isn't the problem.

-Ted Allen

-----Original Message-----
I had a question on here about trying to creat a drop down
menu (list box) for a query used for a form or a report
and John Vinson gave me the answer of creating an unbound
list on a unbound form. Then using the following code in
the criteria of the query.
=Forms!frmname!fieldname
Then on the unbound form creat a commond button that
opened the form or report that used the query.

The idea seems to work but I cannot get it to actually
function. His last message asked if I was using the
fieldname as a lookup in my original table. And I am.
Basically I have a two table database listing citations of
publications. TABLE A is has 3 fields: a publication id
(autonumber), a publication full name and a publication
abbreviation. TABLE B has numerous fields about articles
in the publications such as Title, Authors, Page Numbers
also a field that is a look-up of the Publication Full
Name from Table A. Actually the Publication ID is being
stored in Table B...at least I think that is what is
happening.

I want to have list box query the user for a publication
name and then return all the articles that come from that
publications. I could use a regular parameter query but
many publications have the same words in their titles (
e.g. Agricultural Meteorology ...Agriculture and Forest
Meteorology) I don't want the user to have to type in the
full name.

John asked for the SQL of the query and the row source
from the form. See Below

When I run this and get the resulting form I get now
matches...its like the query is not either 1) getting the
right information to match up the information in the query
or its not even getting anything.

Any body have any ideas.
Thanks


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


.

.

 

[Ted Allen]

My pleasure, glad it helped.

-----Original Message-----
Thanks you Ted...that did the ticket.

-----Original Message-----
Hi Mark,

The first thing that I would check is that the list box
name is in fact journalid (check the control's name
property). The expression that you have in your query
([Forms]![frmselectpub]![journalid]) is referencing the
control name, not the field name, as the controls
collection is the default collection for the forms
collection.

As you are probably aware the names of your controls may
or may not be the same as the name of your underlying
field name. Many people intentionally name the controls
something different than the field names to help keep
them straight (often using prefixes such as txt for text
box, cbo for combo, etc). Others seem to feel that it
isn't worth all of the extra work to rename all of the
controls. I guess I fall somewhere in the middle. I
often rename controls that I will be referencing in code,
but leave others alone.

If you don't explicitly name your controls, Access often
gives them the field name, but not always (such as cases
where that field name has already been used)

Post back if that isn't the problem.

-Ted Allen

-----Original Message-----
I had a question on here about trying to creat a drop down
menu (list box) for a query used for a form or a report
and John Vinson gave me the answer of creating an unbound
list on a unbound form. Then using the following

code
in

the criteria of the query.
=Forms!frmname!fieldname
Then on the unbound form creat a commond button that
opened the form or report that used the query.

The idea seems to work but I cannot get it to actually
function. His last message asked if I was using the
fieldname as a lookup in my original table. And I am.
Basically I have a two table database listing

citations
of

publications. TABLE A is has 3 fields: a publication id
(autonumber), a publication full name and a publication
abbreviation. TABLE B has numerous fields about articles
in the publications such as Title, Authors, Page Numbers
also a field that is a look-up of the Publication Full
Name from Table A. Actually the Publication ID is being
stored in Table B...at least I think that is what is
happening.

I want to have list box query the user for a publication
name and then return all the articles that come from that
publications. I could use a regular parameter query but
many publications have the same words in their titles (
e.g. Agricultural Meteorology ...Agriculture and Forest
Meteorology) I don't want the user to have to type in the
full name.

John asked for the SQL of the query and the row source
from the form. See Below

When I run this and get the resulting form I get now
matches...its like the query is not either 1) getting the
right information to match up the information in the query
or its not even getting anything.

Any body have any ideas.
Thanks


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


.

.

.