Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions

Filter Multiple Subforms on Unbound Parent Form

Posted on 2009-04-13
Last Modified: 2013-11-28
I have an unbound parent form which displays 3 subforms. I want to be able to filter the subforms based on a date (input into an unbound textbox on the parent form).

This is for a moving company and each subform represents records pertaining to one of 3 trucks. So each subform needs to have an additional part to the filter so it can filter which truck it is on.

Is this possible? Would it be easier to bind the parent form?

Fields to be filtered:

Parent form - Driver Input Test

Subform names:

Unbound Date textbox - txtdate
Question by:Greekiwi
  • 9
  • 6
LVL 84

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 500 total points
ID: 24129043
Just set the Recordsource of each subform directly when the Mainform record changes:

Me.SubformControl1.Form.Recordsource = "SELECT * FROM SomeTable WHERE OrderDate=#" & Me.txtDate & "# AND OrderTruck=2"
Me.SubformControl1.Form.Recordsource = "SELECT * FROM SomeTable WHERE OrderDate=#" & Me.txtDate & "# AND OrderTruck=3"
Me.SubformControl1.Form.Recordsource = "SELECT * FROM SomeTable WHERE OrderDate=#" & Me.txtDate & "# AND OrderTruck=5"

Author Comment

ID: 24129093
For some reason I got "Method or Data member not found" and it highlights the txtdate part. The field is definitely named right though

Private Sub Command54_Click()
Me.DriverBig2.Form.RecordSource = "SELECT * FROM Order WHERE OrderDate=#" & Me.txtdate & "# AND OrderTruck=1"
Me.DriverNew2.Form.RecordSource = "SELECT * FROM Order WHERE OrderDate=#" & Me.txtdate & "# AND OrderTruck=2"
Me.DriverMazda2.Form.RecordSource = "SELECT * FROM Order WHERE OrderDate=#" & Me.txtdate & "# AND OrderTruck=3"
End Sub
LVL 84
ID: 24129095
Are you running this on the same form that contains txtDate? Not a Subform, but on the actual Main form? If you are, then recheck that spelling and try again ... if not, then you'll have to refer to it differently.
U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.


Author Comment

ID: 24129103
Yea. txtdate is on the Parent form which also contains that code. I have check the spelling, tried naming it something else and it still doesn't work.
LVL 84
ID: 24129119
Are DriverBig2, DriverNew2 and DriverMazda2 the names of the Subform CONTROLS on your main form? They probably are, else you'd get a different error, but check to be sure.

Is "txtDate" a standard Access textbox?

If you're confident of those two things, then you might want to upload your database here ...

Author Comment

ID: 24129142
I just created a new Mainform with a new SubForm. The old form I was using had some settings changed so I thought it best to start a new.With the new form I got past the original problem. Now this problem comes up.

The new Subform name is OrderTest.

I now get a "Syntax Error in FROM clause" error.

Me.Ordertest.Form.RecordSource = "SELECT * FROM Order WHERE OrderDate=#" & Me.txtdate & "# AND OrderTruck=1"

Also Subform CONTROLS as compared to what?
LVL 84
ID: 24129166
The Subform you're viewing is contained in a Subform CONTROL on the main form ... you need the name of that CONTROL, which may or may not be the name of the form you're using as a subform. Depending on how it was built, the subform control could be named after the form it contains, or it could be named something like Child12.

Is OrderDate a Date field, and is OrderTruck a numeric field? Date fields must be enclosed in hash marks ( # ), and Text fields must be enclosed in quotes ( ' ) or ( " ). I find single quotes easier to work with.


Author Comment

ID: 24129202
I checked and it is called Ordertest.

OrderDate is a date field
OrderTruck is a numeric field.

Author Comment

ID: 24129241
Since it is 12:20am and I have work in a few hours this will probably be my last reply for tonight. I have uploaded the DB to https://filedb.experts-exchange.com/incoming/ee-stuff/7598-CMv32---Copy.accdb for you to have a look at if you want.

Form in question is called Form2
LVL 84
ID: 24131806
You must enclose the Table name Order in square brackets:

SELECT * FROM [Order] FROM blah blah

This can be avoided if you would adapt nameing conventions ... for example, you might name your table tblOrders, or tOrders, which is not a reserved word like "Order".

Author Comment

ID: 24134810
Ah. I had never made a database before this one (as you can probably tell by now with the number of questions I have asked), so if I ever make another one I will be sure to do that.

I have tried that and it fixed the error but now it shows no records (just the add new record line).
LVL 84
ID: 24136906
I've chosen several dates - 4/12, 4/14 etc - and I get records for all of them, and I also get the New Record line.

<so if I ever make another one I will be sure to do that.>

You really should change those names in THIS application. Rick Fisher's Find and Replace can work wonders with this (www.rickworld.com) as can MZtools (www.mztools.com).

Author Comment

ID: 24137044
I know the problem, its the American dating problem again

Author Comment

ID: 24137600
Me.Ordertest.Form.RecordSource = "SELECT * FROM [Order] WHERE OrderDate=#" & Format(Me.txtdate, "YYYY-MM-DD") & "# AND OrderTruck= 1"

I remember this was one of the first problems I had. Took you posting the dates in American style for me to realise.

Author Closing Comment

ID: 31569443
Thanks for being so patient with me. You have probably been one of the biggest helps in making this DB.

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
table joins in qry 17 60
Access 2010 change CurrentUser 5 32
database opened as read only 10 25
Database keeps telling me Recordset Locked 10 21
Introduction The Visual Basic for Applications (VBA) language is at the heart of every application that you write. It is your key to taking Access beyond the world of wizards into a world where anything is possible. This article introduces you to…
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question