Solved

Filter Multiple Subforms on Unbound Parent Form

Posted on 2009-04-13
15
387 Views
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:
OrderDate
OrderTruck

Parent form - Driver Input Test

Subform names:
DriverBig
DriverNew
DriverMazda

Unbound Date textbox - txtdate
0
Comment
Question by:Greekiwi
  • 9
  • 6
15 Comments
 
LVL 84

Accepted Solution

by:
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"
0
 

Author Comment

by:Greekiwi
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
0
 
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.
0
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 

Author Comment

by:Greekiwi
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.
0
 
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 ...
0
 

Author Comment

by:Greekiwi
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?
0
 
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.

0
 

Author Comment

by:Greekiwi
ID: 24129202
I checked and it is called Ordertest.

OrderDate is a date field
OrderTruck is a numeric field.
0
 

Author Comment

by:Greekiwi
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
0
 
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".
0
 

Author Comment

by:Greekiwi
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).
0
 
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).
0
 

Author Comment

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

Author Comment

by:Greekiwi
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.
0
 

Author Closing Comment

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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

730 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