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]

Re: Help with Queries

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!frmMyPopu p!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" <sp014a7590@blu eyonder.co.uk> wrote in message
news:lgIWc.2319 99$28.165581@fe 1.news.blueyond er.co.uk...[color=blue]
> 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.
>
>[/color]

[Steve Patrick]

Re: Help with Queries

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" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:BJIWc.7160 $FV3.1852@newss vr17.news.prodi gy.com...[color=blue]
> There are several ways to do this. You can filter the report or use[/color]
criteria[color=blue]
> 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[/color]
you[color=blue]
> 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,[/color]
fill[color=blue]
> 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[/color]
field[color=blue]
> to the combo box on the form. (i.e. Forms!frmMyPopu p!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[/color]
in[color=blue]
> 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" <sp014a7590@blu eyonder.co.uk> wrote in message
> news:lgIWc.2319 99$28.165581@fe 1.news.blueyond er.co.uk...[color=green]
> > 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[/color][/color]
area[color=blue][color=green]
> > are we opening in?" they input West London for example and this produces[/color][/color]
a[color=blue][color=green]
> > 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.
> >
> >[/color]
>
>[/color]

[Wayne Morgan]

Re: Help with Queries

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" <sp014a7590@blu eyonder.co.uk> wrote in message
news:lHLWc.979$ a66.552@fe2.new s.blueyonder.co .uk...[color=blue]
> 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" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in
> message
> news:BJIWc.7160 $FV3.1852@newss vr17.news.prodi gy.com...[color=green]
>> There are several ways to do this. You can filter the report or use[/color]
> criteria[color=green]
>> 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[/color]
> you[color=green]
>> 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,[/color]
> fill[color=green]
>> 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[/color]
> field[color=green]
>> to the combo box on the form. (i.e. Forms!frmMyPopu p!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[/color]
> in[color=green]
>> 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" <sp014a7590@blu eyonder.co.uk> wrote in message
>> news:lgIWc.2319 99$28.165581@fe 1.news.blueyond er.co.uk...[color=darkred]
>> > 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[/color][/color]
> area[color=green][color=darkred]
>> > are we opening in?" they input West London for example and this
>> > produces[/color][/color]
> a[color=green][color=darkred]
>> > 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.
>> >
>> >[/color]
>>
>>[/color]
>
>[/color]

[Steve Patrick]

Re: Help with Queries

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" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in message
news:msMWc.3088 $Yj6.3003@newss vr31.news.prodi gy.com...[color=blue]
> 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[/color]
sheet[color=blue]
> 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[/color]
a[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
handy[color=blue]
> 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[/color]
the[color=blue]
> 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[/color]
start[color=blue]
> 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" <sp014a7590@blu eyonder.co.uk> wrote in message
> news:lHLWc.979$ a66.552@fe2.new s.blueyonder.co .uk...[color=green]
> > Hi Wayne
> >
> > Thanks for the reply - sorry but I am a complete dope when it comes to[/color][/color]
all[color=blue][color=green]
> > this - the basic designing of a form is fine and whilst I know how to[/color][/color]
use[color=blue][color=green]
> > 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" <comprev_gothro ughthenewsgroup @hotmail.com> wrote in
> > message
> > news:BJIWc.7160 $FV3.1852@newss vr17.news.prodi gy.com...[color=darkred]
> >> There are several ways to do this. You can filter the report or use[/color]
> > criteria[color=darkred]
> >> 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[/color]
> > you[color=darkred]
> >> 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,[/color]
> > fill[color=darkred]
> >> 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[/color][/color][/color]
form[color=blue][color=green][color=darkred]
> >> (Visible = False). If Cancel is selected, close the form.
> >>
> >> 4) In the query feeding the report, set the criteria for the location[/color]
> > field[color=darkred]
> >> to the combo box on the form. (i.e. Forms!frmMyPopu p!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[/color][/color][/color]
code[color=blue][color=green]
> > in[color=darkred]
> >> 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" <sp014a7590@blu eyonder.co.uk> wrote in message
> >> news:lgIWc.2319 99$28.165581@fe 1.news.blueyond er.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,[/color][/color][/color]
editing[color=blue][color=green][color=darkred]
> >> > 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[/color]
> > area[color=darkred]
> >> > are we opening in?" they input West London for example and this
> >> > produces[/color]
> > a[color=darkred]
> >> > report that lists only the applicants that have applied for that[/color][/color][/color]
area.[color=blue][color=green][color=darkred]
> >> >
> >> > Sounds simple - but I have been trying to achieve this for over a[/color][/color][/color]
week[color=blue][color=green][color=darkred]
> >> > now.
> >> >
> >> > Please help.
> >> >
> >> > Steve Patrick.
> >> >
> >> >
> >>
> >>[/color]
> >
> >[/color]
>
>[/color]

[Wayne Morgan]

Re: Help with Queries

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" <sp014a7590@blu eyonder.co.uk> wrote in message
news:7TdXc.1625 1$a66.5569@fe2. news.blueyonder .co.uk...[color=blue]
> 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.[/color]