filtering table based on user input

[Brendan MAther]

I would like to allow the user to filter a list of contacts based on what
city the contacts are in. The user would input the city name into a text
box click a button and the new table would open with all the contacts from
that city. Can anyone help me out with this?

thanks

 

[Steve Schapel]

Brendan,

Here's one approach...
1. Put an unbound combobox on a form, call it CitySelect
2. Set the RowSource property of the combobox to:
SELECT DISTINCT City FROM Contacts ORDER BY City
(This assumes your table is called Contacts and the city data is in a
field called City - adjust as necessary)
3. Make a query based on your Contacts table, and in the criteria of
the City field in the query, put the equivalent of:
Nz([Forms]!NameOfForm]![CitySelect],"*")
4. Make a form based on this query
5. Use a macro or code on the command button on the first form, to
open the second form.
6. Select the city in the combobox, click the button, and all
contacts from the selected city shoud then be shown on the second
form.

- Steve Schapel, Microsoft Access MVP

 

[Brendan MAther]

Thanks, that was very helpful.
Is it possible to make it so that the user cannot type anything into that
field. As soon as any character is typed into this box the form with the
previous city will come up

Thanks,
Brendan

Brendan,

Here's one approach...
1. Put an unbound combobox on a form, call it CitySelect
2. Set the RowSource property of the combobox to:
SELECT DISTINCT City FROM Contacts ORDER BY City
(This assumes your table is called Contacts and the city data is in a
field called City - adjust as necessary)
3. Make a query based on your Contacts table, and in the criteria of
the City field in the query, put the equivalent of:
Nz([Forms]!NameOfForm]![CitySelect],"*")
4. Make a form based on this query
5. Use a macro or code on the command button on the first form, to
open the second form.
6. Select the city in the combobox, click the button, and all
contacts from the selected city shoud then be shown on the second
form.

- Steve Schapel, Microsoft Access MVP

I would like to allow the user to filter a list of contacts based on what
city the contacts are in. The user would input the city name into a text
box click a button and the new table would open with all the contacts from
that city. Can anyone help me out with this?

thanks

 

[Steve Schapel]

Brendan,

The4 only way I can think of at the moment, without using VBA, is to
have an unbound textbox on the form, suppose it is called CityText,
invisible possibly, and every time the user makes a selection in the
combobox, the selected city is entered into the textbox using a
SetValue macro action, with arguments:
Item: [CityText]
Expression: [CitySelect]
Then, you would use the On Key Press event of the combobox to run a
macro which opens the form with the records according to the city in
the textbox. Hope that makes sense.

- Steve Schapel, Microsoft Access MVP

Thanks, that was very helpful.
Is it possible to make it so that the user cannot type anything into that
field. As soon as any character is typed into this box the form with the
previous city will come up

Thanks,
Brendan

Brendan,

Here's one approach...
1. Put an unbound combobox on a form, call it CitySelect
2. Set the RowSource property of the combobox to:
SELECT DISTINCT City FROM Contacts ORDER BY City
(This assumes your table is called Contacts and the city data is in a
field called City - adjust as necessary)
3. Make a query based on your Contacts table, and in the criteria of
the City field in the query, put the equivalent of:
Nz([Forms]!NameOfForm]![CitySelect],"*")
4. Make a form based on this query
5. Use a macro or code on the command button on the first form, to
open the second form.
6. Select the city in the combobox, click the button, and all
contacts from the selected city shoud then be shown on the second
form.

- Steve Schapel, Microsoft Access MVP

I would like to allow the user to filter a list of contacts based on what
city the contacts are in. The user would input the city name into a text
box click a button and the new table would open with all the contacts from
that city. Can anyone help me out with this?

thanks