Use of form to gather data for a query

[tarhim47]

Hi,

Scenario:

I'm working on a database which holds numerous data about a number of clients.

After the data has been entered, I want to be able to use parameters and only return information about those client which meet the parameters.

Progress so far:

All the data is currently in a table.

I have made a query which has three different parameters.



I have created an unbound form to gather data (from the user) and I have tied that form to a report. Everything works fine right now.

Problem:

The problem I'm having is that the form is only able to return information about clients when I use one value for a parameter.

For example, if I want the form to return all clients which reside in ABC city, all I have to do is type in ABC in the text box in the form and the form will return all clients which reside in that city. However when I convert the city textbox to a list box which allows multiselect, the form doesnt return any data.

Ideal solution:

I want to make it so that I can select ABC and DEF in the form and have it return all clients which reside in ABC and DEF.

Notes:

I have been trying this for hours now and cant get it...maybe I'm missing something.

Also, I'm somewhat limited using VB codes, everything Ive created so far, Ive done using Access 07 tools provided in the tabs.

Any help is greatly appreciated and thank you in advance for all your efforts.

[pbaldy]

A multiselect listbox always has a Null value, no matter what is selected. Here's what I usually do:

http://www.baldyweb.com/multiselect.htm

[tarhim47]

Hi Paul,

Thanks for your reply.

The example you provided is great but its difficult for me to implement. This is because the list box derives its values from a table and I cant do that. Thats because the form Ive creted will be asking for a range and not a singular value found in the table.

For example, the table will include on date - Nov 4/10. However, the form will be asking for a starting date and an ending date. These two text boxes cant be referenced to any particular field in a table because the table has only one date.

Any other ideas?

Thanks!

[pbaldy]

I guess I was confused by this:

However when I convert the city textbox to a list box which allows multiselect, the form doesnt return any data

What is it you're selecting from the listbox?

[tarhim47]

I'm trying to select the client's city of residence. I want to be able to select multiple cities so that the form will return the information about all the clients which reside in the selected cities.

I can use a list box for this or a combo box whichever allows me to input a list from which the user can select the desired cities.

Does this answer your question?

[pbaldy]

It sounds like you want to be able to select multiple items from a listbox to filter a form/report, which is what that code does.

[tarhim47]

You're right! That is exactly what I''m trying to do but the code isnt working.

How can i attach my database? I tried it yesterday and it failed to upload.

[pbaldy]

Generally if you do a compact/repair and then zip, it will upload fine.

[tarhim47]

Hey Paul,

You can find it here...https://www.accessforums.net/forms/file-9120.html

[tarhim47]

Dont worry...all the data is fake.

So the question is how I do make it so that you can select multiple cities in the conty list box and have the form return all the info about all the client within the selected cities?

[pbaldy]

You can take the parameter for county out of your query and adapt the sample VBA code in my link to open the report. To my knowledge, there's no way to get the selections from a multiselect listbox with a macro, only VBA code.