Filtering a Form with vba

[Jan G. Thorstensen]

Hi. I am a newbie using Access 2000 and windows98.

I would like to know how to write code to filter my Form.

In my Example I have one table and one Form with 4 Text Boxes:

First Name
Last Name
Address
City

I want to have one additional unbound Control, a Combo Box, loaded
with all the City Names in my Table. So far - no problem.
When I chose a city from the Combo Box, my code should make a filter
in my Form.

Can somebody help me with the code to do this?

Any help is very much appriciated, thanks!

Jan

 

[MacDermott]

While this can be coded, Access also has a built-in FilterByForm option in
the toolbar.
Click on it, and you will see a view of your form with all available options
in drop-downs in each control.
You can select the city you want, then click on the Filter button (also in
the toolbar) to see your form filtered by that selection.

Why re-invent the wheel?

- Turtle

 

[Peter Kaufman]

Hi Jan,

A few things to watch here. Ideally the combo box will be
multi-column, the first one being hidden and holding the primary key
field, an integer, from your table, and the next being the city name
in text. If you just have one text field, then the syntax must be
adjusted.

The column count is zero based, so the first column is number zero.

Also, I code every single procedure in my applications with error
handling but to keep things simple, try this.

'After update event of your combo box
Private Sub City_AfterUpdate()
'one line...
Me.RecordSource = "Select * from Mytable where City = " &
Me.Mycombobox.Column(0)
End Sub

HTH,

Peter