Filtering a sub-form from the main form VBA module

[ScubaBart]

I have searched and searched and tried dozens of different syntax to try and get this to work and it simply evades me.



I'm trying to perform a DcCmd.ApplyFilter to a SubForm when I open the MainForm.

Here is what I currently have:

DoCmd.ApplyFilter , "Forms!F_Main_Overview.[Fsub_Log_Apiary_Main SubForm].Form!Apiary_Active =" & -1

I even tried attaching the code to the open event on the SubForm and it didn't work there either.

I hope someone can tell me what the correct syntax is as what I find online simply isn't working.

I have a second filter condition I will apply with this one so expecting that an And condition will work ?


Is there a better way to apply a filter in this circumstance ?? I keep seeing reference to FilterName but have no clue on how to construct a Named Filter.


My problem for this weekend and hopefully someone can help.

[Micron]

Never used that approach so I looked it up. Seems to me that the "filter" has to be a named filter or a query that provides the filtering. You seem to be trying to build the filter in the command. I suggest you bookmark this site, at least at the root of Access. I've got it bookmarked at the top.

https://learn.microsoft.com/en-us/of...md.applyfilter

[Welshgasman]

I would do it the simple way.

Just set the Filter property of the form and the set that FilterOn to True

[Gicu]

I would also use the Filter property of the subform:

Me.Fsub_Log_Apiary_Main SubForm].Form.Filter="Apiary_Active=True" 'Assumes you run this from the main form's event
Me.Fsub_Log_Apiary_Main SubForm].Form.FilterOn=True

https://learn.microsoft.com/en-us/of...lter(property)

Cheers,

[ScubaBart]

I would also use the Filter property of the subform:

Me.Fsub_Log_Apiary_Main SubForm].Form.Filter="Apiary_Active=True" 'Assumes you run this from the main form's event
Me.Fsub_Log_Apiary_Main SubForm].Form.FilterOn=True

Cheers,

Had not tried this approach.

Does not work from the Mainform but FINALLY, it does work when I apply it to the On Open event of the subform. And I was able to add the second condition with no problem.

Seems it should work from the mainform but, at least it's working now. This was painful.

Many Thanks !!!

[Welshgasman]

It will work from the mainform, but YOU have to get the syntax correct.
Forms: Refer to Form and Subform properties and controls

Computers are stupid. You could type something I would likely be able to work out what you are trying to do (well sometimes ), but computers are not that forgiving.

YOU have to be very explicit in your instructions.

Plus that code would NOT work from the subform as it references a subfom control.

Plus there appears to be an errant ] in that code.

[davegri]

I don't think you need the left subform at all. You mainform/subform setup is all wrong. Your main form has no recordsource, so it cannot tell the subforms what to show.
The main form needs its recordsource set to T_Log_Apiary. The left subform currently has that recordsource, so just move the fields from that subform to the main form and remove the subform.
That allows Interim_link to be changed to Log_Apairy_ID.
Now add navigation buttons back to the main form so that you can move from one apiary to the next.

Another big problem is the code that is opening F_Main_Overview when returning from "Add/Edit Apiary Details" and "Add/Edit Hive Details".
Both of those forms, when returning to F_Main_Overview have syntax that opens the form in Form Add mode which means it shows nothing and cannot scroll records.
Why? Well, it's kind of a VBA trap having to do with Access Enumerations. In the form open dialog, acNormal resolves to zero. But so does acFormAdd.
So if you have Docmd.openform "F_Main_Overview",acNormal, "", , , acNormal
That 4th position is going to be zero and IN SOME CASES Access is going to open the form in Form Add mode. I can't identify what causes it to happen in some setup and not others.
My advice is never use acNormal anywhere except the first param, and it usually is not required even there.
In this case simply use DoCmd.OpenForm "F_Main_Overview" in both the forms returning to F_Main_Overview.

This analysis is based on the DB uploaded 9/15/24.

[ScubaBart]

It will work from the mainform, but YOU have to get the syntax correct.
Forms: Refer to Form and Subform properties and controls

Computers are stupid. You could type something I would likely be able to work out what you are trying to do (well sometimes ), but computers are not that forgiving.

YOU have to be very explicit in your instructions.

Plus that code would NOT work from the subform as it references a subfom control.

Plus there appears to be an errant ] in that code.

To work in the subform, I simply shortened it to :

Private Sub Form_Open(Cancel As Integer)

    Filter = "Apiary_Active = -1 And Log_Apiary_ID > 1"
    FilterOn = True
        
End Sub

Though this shows the 2nc condition being added.

I don't think the ] was errant but rather the beginning was missing the [ as calling out the subform from the main from should be [[Fsub_Log_Apiary_Main SubForm] based on everything I have found online.

I do want to still try to get it to work from the mainform as putting it in the subform limits the versatility of the filter. I want to be able to adjust the filter if needed by addressing the subform from the mainform.

So I haven't given up totally yet, just for the short term.

[ScubaBart]

I don't think you need the left subform at all. You mainform/subform setup is all wrong. Your main form has no recordsource, so it cannot tell the subforms what to show.
The main form needs its recordsource set to T_Log_Apiary. The left subform currently has that recordsource, so just move the fields from that subform to the main form and remove the subform.
That allows Interim_link to be changed to Log_Apairy_ID.
Now add navigation buttons back to the main form so that you can move from one apiary to the next.

Another big problem is the code that is opening F_Main_Overview when returning from "Add/Edit Apiary Details" and "Add/Edit Hive Details".
Both of those forms, when returning to F_Main_Overview have syntax that opens the form in Form Add mode which means it shows nothing and cannot scroll records.
Why? Well, it's kind of a VBA trap having to do with Access Enumerations. In the form open dialog, acNormal resolves to zero. But so does acFormAdd.
So if you have Docmd.openform "F_Main_Overview",acNormal, "", , , acNormal
That 4th position is going to be zero and IN SOME CASES Access is going to open the form in Form Add mode. I can't identify what causes it to happen in some setup and not others.
My advice is never use acNormal anywhere except the first param, and it usually is not required even there.
In this case simply use DoCmd.OpenForm "F_Main_Overview" in both the forms returning to F_Main_Overview.

This analysis is based on the DB uploaded 9/15/24.

Bear in mind, as I am developing this, not everything is in its final format. The main form will be read-only as will some of the other primary forms. Editing from the main from would cause issues because of the nature of the relationships. These are all little details I have to address. I'm working on the general navigation first and then filtering down to the details as I go. It's been a good 15 plus years since I worked in Access and back then, it was simple stuff, not as complex as this, relatively speaking.

I am still learning the VBA syntax so don't have an understanding on what the acNormal and other assorted qualifiers actually do so I will take all the input I can on that. Some of what is in the current version is code based on what I copied when I took a copy of the database and converted my original macros to code. I have historically used macros for everything but this project forced me to use VBA to accomplish some of my operations and once I started, I decided to try and just put everything into VBA and so am learning as I go.

Once this beast is complete, and I am getting closer, I will post the final version for some 'beta testing' so to speak to see if anyone can break it. I will provide a synopsis to describe the intended functions of what should and should not be possible.

[Welshgasman]

Moving to vba, is probably one of your better decisions. Debugging is so much easier.
I doubt you will regret it.

[davegri]

The main form will be read-only

Just FYI, this is easily accomplished for any form:

[Rickwave]

It looks like you're struggling with the syntax for applying a filter to your SubForm. Ensure you're referencing the SubForm correctly in your code; you might need to adjust it to Forms!F_Main_Overview![Fsub_Log_Apiary_Main SubForm].Form!Apiary_Active = -1 for proper filtering.

[Welshgasman]

It looks like you're struggling with the syntax for applying a filter to your SubForm. Ensure you're referencing the SubForm correctly in your code; you might need to adjust it to Forms!F_Main_Overview![Fsub_Log_Apiary_Main SubForm].Form!Apiary_Active = -1 for proper filtering.

I would never use full form reference for any form that I have code in which is referencing that form.?