• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 410
  • Last Modified:

Filter Multiple Subforms on Unbound Parent Form

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
Greekiwi
Asked:
Greekiwi
  • 9
  • 6
1 Solution
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
GreekiwiAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
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!

 
GreekiwiAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
GreekiwiAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
GreekiwiAuthor Commented:
I checked and it is called Ordertest.

OrderDate is a date field
OrderTruck is a numeric field.
0
 
GreekiwiAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
GreekiwiAuthor Commented:
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
 
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
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
 
GreekiwiAuthor Commented:
I know the problem, its the American dating problem again
0
 
GreekiwiAuthor Commented:
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
 
GreekiwiAuthor Commented:
Thanks for being so patient with me. You have probably been one of the biggest helps in making this DB.
0

Featured Post

 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

  • 9
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now