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.
Excuse me for my bad english, I want to thank you for your very useful videos. In all RUclips 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!
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.
Yes, you would want to set the recordsources to both the main and sub forms using SQL Queries in VBA. I found it's the most comprehensive way to manage.
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
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.MyProperty Me.MySubFormControl.Form.MyMethod 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!) stackoverflow.com/questions/31606432/the-ms-access-subform-control-intellisense-does-not-list-all-form-class-members I'd value you comments there. Harvey
Harvey French Your point is salient but very much a stylistic choice. Even for an advanced course I hesitate to get too far into the weeds on where to put the code for encapsulation except for where it seems necessary. As a side note, I have been exploring an MVVM style of code writing but it's quite difficult without proper .NET classes to handle the form and data updates the code becomes quite verbose. But what I have been able to get to work has been quite nifty. I've thought about doing an episode on using classes a bit more to handle the form interactions and data updating, but it's such an advanced topic I think it's best to wait until this advanced series is complete.
I'd be very interested in seeing how "common" classes can be used to support forms more in Access. I'm not a OO programmer, but Iv'e been using classes in Excel and Access and see the massive benefit they can bring. The advise I keep getting is forget using Access and use C# as it has more "proper" OO features. Your videos are very much appreciated by me. I would love to see you produce a "very advanced" fast based series...
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?
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
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?)
Harvey French Nah, that's added functionality that I didn't really plan on doing since by now hopefully someone can figure out how to alter the SQL based on user clicks. If I started doing videos on every possible scenario then the series would never end. :D
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 & " " Me.Daily_Sale_Report_Subform_for_Daily_Installment_Sale_Quantity.Form.RecordSource = SQL2 Me.Daily_Sale_Report_Subform_for_Daily_Installment_Sale_Quantity.Requery At the line above, i declare all as you shown. Thanks
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...
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
Good evening Steve, I am having issues trying to add the Instr function in a Select statement within VBA. Well researching this issue, I came across the following website on stackoverflow.com/questions/25805168/unidentified-function-instrrev-in-expression-error-in-an-access-query-from-c. The older "Jet" driver for Access did not allow us to use VBA functions like InStrRev() in queries from external applications. Those functions would only be available to queries that were run from within Microsoft Access itself. However, the OLEDB and ODBC drivers for the newer version of the Access Database Engine (a.k.a "ACE") do allow external applications to make use of many of those built-in VBA functions. So, if your application uses Provider=Microsoft.Jet.OLEDB.4.0; (OLEDB), or Driver={Microsoft Access Driver (*.mdb)}; (ODBC) then the InStrRev() function will not work. However, if you use the newer "ACE" driver: Provider=Microsoft.ACE.OLEDB.12.0; (OLEDB), or Driver={Microsoft Access Driver (*.mdb, *.accdb)}; (ODBC) then those same InStrRev() queries will run without error. The newer version of the Access Database Engine (and drivers) is available as a free download here: Microsoft Access Database Engine 2010 Redistributable Thank you David
Thank you for the reply. I have tried your suggestion, but it returns a 0. For some reason when the function is in a Select statement with VBA the issue happens. However, if I create the SQL with the Instr function with the QBE it works just fine.
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.
Excuse me for my bad english, I want to thank you for your very useful videos. In all RUclips 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!
Thank you! Clear, concise, and thorough - a running theme in your videos!
Hi Steve
Excellent Tutorials, Thanks a lot Steve for uploading this videos, it’s really helpful, never found such good videos.
Thank you so much. Brilliant work.
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
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.
Yes, you would want to set the recordsources to both the main and sub forms using SQL Queries in VBA. I found it's the most comprehensive way to manage.
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
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.MyProperty
Me.MySubFormControl.Form.MyMethod
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!)
stackoverflow.com/questions/31606432/the-ms-access-subform-control-intellisense-does-not-list-all-form-class-members
I'd value you comments there.
Harvey
Harvey French Your point is salient but very much a stylistic choice. Even for an advanced course I hesitate to get too far into the weeds on where to put the code for encapsulation except for where it seems necessary. As a side note, I have been exploring an MVVM style of code writing but it's quite difficult without proper .NET classes to handle the form and data updates the code becomes quite verbose. But what I have been able to get to work has been quite nifty. I've thought about doing an episode on using classes a bit more to handle the form interactions and data updating, but it's such an advanced topic I think it's best to wait until this advanced series is complete.
I'd be very interested in seeing how "common" classes can be used to support forms more in Access. I'm not a OO programmer, but Iv'e been using classes in Excel and Access and see the massive benefit they can bring.
The advise I keep getting is forget using Access and use C# as it has more "proper" OO features. Your videos are very much appreciated by me. I would love to see you produce a "very advanced" fast based series...
Harvey French I'll think about that. We'll see where this series ends up.
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?
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.
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
great stuff!
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?)
Harvey French Nah, that's added functionality that I didn't really plan on doing since by now hopefully someone can figure out how to alter the SQL based on user clicks. If I started doing videos on every possible scenario then the series would never end. :D
Hi Steve, how do I hide or show a column in a subform without going into design mode? Thanks
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 & " "
Me.Daily_Sale_Report_Subform_for_Daily_Installment_Sale_Quantity.Form.RecordSource = SQL2
Me.Daily_Sale_Report_Subform_for_Daily_Installment_Sale_Quantity.Requery
At the line above, i declare all as you shown.
Thanks
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
How can we just print these filtered records only
How do i operate linking three subforms in one main form?
What if you want to filter out two employees
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...
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
I don't understand your question.
Good evening Steve,
I am having issues trying to add the Instr function in a Select statement within VBA. Well researching this issue, I came across the following website on stackoverflow.com/questions/25805168/unidentified-function-instrrev-in-expression-error-in-an-access-query-from-c.
The older "Jet" driver for Access did not allow us to use VBA functions like InStrRev() in queries from external applications. Those functions would only be available to queries that were run from within Microsoft Access itself.
However, the OLEDB and ODBC drivers for the newer version of the Access Database Engine (a.k.a "ACE") do allow external applications to make use of many of those built-in VBA functions. So, if your application uses
Provider=Microsoft.Jet.OLEDB.4.0; (OLEDB), or
Driver={Microsoft Access Driver (*.mdb)}; (ODBC)
then the InStrRev() function will not work. However, if you use the newer "ACE" driver:
Provider=Microsoft.ACE.OLEDB.12.0; (OLEDB), or
Driver={Microsoft Access Driver (*.mdb, *.accdb)}; (ODBC)
then those same InStrRev() queries will run without error.
The newer version of the Access Database Engine (and drivers) is available as a free download here:
Microsoft Access Database Engine 2010 Redistributable
Thank you
David
+da jo You should be able to build a VBA function that includes an InStr and InStrRev function in them and returns the value to the query.
Thank you for the reply. I have tried your suggestion, but it returns a 0. For some reason when the function is in a Select statement with VBA the issue happens. However, if I create the SQL with the Instr function with the QBE it works just fine.
Access 2003 As Full And Good From 2007 and up versions .
convert to XML Before Saving
Access 2016 Xxxxxxxx Errors
Why for Save Record .