Form like Query

[Annemarie]

I would like to make a form that does many of the same functions as a
query. The idea is to have users be able to choose one or several
selections from a list box, or several list boxes and have it query the
results. I know this is way easier by simply using a query, but the
idea is to not have the users have to interface with the query screen,
to simply choose from a list and see the results based on those
selections. The main idea is user ease.

I have started to play with the idea but only have built a form that
will show a result of choosing one selection from each of three combo
boxes. The results only display if all three have a value selected.
Is there a way to have a 'wild card' that will cause that category to
show all results in the sub form that meet the criteria of the other
fields chosen? Right now it is set up to sort by three different
fields. Say a user wants to see all of the records that relate to a
specific location..they would choose the location and leave the other
two identifying fields blank...or if they wanted to see all of the
records for the ID 123456 at that location, leaving one identifier
blank. or all ID 123456 and 654321 for that location. etc.... I
know this is easy to do within a query but cannot figure out how to
make it work in a form.

Another useful tool would be if a user chooses a location, to have only
those Id's associated with that location be choices in the list box. I
have quite a long list of IDs and Uses, and it would be very helpful to
narrow down the choices.

Any help would be appreciated.

Thanks!

 

[Allen Browne]

Annmarie, you have asked for a lot of different things there, so be prepared
to take it one step at a time.

For starters, you want to know how to build a form where the user can choose
from several different boxes, and you return the matches. There is an
example to download and pull apart here:
Search form - Handle many optional criteria
at:
http://allenbrowne.com/ser-62.html

Now you want to combine that idea with multi-select list boxes. There's an
example of how to loop through the ItemsSelected and build the filter string
here:
Use a multi-select list box to filter a report
at:
http://allenbrowne.com/ser-50.html

Finally, you also want to be able to filter a 2nd combo based on the choice
in an earlier one. See:
Limit content of combo/list boxes
at:
http://www.mvps.org/access/forms/frm0028.htm

Have fun putting it all together.

 

[Steve C, MFS]

Hi, Annemarie

It may not cover everything you are after, but there's some good stuff on
the following site that at least includes how to limit one combo box contents
based on the selection in another. Look for cascading combo boxes. Also
there's lots of other good stuff in there for developing databases.

http://www.rogersaccesslibrary.com/TableOfContents3.asp

Hope it helps.

 

[Annemarie]

Thank you both for your help, I will look through these websites when I
get back to the office this afternoon!

Be prepared I might have more questions later..I am still learning the
whole coding structure, but am usually successful at just manipulating
prebuilt codes to reflect what I need.

Thanks again

Annemarie

 

[Annemarie]

Wow, it works great so far. I am having troubles with the multi select
list box though...i keep getting a message saying there is no criteria.
I have tried to understand the code but am just really lost... The
code on the website is also built for opening a report...I just need to
be able to multi select as the criteria for my query.

I am also having trouble pulling up the "all" option. I just cant
figure it out!

Please help!

Both of those sites have been great!