Filtering a subform table on opening

[Dibbler]

Sorry for the stupid question. This is my first database build using VBA (but I'm intermediate with Excel VBA).



I've had a look on google & here but I'm not sure of the right words to be able to ask the question to find it.

I have a form with a sub form inside it which is several fields from a much larger table.

I can open the form & the data is being displayed, but I want to filter the subtable to only show certain entries on opening. IE those that are "Current" in the "Status" field. Currently the subtable shows all the records.

Private Sub BTN_Current_Click()

DoCmd.OpenForm "Current", , , "Status = 'Current'"


End Sub

My assumption is that I need to add the name of the subform (named "Current - Grid Subform") in front of "Status = 'Current'" but I cant find a code snippet to show me the syntax.

Thanks in advance

[Bob Fitz]

Can you post a copy of your db

[Dibbler]

Thanks Bob,

I had to zip it to upload it due to size restrictions

[Bob Fitz]

Thanks Bob,

I had to zip it to upload it due to size restrictions

I don't see it
EDIT:

OK I was expecting to see it in you last post.

[Bob Fitz]

Set the Filter property of the subform to:
[Status]="Current"

Either set the Filter On Load property to: Yes
or use a button to set the Filter On property to: True
with the following code in the On Click event of the button:

Me.[Current - Grid Subform].Form.FilterOn = True

BTW
Do not use spaces or special keys in the names of objects in Access

[Dibbler]

Thanks I'll have a play.

Also, thanks for the heads up with the spaces, in the real version there are no spaces, but I appreciate the fact that you mentioned it. I have no idea why I included them when I renamed things for this mock up... Stupidity probably.

[ArviLaanemets]

Or simply on main form have an unbound hidden textbox, , set it's value to value you use for "Current" in your subform (the string "Current", or smallint value you use for this), and link your subform with this texbox in addition to original link between main- and subform. You also can use combo instead of textbox - then you can switch the subform between different statuses to be displayed. But there is a downside too - your subform will always display data filtered by one status value. And any new records in subform are getting this status by default.

[Dibbler]

I may have marked it soved too early.

I've added [Status]="Current" to filter & then set filter to On in properties on the sub form. That made no difference to what it showed.

Adding Me.[Current - Grid Subform].Form.FilterOn = True to the Click event for the form gives me an error "Runtime Error '2465': cant find the field referred to in your expression.

That implies to me that it isnt working because it isnt resolving the name somehow.

[Welshgasman]

I don't see it
EDIT:

OK I was expecting to see it in you last post.

Yeh, I hate it when they do that. expecially when it is a long post.

I would go with suggestion in post #7 ?
Otherwise you could pass the status as OpenArgs and then set the filter that way.
However you are setting the filter of the SUBFORM, not the MAINFORM

[Bob Fitz]

Adding Me.[Current - Grid Subform].Form.FilterOn = True to the Click event for the form gives me an error "Runtime Error '2465': cant find the field referred to in your expression.

That code belongs in the On Click event of the button not the form

[Welshgasman]

What does the Quote form look like?
If you changed the Quoted button caption to Quote, you could just pass the caption name to the main form and set that hidden control on the mainform.
With the subform linked to that control and status in subform, that would be all you need?

[Bob Fitz]

Dibbler

If you use a combo rather than a button you could filter by any value that you have in the Status field. See attached file: