MS Access Forms Filter  
Author Message
yqureshi80





PostPosted: Visual Basic for Applications (VBA), MS Access Forms Filter Top

Hello Gentlemen,

I am a novice in databases. Right now I am trying to make a small database that can search out result from the table I have already made. My question is can I place command buttons inside the form that have functions like

-Filter By Form

-Apply Filter

in MS Access Toolbars

Please note that I dont want to use the existing toolbar

I hope I will get an amicable responce

With Best Regards

yqureshi80




Microsoft ISV Community Center Forums2  
 
 
duck thing





PostPosted: Visual Basic for Applications (VBA), MS Access Forms Filter Top

You sure can. How to implement it depends on how your form displays the data. Presuming that your form's data is populated with a simple RecordSet opened with an SQL select query, all you should need to do is:

- close the existing recordset (not the form!)

- open a new recordset using the new select query

- refresh the form to show the new RecordSet data

I don't have Access handy at the moment and haven't worked with it in quite a while, so I apologize if this is too vague.



 
 
Derek Smyth





PostPosted: Visual Basic for Applications (VBA), MS Access Forms Filter Top

Yes you can do this, it all depends though on how your form is displaying in the data. I'm thinking your using data bound controls, in which case there are a number of ways to do it.

The easiest way is to apply the filter to the form. If you look at the properties of your form, under the data tag, you will see a Filter property. You need to set this to a filter expression in order to filter the records. A filter expression is like this '[Field] = value'. You also need to specify if the filter is on or off.

For example, I have a form thats bound to a table Customer, the form displays the tables [CustomerID] field. To filter by this field on the form I create a button and place this in its event handler.

Private Sub cmdFilter_Click()
Me.Filter = "[CustomerID] = 'ALFKI'"
Me.FilterOn = True
End Sub

You can change the ALFKI to an variable, be sure that the filter matches the data type of the field, for example CustomerID is a text field and so the filter expression needs to be wrapped in single quotes.