Is my problem Query,Form or a Macro?

[Rebecca]

What I have is a tbl-Committees. In the tbl there is a column named
committees that is a lookup field from the tbl-committee.
What I need to accomplish:
My query now pulls up the committee I need, the year and list the members in
that committee

What I have that does this:
In the query, in the committees criteria I have [Name of the committee:]
when query is run, it pops up a box and I type in the committee name.
In the Year criteria I have [Year:]

This works great for me because I know the names of the committees and know
the "spelling" of them. (they are not all correctly spelled or have very
strange abbreviations because of the way the information use to be kept)

What I would like to do:
Is for the criteria to open a pull down menu or a scrolled menu to where the
person can click and say ok, the year can stay the way I have it.
My thoughts is I would need to do a form/macro of sorts. But I have no clue
where to start.
I would really appreciate the help!

If I need to ask this in a different newsgroup where I could get better
answers, please let me know.
Thanks in advance.
I am running Access 2002 SP3
Rebecca S.

 

[Wayne Morgan]

You are correct, you can do this on a form using a combobox. I just want to
make sure that you have 2 tables in what you mentioned, one that ends with
an "s" and one that doesn't. What field(s) are in each table?

On the form you would place a combobox with its Row Source set to the
information from tbl-committee. You could also place a textbox for the user
to enter the year. The criteria in the query would be changed to point to
the controls on the form for their values. The form has to be open for the
query to see it.

As an example, if the combobox on the form was called cboCommittee, the
criteria would be changed to

Forms!frmCriteriaForm!cboCommittee

Now, for a word on setting up the lookup field in the table:
http://www.mvps.org/access/lookupfields.htm

 

[Rebecca]

After looking what I typed and your question, The one table Committees has
fields:
MemberID, Committee, Date
The look up table is named CommitteeList the field:
Committee

I am going to go try what you wrote. Hopefully it will work for me, if not I
will be back with questions .

Thanks again!
Rebecca S.

You are correct, you can do this on a form using a combobox. I just want to
make sure that you have 2 tables in what you mentioned, one that ends with
an "s" and one that doesn't. What field(s) are in each table?

On the form you would place a combobox with its Row Source set to the
information from tbl-committee. You could also place a textbox for the user
to enter the year. The criteria in the query would be changed to point to
the controls on the form for their values. The form has to be open for the
query to see it.

As an example, if the combobox on the form was called cboCommittee, the
criteria would be changed to

Forms!frmCriteriaForm!cboCommittee

Now, for a word on setting up the lookup field in the table:
http://www.mvps.org/access/lookupfields.htm


--
Wayne Morgan
Microsoft Access MVP

What I have is a tbl-Committees. In the tbl there is a column named
committees that is a lookup field from the tbl-committee.
What I need to accomplish:
My query now pulls up the committee I need, the year and list the

members

in
that committee

What I have that does this:
In the query, in the committees criteria I have [Name of the committee:]
when query is run, it pops up a box and I type in the committee name.
In the Year criteria I have [Year:]

This works great for me because I know the names of the committees and know
the "spelling" of them. (they are not all correctly spelled or have very
strange abbreviations because of the way the information use to be kept)

What I would like to do:
Is for the criteria to open a pull down menu or a scrolled menu to where the
person can click and say ok, the year can stay the way I have it.
My thoughts is I would need to do a form/macro of sorts. But I have no clue
where to start.
I would really appreciate the help!

If I need to ask this in a different newsgroup where I could get better
answers, please let me know.
Thanks in advance.
I am running Access 2002 SP3
Rebecca S.

 

[Rebecca]

One other thing:

You said:
Are you referring to the criteria in the query I already have set up? I
would use this statement and it would open my new combobox committee form
where the person then would use the combo box to choose the committee, type
in the date and it would then give me the report I need?

Thanks once again!
Rebecca S.

You are correct, you can do this on a form using a combobox. I just want to
make sure that you have 2 tables in what you mentioned, one that ends with
an "s" and one that doesn't. What field(s) are in each table?

On the form you would place a combobox with its Row Source set to the
information from tbl-committee. You could also place a textbox for the user
to enter the year. The criteria in the query would be changed to point to
the controls on the form for their values. The form has to be open for the
query to see it.

As an example, if the combobox on the form was called cboCommittee, the
criteria would be changed to

Forms!frmCriteriaForm!cboCommittee

Now, for a word on setting up the lookup field in the table:
http://www.mvps.org/access/lookupfields.htm


--
Wayne Morgan
Microsoft Access MVP

What I have is a tbl-Committees. In the tbl there is a column named
committees that is a lookup field from the tbl-committee.
What I need to accomplish:
My query now pulls up the committee I need, the year and list the

members

in
that committee

What I have that does this:
In the query, in the committees criteria I have [Name of the committee:]
when query is run, it pops up a box and I type in the committee name.
In the Year criteria I have [Year:]

This works great for me because I know the names of the committees and know
the "spelling" of them. (they are not all correctly spelled or have very
strange abbreviations because of the way the information use to be kept)

What I would like to do:
Is for the criteria to open a pull down menu or a scrolled menu to

where
the

person can click and say ok, the year can stay the way I have it.
My thoughts is I would need to do a form/macro of sorts. But I have

no
clue

where to start.
I would really appreciate the help!

If I need to ask this in a different newsgroup where I could get better
answers, please let me know.
Thanks in advance.
I am running Access 2002 SP3
Rebecca S.

 

[Rebecca]

I am having no luck.

After making a form cbo_committee, I put the form in my queries criteria.
When I run the query, it opens the query with the column "committee" as a
pull down menu. It does pull open the list but it won't let me choose from
the list. The cbo_committee form does not even open.

Rebecca S.

One other thing:

You said:
Are you referring to the criteria in the query I already have set up? I
would use this statement and it would open my new combobox committee form
where the person then would use the combo box to choose the committee, type
in the date and it would then give me the report I need?

Thanks once again!
Rebecca S.

You are correct, you can do this on a form using a combobox. I just

want
to

make sure that you have 2 tables in what you mentioned, one that ends with
an "s" and one that doesn't. What field(s) are in each table?

On the form you would place a combobox with its Row Source set to the
information from tbl-committee. You could also place a textbox for the user
to enter the year. The criteria in the query would be changed to point to
the controls on the form for their values. The form has to be open for the
query to see it.

As an example, if the combobox on the form was called cboCommittee, the
criteria would be changed to

Forms!frmCriteriaForm!cboCommittee

Now, for a word on setting up the lookup field in the table:
http://www.mvps.org/access/lookupfields.htm


--
Wayne Morgan
Microsoft Access MVP


What I have is a tbl-Committees. In the tbl there is a column named
committees that is a lookup field from the tbl-committee.
What I need to accomplish:
My query now pulls up the committee I need, the year and list the members
in
that committee

What I have that does this:
In the query, in the committees criteria I have [Name of the committee:]
when query is run, it pops up a box and I type in the committee name.
In the Year criteria I have [Year:]

This works great for me because I know the names of the committees and
know
the "spelling" of them. (they are not all correctly spelled or have very
strange abbreviations because of the way the information use to be kept)

What I would like to do:
Is for the criteria to open a pull down menu or a scrolled menu to where
the
person can click and say ok, the year can stay the way I have it.
My thoughts is I would need to do a form/macro of sorts. But I have no
clue
where to start.
I would really appreciate the help!

If I need to ask this in a different newsgroup where I could get better
answers, please let me know.
Thanks in advance.
I am running Access 2002 SP3
Rebecca S.

 

[Wayne Morgan]

The query won't open the form, the form will have to already be open and the
selection made before running the query. Normally, you won't let your users
see the query anyway, this would normally be handled by opening a report. In
the report's Open event you would add code that would open the form as a
"pop-up", which will pause the report from opening while you make your
selections. You would then hide the report by setting its Visible property
to False. This will allow the report to continue opening. The report would
use the query as its record source and since the form is already open (even
though it is hidden, it is still open) and the selections have been made the
query will be able to use them as criteria.

Your first guess was correct, I was referring to the criteria in the query
that you already have set up.

--
Wayne Morgan
Microsoft Access MVP

I am having no luck.

After making a form cbo_committee, I put the form in my queries criteria.
When I run the query, it opens the query with the column "committee" as a
pull down menu. It does pull open the list but it won't let me choose from
the list. The cbo_committee form does not even open.

Rebecca S.

One other thing:

You said:

As an example, if the combobox on the form was called cboCommittee, the
criteria would be changed to

Forms!frmCriteriaForm!cboCommittee

Are you referring to the criteria in the query I already have set up? I
would use this statement and it would open my new combobox committee form
where the person then would use the combo box to choose the committee, type
in the date and it would then give me the report I need?

Thanks once again!
Rebecca S.

You are correct, you can do this on a form using a combobox. I just want
to
make sure that you have 2 tables in what you mentioned, one that

ends
with

an "s" and one that doesn't. What field(s) are in each table?

On the form you would place a combobox with its Row Source set to the
information from tbl-committee. You could also place a textbox for the
user
to enter the year. The criteria in the query would be changed to

point
to

the controls on the form for their values. The form has to be open

for
the

query to see it.

As an example, if the combobox on the form was called cboCommittee, the
criteria would be changed to

Forms!frmCriteriaForm!cboCommittee

Now, for a word on setting up the lookup field in the table:
http://www.mvps.org/access/lookupfields.htm


--
Wayne Morgan
Microsoft Access MVP


What I have is a tbl-Committees. In the tbl there is a column named
committees that is a lookup field from the tbl-committee.
What I need to accomplish:
My query now pulls up the committee I need, the year and list the
members
in
that committee

What I have that does this:
In the query, in the committees criteria I have [Name of the committee:]
when query is run, it pops up a box and I type in the committee name.
In the Year criteria I have [Year:]

This works great for me because I know the names of the committees and
know
the "spelling" of them. (they are not all correctly spelled or have
very
strange abbreviations because of the way the information use to be kept)

What I would like to do:
Is for the criteria to open a pull down menu or a scrolled menu to where
the
person can click and say ok, the year can stay the way I have it.
My thoughts is I would need to do a form/macro of sorts. But I

have
no

clue
where to start.
I would really appreciate the help!

If I need to ask this in a different newsgroup where I could get better
answers, please let me know.
Thanks in advance.
I am running Access 2002 SP3
Rebecca S.