17. (Advanced Programming In Access 2013) Filtering Sub-Forms Using VBA

Filtering Sub-Forms Using VBA video in the "Advanced Programming in Microsoft Access 2013" series hosted by Steve Bishop. In this free advanced video tutorial series Steve will be going over Microsoft SQL Server installation, Database Migration, creating a better User Interface, using external data sources, complex Visual Basic For Applications (VBA) concepts and distributing your application.

Click here for the full playlist of "Advanced Programming in Access 2013":

Click here for the Work Files of this series:

[gardnmi]

The built in access method of filtering subforms had me scratching my head for a week. The vba method is more straightforward and ultimately more flexible. Thanks for posting these tutorials for free.

[EmersonCabrera]

Excuse me for my bad english, I want to thank you for your very useful videos. In all Youtube I have not found enough videos about VBA for Access in my language (spanish), but your videos have served to me a lot, you are excellent!

[artsafari]

Thank you! Clear, concise, and thorough - a running theme in your videos!

[swapnilwankhede]

Hi Steve
Excellent Tutorials, Thanks a lot Steve for uploading this videos, it’s really helpful, never found such good videos.

[mode]

great video, but how we can return all values if there is no selection in combo box?
im doing a project and that is there request.

[cesarleopoldosouza]

Thank you for your job.
Question: Is it applicable to ussing ADO connection and ADORecordset ?
If Not, can it be done in ADO ?
Cesar - Brasil

[johnpescat]

Thank you for ur help. Question: I have develop a access (2016) program on one computer and load it onto another computer using access (2016) when I click of form combo box to do a lookup value it works fine. When I do a second lookup value it knocks me out of the program completely. Also, all the VBA codes are completely gone.  Ur thoughts . Thanks JP

[mhabdallahpi]

Is there a way to use the combo box to set values in the fields of the subform, instead of filter? For example, you have a linked table tblAllPeople and you want to use that to populate a form in an unrelated table (tblSpecialPeople) based on the selection of the combobox

[hyperoid]

What if you want to filter out two employees

[asimrabh]

Hi Steve, how do I hide or show a column in a subform without going into design mode? Thanks

[warhero]

Hello, Thank you for posting this. I have a question about this method working for a Text box that is used as a Search Box. I was trying to incorporate these procedures in a Subroutine that works off of a Click Action to populate both my main and Subform. How would I be able to incorporate the Subform Values into the code for my Search Box?

[cozak]

I get that the record source specified on this form or report does not exist, while it it the correct name since I see it appear when I type it.... So annoying...

[najibafghan]

How can we just print these filtered records only

[HarveyFrench]

A very clear video, however, I would recommend that the code which builds a SQL statement for a subform is declared in a public sub routine of subforms code module.

This encapsulates the code relating to the subform in the subform.

Any custom properties, methods and functions you delcare in the subform module can be referenced from the main form using:



Me.MySubFormControl.Form.MyFunction(P1, ...)

eg In the subform you could use a method:

Public Sub CreateSQL(ProductID as variant) 

    me.recordsource = "SELECT ...."   

End Sub

note that I have just opened a question on stack overflow, which further explores this issue.  (In the past people have said, use C#, Use VB, stop using VBA, but I think there is a call for access to behave itself better, so I'm asking again, but differently!)


I'd value you comments there.

Harvey

[HarveyFrench]

Although the technique you show will work, I prefer a technique that will allow the user to set the filter and orderby properties themselves.  I'm working a demo and will let you know when I've got it together!  (PS. I've still not watched you remaining advanced videos, so maybe you cover this?)

[kominyu]

Hi Sir
Thanks a lot for your clear tutorial, but i have one question. If i want to filter by date not by id, how it could be? I try it, but i can't filter it by Sale Date. This is by coding, can you tell me why i can't filter it by date?
Dim SQL2 As String
SQL2 = "SELECT [Installment Sale].InstID, [Installment Sale].Sale_Date, [Installment Sale].[Item Name], " _
& "[Installment Sale].Quantity, Category.ID FROM (Category INNER JOIN Items " _
& "ON Category.ID = Items.Category) INNER JOIN [Installment Sale] ON Items.ID = [Installment Sale].ItemID " _
& "WHERE (((Category.ID)=1)) AND [Installment Sale].Sale_Date=" & Me.CboSaleDate & " "

= SQL2



At the line above, i declare all as you shown.
Thanks

[beewasp]

Hi Steve, awesome series, thank you so much. I've only recently discovered your stuff, but it's been the most helpful I've come across anywhere. I have a question about this topic:

If you are using a combo box to filter BOTH the main form AND its related subform records at once, would you then need two SQL statements (one for the main form, one for the subform)? Similarly, if the main form derived its data from a table, and the subform from a query, with a parent-child field connection, could you use a recordset and an SQL statement to clear the filter in order to show all records again? I've tried the former, it seems to work, but maybe there's a more elegant way. I haven't tried the latter.

[philtheone]

Hello, I would need some support please. Could some tell me how to do this with a text field and then filter the sub form? Thanks a lot

[bartstandaert]

How do i operate linking three subforms in one main form?

[MohamedFetih]

Good Work, but I have one problem please how I highlight the record source in property sheet and whey it not like your example at 0:56 sec it's name start with select when you select sub form ? why?waitingthanks