Open Access Form with Filter VBA

Question

I'm having trouble opening a form using a filter. The code that I am trying to run is this:

DoCmd.OpenForm "MyForm", , "[ID] = " & Me.MyListBox.Column(0), , acFormEdit, acDialog

When I run this line, it opens the form, but does not apply the filter.

The FilterOnLoad property is set to True, and I have verified that it is true in the On Load event.

While in the Form_Load event, Me.FilterOn = False and Me.Filter = "".

What happened to the filter? What am I doing wrong? I've tried setting the Me.FilterOn property to True via VBA and then saving the form, but when I open the form again, it is reset to False.

If I put the filter in using VBA and then set the Me.FilterOn property to True, the form correctly filters. I have verified that the correct value is there for "Me.MyListBox.Column(0)"

HansUp · Accepted Answer · 2015-03-17 18:50:09Z

3

Here is what the DoCmd.OpenForm Method help topic has to say about the FilterName parameter:

A string expression that's the valid name of a query in the current database.

But you're not giving it the name of a query. I think you actually want the WhereCondition parameter instead:

DoCmd.OpenForm FormName:="MyForm", _
    WhereCondition:="[ID] = " & Me.MyListBox.Column(0), _
    DataMode:=acFormEdit, _
    WindowMode:=acDialog

edited Mar 17, 2015 at 18:50

answered Mar 17, 2015 at 18:44

HansUp

97.3k11 gold badges80 silver badges139 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

OpiesDad Over a year ago

Thanks! I saw that in the documentation, but then when I set the filter equal to the same string I tried to pass using openform it worked, so I thought that maybe there was something else I needed to do to get it to work. It's been a long day....

HansUp Over a year ago

Yeah, I investigated further and I think the situation is misleading. When you give OpenForm a string value for FilterName which does not match the name of a saved query, Access doesn't complain --- it just proceeds to open the form same as if you'd not supplied a FilterName in the first place. Seems to me it should alert you that it can't find the query. But I really never use FilterName; WhereCondition is the way to go. ;-)

Collectives™ on Stack Overflow

Open Access Form with Filter VBA

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related