General discussion

Locked

Can I automate Filter by Form in Access?

By jmiller ·
After you have all your information in a table and you have a form setup to view them is their away to have the filter by form automatically start when you first open the form? So you may use the drop down lists to search for what you want? Or a button that starts filter by form, then have an apply filter button?

Is their anyway besides this way to lookup your information automatically besides goin through the records.

Thank you

This conversation is currently closed to new comments.

7 total posts (Page 1 of 1)  
| Thread display: Collapse - | Expand +

All Comments

Collapse -

Can I automate Filter by Form in Access?

by Bill_H In reply to Can I automate Filter by ...

You can open the "Filter By" for by adding the following code to the Load event for the form:

Private Sub Form_Load()
DoCmd.RunCommand acCmdFilterByForm
End Sub

This will first display the "Filter By" form; make your selections and closethe "Filter By" form, and the associated form will be displayed with the filter applied.

Hope this helps...

Collapse -

Can I automate Filter by Form in Access?

by jmiller In reply to Can I automate Filter by ...

Poster rated this answer

Collapse -

Can I automate Filter by Form in Access?

by Bob Sellman In reply to Can I automate Filter by ...

Let's assume you call the form from another form. You could include on the calling form one or more (as needed) controls with drop down lists or whatever to select the values you want as filter options.

If the form is not based on a query that uses these controls as criteria within the query, then you must build the filter when the user clicks the button that opens the form.

If you use strFilter as the variable in your VBA code, once you have built strFilter properly, then open the form with the following statement:

DoCmd.OpenForm "frmFormName",acNormal,strFilter

Make sure you pay attention to surrounding strings (text) values with single quotes and dates with # signs when building your filter string.

If you want the user to be able to select and re-select records while the form is displayed, do the following. I'll use the simple example of the user entering an ID# to select a specific record, but you can use multiple controls and drop down boxes based on the same logic.

Add the control txtSelectedID to the form's footer (or header) area.

For txtSelectID put in the following code for the On Update property:

Dim strFilter as String
If me.txtSelectedID > "0" then
strFilter = "[IDNbr] = " &
Int(me.txtSelectID) '(put this on one line)
me.Filter = strFilter
me.FilterOn = True
me.Recalc '(or: me.Requery)
End If

For this method to work the form property of AllowEdits must be set to True. You can force the form to come showing no records by setting the form's OnOpen property to run the following code (just use a condition which can never be true):

me.FilterOn = True
me.Filter = "[IDNbr] = -1"

If you set up multiple conditions on the form, building the filter can be more complex, especially if not all conditions must have values. But in that case you would have a separate Apply Filter button (as you suggest) that would actually start the fil

Collapse -

Can I automate Filter by Form in Access?

by jmiller In reply to Can I automate Filter by ...

Poster rated this answer

Collapse -

Can I automate Filter by Form in Access?

by donq In reply to Can I automate Filter by ...

Sure: Many samples of this in the better Access books.

If everything comes from one table - place a primary key field on the form and hide it. Then place a combo box in the form header (myCombo) and populate it with a different query linked to primary key values.

In the form query under the primary key's constraints enter [MyCombo].[PrimaryKeyFieldName] That way the query will base its answer from the users selection in the header's combo box. Next set the combo boxes after update property to requery Me.

Collapse -

Can I automate Filter by Form in Access?

by jmiller In reply to Can I automate Filter by ...

Poster rated this answer

Collapse -

Can I automate Filter by Form in Access?

by jmiller In reply to Can I automate Filter by ...

This question was closed by the author

Back to Web Development Forum
7 total posts (Page 1 of 1)  

Related Discussions

Related Forums