Help with Queries

[Steve Patrick]

Hi All

You guys are my last hope, despite spending money on books and hours reading
them I still can not achieve the results I need.

I have designed a database in Access 2000 based on 1 table, all has gone
very well with one exception. The table is based on applications made by
potential customers looking to buy franchise rights to particular locations
and as part of the process they are asked to list their preferred locations
1 to 4. The table I have designed and forms for inputting info, editing info
are fine. I have even managed to design a form that lists applicants and
their preferred areas 1 - 4.

However I would like to design a process that says to the user "What area
are we opening in?" they input West London for example and this produces a
report that lists only the applicants that have applied for that area.

Sounds simple - but I have been trying to achieve this for over a week now.

Please help.

Steve Patrick.

[Wayne Morgan]

There are several ways to do this. You can filter the report or use criteria
in the query feeding the report. Using the criteria, you can place the
criteria in the query as a parameter and let the query prompt for it or you
could use a pop-up form to prompt the user and let the query get the value
of the parameter from the form. The nice thing about the form is you could
use a combo box to let the user make their selection instead of relying on
them typing it in properly.

1) Create a form with a combo box. For the Row Source of the combo box, fill
in the locations by a value list or query.

2) Place two buttons on the form, Ok and Cancel.

3) If Ok is clicked, verify a value has been selected then hide the form
(Visible = False). If Cancel is selected, close the form.

4) In the query feeding the report, set the criteria for the location field
to the combo box on the form. (i.e. Forms!frmMyPopup!cboMyCombo)

5) In the Open event of the report, open the pop-up form with the acDialog
window mode argument in the DoCmd.OpenForm call. This will cause the code in
the report to pause until the pop-up form is closed or hidden. In the next
line, check to see if the form is open, if it isn't then the user chose
Cancel and you can also cancel the opening of the report.

6) Close the pop-up form when you close the report.

--
Wayne Morgan
Microsoft Access MVP
"Steve Patrick" <sp********@blueyonder.co.uk> wrote in message
news:lg********************@fe1.news.blueyonder.co .uk...

Hi All

You guys are my last hope, despite spending money on books and hours
reading
them I still can not achieve the results I need.

I have designed a database in Access 2000 based on 1 table, all has gone
very well with one exception. The table is based on applications made by
potential customers looking to buy franchise rights to particular
locations
and as part of the process they are asked to list their preferred
locations
1 to 4. The table I have designed and forms for inputting info, editing
info
are fine. I have even managed to design a form that lists applicants and
their preferred areas 1 - 4.

However I would like to design a process that says to the user "What area
are we opening in?" they input West London for example and this produces a
report that lists only the applicants that have applied for that area.

Sounds simple - but I have been trying to achieve this for over a week
now.

Please help.

Steve Patrick.

[Steve Patrick]

Hi Wayne

Thanks for the reply - sorry but I am a complete dope when it comes to all
this - the basic designing of a form is fine and whilst I know how to use
the preferences to find some things I am at a loss to know how to create the
query or "fill in locations".

Any chance you could simplify things as much as possible for me?

All / any help gratefully received.

Steve
"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:BJ*****************@newssvr17.news.prodigy.co m...

There are several ways to do this. You can filter the report or use criteria in the query feeding the report. Using the criteria, you can place the
criteria in the query as a parameter and let the query prompt for it or you could use a pop-up form to prompt the user and let the query get the value
of the parameter from the form. The nice thing about the form is you could
use a combo box to let the user make their selection instead of relying on
them typing it in properly.

1) Create a form with a combo box. For the Row Source of the combo box, fill in the locations by a value list or query.

2) Place two buttons on the form, Ok and Cancel.

3) If Ok is clicked, verify a value has been selected then hide the form
(Visible = False). If Cancel is selected, close the form.

4) In the query feeding the report, set the criteria for the location field to the combo box on the form. (i.e. Forms!frmMyPopup!cboMyCombo)

5) In the Open event of the report, open the pop-up form with the acDialog
window mode argument in the DoCmd.OpenForm call. This will cause the code in the report to pause until the pop-up form is closed or hidden. In the next
line, check to see if the form is open, if it isn't then the user chose
Cancel and you can also cancel the opening of the report.

6) Close the pop-up form when you close the report.

--
Wayne Morgan
Microsoft Access MVP
"Steve Patrick" <sp********@blueyonder.co.uk> wrote in message
news:lg********************@fe1.news.blueyonder.co .uk...

Hi All

You guys are my last hope, despite spending money on books and hours
reading
them I still can not achieve the results I need.

I have designed a database in Access 2000 based on 1 table, all has gone
very well with one exception. The table is based on applications made by
potential customers looking to buy franchise rights to particular
locations
and as part of the process they are asked to list their preferred
locations
1 to 4. The table I have designed and forms for inputting info, editing
info
are fine. I have even managed to design a form that lists applicants and
their preferred areas 1 - 4.

However I would like to design a process that says to the user "What area are we opening in?" they input West London for example and this produces a report that lists only the applicants that have applied for that area.

Sounds simple - but I have been trying to achieve this for over a week
now.

Please help.

Steve Patrick.

[Wayne Morgan]

Ok, the Row Source of a combo box is what fills in the items in the drop
down list that you then select from. On the Data tab of the Properties sheet
for the combo box you will find two options, "Row Source Type" and "Row
Source". Set the type to the type of item you are using to "fill" the row
source. Value List will simply let you type a list of items into the row
source separated by semi-colons. Table/Query will let you put the name of a
table or query into the Row Source or you could type the "SQL view" of a
query directly into the box. Field list will cause the combo box to give the
names of fields in a table or query rather than the values of the fields.

A query (on the query tab of the database window) will let you connect
multiple tables together to link together correlated data in each of the
tables and display it as if it was in a single table. It also comes in handy
for things such as this when you need only a single table because it also
has sorting capability. This will allow you to alphabetize the items in the
drop down list.

You will also want to set the "Limit To List" property of the combo box to
Yes. This will force the users to only use items that are in the list.

You may want to check out the Northwind Traders sample database that comes
with Access and see how they did things. When you see something that works
similar to what you're wanting, go into design view of the object and start
looking around. You may also want to try a couple of books. "Microsoft
Office Access 2003 Inside Out" would probably be a good choice.
http://www.viescas.com/Info/books.htm#Access

--
Wayne Morgan
Microsoft Access MVP
"Steve Patrick" <sp********@blueyonder.co.uk> wrote in message
news:lH***************@fe2.news.blueyonder.co.uk.. .

Hi Wayne

Thanks for the reply - sorry but I am a complete dope when it comes to all
this - the basic designing of a form is fine and whilst I know how to use
the preferences to find some things I am at a loss to know how to create
the
query or "fill in locations".

Any chance you could simplify things as much as possible for me?

All / any help gratefully received.

Steve
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:BJ*****************@newssvr17.news.prodigy.co m...

There are several ways to do this. You can filter the report or use

criteria

in the query feeding the report. Using the criteria, you can place the
criteria in the query as a parameter and let the query prompt for it or

you

could use a pop-up form to prompt the user and let the query get the
value
of the parameter from the form. The nice thing about the form is you
could
use a combo box to let the user make their selection instead of relying
on
them typing it in properly.

1) Create a form with a combo box. For the Row Source of the combo box,

fill

in the locations by a value list or query.

2) Place two buttons on the form, Ok and Cancel.

3) If Ok is clicked, verify a value has been selected then hide the form
(Visible = False). If Cancel is selected, close the form.

4) In the query feeding the report, set the criteria for the location

field

to the combo box on the form. (i.e. Forms!frmMyPopup!cboMyCombo)

5) In the Open event of the report, open the pop-up form with the
acDialog
window mode argument in the DoCmd.OpenForm call. This will cause the code

in

the report to pause until the pop-up form is closed or hidden. In the
next
line, check to see if the form is open, if it isn't then the user chose
Cancel and you can also cancel the opening of the report.

6) Close the pop-up form when you close the report.

--
Wayne Morgan
Microsoft Access MVP
"Steve Patrick" <sp********@blueyonder.co.uk> wrote in message
news:lg********************@fe1.news.blueyonder.co .uk...

> Hi All
>
> You guys are my last hope, despite spending money on books and hours
> reading
> them I still can not achieve the results I need.
>
> I have designed a database in Access 2000 based on 1 table, all has
> gone
> very well with one exception. The table is based on applications made
> by
> potential customers looking to buy franchise rights to particular
> locations
> and as part of the process they are asked to list their preferred
> locations
> 1 to 4. The table I have designed and forms for inputting info, editing
> info
> are fine. I have even managed to design a form that lists applicants
> and
> their preferred areas 1 - 4.
>
> However I would like to design a process that says to the user "What area > are we opening in?" they input West London for example and this
> produces a > report that lists only the applicants that have applied for that area.
>
> Sounds simple - but I have been trying to achieve this for over a week
> now.
>
> Please help.
>
> Steve Patrick.
>
>

[Steve Patrick]

Hi Wayne

Thanks for the further help.

I have managed the combo box with the locations listed in the drop down
menu. I have also managed the 2 buttons, the exit button (I guess the easy
one) works fine. However I need some help in how to make the find button do
things:

1. to look at the 4 columns in the contacts table (I am only using 1 table)
and find matches to the locations listed in those columns.

&

2. How to the product that information coupled with the candidates details
on a report.

As always any / all help much appreciated.

Steve

"Wayne Morgan" <co***************************@hotmail.com> wrote in message
news:ms*****************@newssvr31.news.prodigy.co m...

Ok, the Row Source of a combo box is what fills in the items in the drop
down list that you then select from. On the Data tab of the Properties sheet for the combo box you will find two options, "Row Source Type" and "Row
Source". Set the type to the type of item you are using to "fill" the row
source. Value List will simply let you type a list of items into the row
source separated by semi-colons. Table/Query will let you put the name of a table or query into the Row Source or you could type the "SQL view" of a
query directly into the box. Field list will cause the combo box to give the names of fields in a table or query rather than the values of the fields.

A query (on the query tab of the database window) will let you connect
multiple tables together to link together correlated data in each of the
tables and display it as if it was in a single table. It also comes in handy for things such as this when you need only a single table because it also
has sorting capability. This will allow you to alphabetize the items in the drop down list.

You will also want to set the "Limit To List" property of the combo box to
Yes. This will force the users to only use items that are in the list.

You may want to check out the Northwind Traders sample database that comes
with Access and see how they did things. When you see something that works
similar to what you're wanting, go into design view of the object and start looking around. You may also want to try a couple of books. "Microsoft
Office Access 2003 Inside Out" would probably be a good choice.
http://www.viescas.com/Info/books.htm#Access

--
Wayne Morgan
Microsoft Access MVP
"Steve Patrick" <sp********@blueyonder.co.uk> wrote in message
news:lH***************@fe2.news.blueyonder.co.uk.. .

Hi Wayne

Thanks for the reply - sorry but I am a complete dope when it comes to all this - the basic designing of a form is fine and whilst I know how to use the preferences to find some things I am at a loss to know how to create
the
query or "fill in locations".

Any chance you could simplify things as much as possible for me?

All / any help gratefully received.

Steve
"Wayne Morgan" <co***************************@hotmail.com> wrote in
message
news:BJ*****************@newssvr17.news.prodigy.co m...

There are several ways to do this. You can filter the report or use

criteria

in the query feeding the report. Using the criteria, you can place the
criteria in the query as a parameter and let the query prompt for it or

you

could use a pop-up form to prompt the user and let the query get the
value
of the parameter from the form. The nice thing about the form is you
could
use a combo box to let the user make their selection instead of relying
on
them typing it in properly.

1) Create a form with a combo box. For the Row Source of the combo box,

fill

in the locations by a value list or query.

2) Place two buttons on the form, Ok and Cancel.

3) If Ok is clicked, verify a value has been selected then hide the form (Visible = False). If Cancel is selected, close the form.

4) In the query feeding the report, set the criteria for the location

field

to the combo box on the form. (i.e. Forms!frmMyPopup!cboMyCombo)

5) In the Open event of the report, open the pop-up form with the
acDialog
window mode argument in the DoCmd.OpenForm call. This will cause the code

in

the report to pause until the pop-up form is closed or hidden. In the
next
line, check to see if the form is open, if it isn't then the user chose
Cancel and you can also cancel the opening of the report.

6) Close the pop-up form when you close the report.

--
Wayne Morgan
Microsoft Access MVP
"Steve Patrick" <sp********@blueyonder.co.uk> wrote in message
news:lg********************@fe1.news.blueyonder.co .uk...
> Hi All
>
> You guys are my last hope, despite spending money on books and hours
> reading
> them I still can not achieve the results I need.
>
> I have designed a database in Access 2000 based on 1 table, all has
> gone
> very well with one exception. The table is based on applications made
> by
> potential customers looking to buy franchise rights to particular
> locations
> and as part of the process they are asked to list their preferred
> locations
> 1 to 4. The table I have designed and forms for inputting info,

editing > info
> are fine. I have even managed to design a form that lists applicants
> and
> their preferred areas 1 - 4.
>
> However I would like to design a process that says to the user "What

area

> are we opening in?" they input West London for example and this
> produces

a

> report that lists only the applicants that have applied for that area. >
> Sounds simple - but I have been trying to achieve this for over a week > now.
>
> Please help.
>
> Steve Patrick.
>
>

[Wayne Morgan]

I don't fully understand what you're after by the way it is written. The
"Find" button should only need to hide the form (Me.Visible = False) and
perhaps verify that a selection has been made in one of the combo boxes.
When the form is hidden, the code in the Open event of the report will
resume running. When the report draws its data from the query, the query
refers to the selected values as its criteria and filters the results based
on that. You can also add an "All" option to the combo box if you wish. An
example of doing that can be found here.

http://www.mvps.org/access/forms/frm0043.htm

#2 I don't understand at all.

--
Wayne Morgan
Microsoft Access MVP
"Steve Patrick" <sp********@blueyonder.co.uk> wrote in message
news:7T******************@fe2.news.blueyonder.co.u k...

Hi Wayne

Thanks for the further help.

I have managed the combo box with the locations listed in the drop down
menu. I have also managed the 2 buttons, the exit button (I guess the easy
one) works fine. However I need some help in how to make the find button
do
things:

1. to look at the 4 columns in the contacts table (I am only using 1
table)
and find matches to the locations listed in those columns.

&

2. How to the product that information coupled with the candidates details
on a report.