Filter Multiple Subforms on Unbound Parent Form

Posted on 2009-04-13
Medium Priority
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
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
  • 9
  • 6
LVL 85

Accepted Solution

Scott McDaniel (Microsoft Access MVP - EE MVE ) earned 2000 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 85
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.
Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.


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 85
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 85
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 85
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 85
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


Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

762 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