how do i create a drop down menu with a list of queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi:

I am creating a Supplier scorecard database which tracks the monthly scores
for each Supplier. I would like to create a drop down menu in the Main
Switchboard which will allow the user to choose a Supplier out of the
Supplier list. Once the Supplier of choice is chosen, I would like to be able
to have the user click a run command that will show the Monthly Scorecard for
that Supplier.

I can only get as far as creating the Scorecard form but I don't want to
have to run 100 queries for all 100 Suppliers. Any suggestions? I do not know
SQL so please keep the answer simple.
 
Presumably you know how to create a query that returns only the data for a
specific supplier, and this takes the form of putting something that
identifies the supplier in the WHERE clause of the query.

Have your query refer to the combo box where the user selects the supplier
(you refer to it as Forms!FormName!ComboBoxName), rather than hard-coding
the supplier information.
 
O.k. here is what I did:

I created a query called "score" which shows everything on my master table
for all Suppliers.

I then created a form called "scorecard" which shows all the info for all
Suppliers.

In the Swtichboard, I created a combo box which picks from the "score" query
just the field for Supplier name. I named the combo box "Supplier". This
shows a list of all the Suppliers.

I went back into the "score" query and did a group by selection. Under the
Supplier Name column, I chose "Where" and in the criteria, I typed
[Forms]![scorecard]![supplier].

When I go to choose a Supplier from my combo box on the switchboard, I get
prompted to enter parameter values.

What did I do wrong? Please help.
 
It's not clear to me from your description whether the form "scorecard" is
open when you're opening the report. Since you're referencing it in the
query, it must be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


David said:
O.k. here is what I did:

I created a query called "score" which shows everything on my master table
for all Suppliers.

I then created a form called "scorecard" which shows all the info for all
Suppliers.

In the Swtichboard, I created a combo box which picks from the "score"
query
just the field for Supplier name. I named the combo box "Supplier". This
shows a list of all the Suppliers.

I went back into the "score" query and did a group by selection. Under the
Supplier Name column, I chose "Where" and in the criteria, I typed
[Forms]![scorecard]![supplier].

When I go to choose a Supplier from my combo box on the switchboard, I get
prompted to enter parameter values.

What did I do wrong? Please help.

Douglas J. Steele said:
Presumably you know how to create a query that returns only the data for
a
specific supplier, and this takes the form of putting something that
identifies the supplier in the WHERE clause of the query.

Have your query refer to the combo box where the user selects the
supplier
(you refer to it as Forms!FormName!ComboBoxName), rather than hard-coding
the supplier information.
Click to expand...
Click to expand...
 
I want to open the form "scorecard" from the drop down menu in the main
switchboard. Basically, when I choose the Supplier Name from the drop down
menu, Access should be able to filter all the records in my "score query" and
show me info for just that Supplier in the format as form "Scorecard".

Does this make sense?

Douglas J. Steele said:
It's not clear to me from your description whether the form "scorecard" is
open when you're opening the report. Since you're referencing it in the
query, it must be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


David said:
O.k. here is what I did:

I created a query called "score" which shows everything on my master table
for all Suppliers.

I then created a form called "scorecard" which shows all the info for all
Suppliers.

In the Swtichboard, I created a combo box which picks from the "score"
query
just the field for Supplier name. I named the combo box "Supplier". This
shows a list of all the Suppliers.

I went back into the "score" query and did a group by selection. Under the
Supplier Name column, I chose "Where" and in the criteria, I typed
[Forms]![scorecard]![supplier].

When I go to choose a Supplier from my combo box on the switchboard, I get
prompted to enter parameter values.

What did I do wrong? Please help.

Douglas J. Steele said:
Presumably you know how to create a query that returns only the data for
a
specific supplier, and this takes the form of putting something that
identifies the supplier in the WHERE clause of the query.

Have your query refer to the combo box where the user selects the
supplier
(you refer to it as Forms!FormName!ComboBoxName), rather than hard-coding
the supplier information.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi:

I am creating a Supplier scorecard database which tracks the monthly
scores
for each Supplier. I would like to create a drop down menu in the Main
Switchboard which will allow the user to choose a Supplier out of the
Supplier list. Once the Supplier of choice is chosen, I would like to
be
able
to have the user click a run command that will show the Monthly
Scorecard
for
that Supplier.

I can only get as far as creating the Scorecard form but I don't want
to
have to run 100 queries for all 100 Suppliers. Any suggestions? I do
not
know
SQL so please keep the answer simple.
Click to expand...
Click to expand...
Click to expand...
 
Sorry, that doesn't clarify it at all for me.

If you're opening the form "scorecard" from the main switchboard, then combo
box "Supplier" must exist on the main switchboard, not on form "scorecard".
Put the name of the switchboard instead of "scorecard" in
[Forms]![scorecard]![supplier].

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


David said:
I want to open the form "scorecard" from the drop down menu in the main
switchboard. Basically, when I choose the Supplier Name from the drop down
menu, Access should be able to filter all the records in my "score query"
and
show me info for just that Supplier in the format as form "Scorecard".

Does this make sense?

Douglas J. Steele said:
It's not clear to me from your description whether the form "scorecard"
is
open when you're opening the report. Since you're referencing it in the
query, it must be.

--
Doug Steele, Microsoft Access MVP

(no private e-mails, please)


David said:
O.k. here is what I did:

I created a query called "score" which shows everything on my master
table
for all Suppliers.

I then created a form called "scorecard" which shows all the info for
all
Suppliers.

In the Swtichboard, I created a combo box which picks from the "score"
query
just the field for Supplier name. I named the combo box "Supplier".
This
shows a list of all the Suppliers.

I went back into the "score" query and did a group by selection. Under
the
Supplier Name column, I chose "Where" and in the criteria, I typed
[Forms]![scorecard]![supplier].

When I go to choose a Supplier from my combo box on the switchboard, I
get
prompted to enter parameter values.

What did I do wrong? Please help.

:

Presumably you know how to create a query that returns only the data
for
a
specific supplier, and this takes the form of putting something that
identifies the supplier in the WHERE clause of the query.

Have your query refer to the combo box where the user selects the
supplier
(you refer to it as Forms!FormName!ComboBoxName), rather than
hard-coding
the supplier information.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)


Hi:

I am creating a Supplier scorecard database which tracks the monthly
scores
for each Supplier. I would like to create a drop down menu in the
Main
Switchboard which will allow the user to choose a Supplier out of
the
Supplier list. Once the Supplier of choice is chosen, I would like
to
be
able
to have the user click a run command that will show the Monthly
Scorecard
for
that Supplier.

I can only get as far as creating the Scorecard form but I don't
want
to
have to run 100 queries for all 100 Suppliers. Any suggestions? I do
not
know
SQL so please keep the answer simple.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top