Solved

Filter Multiple Subforms on Unbound Parent Form

Posted on 2009-04-13
15
354 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
 

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
Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

 

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

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions 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 Ac…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

759 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now