Filtering Subforms with VBA

  • Thread starter Thread starter Keith
  • Start date Start date
K

Keith

Whenever I try to use VBA or a Macro to filter my subform,
it doesn't work. I want to filter the subform when I open
the main (parent) form. When I put parameters in the
query that the subform is based on, it keeps asking me
each time I go to a new Parent Form record for the
parameters again.

Any suggestions?

Thanks,

Keith
 
Keith-

What's the SQL of the query you're using in the subform? What VBA code are
you trying to use to filter the subform?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
The probelm is ApplyFilter works only on the outer form that has the focus.
To apply a filter to a subform, you must set the Filter and FilterOn
properties of the form that's inside the subform control.

Try this:

Dim MyCoSelection As String, frm As Form

MyCoSelection = InputBox("Choose your company...", _
"Choose Company Input Box", "301", 100, 100)

Set frm =
Forms!frmHyperionHeaderWQueryAsSubform!frmOracleHeaderAndDetailSubform.Form
frm.Filter = "Co = " & MyCoSelection
frm.FitlterOn = True

If this code is running in the outer form, then you can say:

Set frm = Me!frmOracleHeaderAndDetailSubform.Form

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Keith said:
The SQL for the query on which my subform is based is as
follows:

SELECT tblOracleAccountHeader.Hyperion_Account,
tblOracleAccountHeader.AcctAcctType AS
tblOracleAccountHeader_AcctAcctType,
tblOracleAccountHeader.Account_Description,
tblOracleDetail.Date, tblOracleDetail.Co,
tblOracleDetail.AcctAcctType AS
tblOracleDetail_AcctAcctType,
tblOracleDetail.Prior_Amount,
tblOracleDetail.Current_Amount, tblOracleDetail.[Variance
Explanation]
FROM tblOracleAccountHeader INNER JOIN tblOracleDetail ON
tblOracleAccountHeader.AcctAcctType =
tblOracleDetail.AcctAcctType;

The problem I'm having is this: If I put a parameter in
the query, it asks again each time I move to a new record
in the main form. What I want is to be able to filter the
subform upon entering. I've tried several methods,
DoCmd.ApplyFilter, DoCmd.OpenForm (setting the filter),
but it doesn't work. Here's one example of the code I've
tried:

Dim MyCoSelection As String

MyCoSelection = InputBox("Choose your company...", "Choose
Company Input Box", "301", 100, 100)

DoCmd.ApplyFilter , "Forms!
frmHyperionHeaderWQueryAsSubform!
frmOracleHeaderAndDetailSubform!Co = MyCoSelection"

Any help you can give me would be greatly appreciated.

Thanks

Keith Lorenzen
(e-mail address removed)
-----Original Message-----
Keith-

What's the SQL of the query you're using in the subform? What VBA code are
you trying to use to filter the subform?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)



.
Click to expand...
Click to expand...
 
Thank you so much for your help, John.

I had to modify slightly the code you suggested, but it
works! Here's the code I ended up with in case you're
interested (the names of the forms are different and I
added another field to the filter, but there are a couple
of other differences too).

Dim MyCoSelection As String, frm As Form, MyPeriod As Date

MyCoSelection = InputBox("Choose your company:", _
"Choose Company Input Box", "301", 100, 100)
MyPeriod = InputBox("Choose your Quarter-End Date:", _
"Choose Quarter-End Date Input Box", #9/30/2003#, 100,
100)


Set frm = Forms!frmMainAllFromTables!
frmSubformNotFromQuery.Form
frm.Filter = "[Co] = '" & MyCoSelection & "' And [Date] =
#" & MyPeriod & "#"

'frm.FitlterOn = True (I had to comment this out because
it kept erroring - Keith).

'If this code is running in the outer form, then you can
say:

Set frm = Me!frmSubformNotFromQuery.Form
Forms!frmMainAllFromTables!
frmSubformNotFromQuery.Form.FilterOn = True

Thanks again for your help. I really appreciate it.

Keith Lorenzen
(e-mail address removed)

-----Original Message-----
The probelm is ApplyFilter works only on the outer form that has the focus.
To apply a filter to a subform, you must set the Filter and FilterOn
properties of the form that's inside the subform control.

Try this:

Dim MyCoSelection As String, frm As Form

MyCoSelection = InputBox("Choose your company...", _
"Choose Company Input Box", "301", 100, 100)

Set frm =
Forms!frmHyperionHeaderWQueryAsSubform! frmOracleHeaderAndDetailSubform.Form
frm.Filter = "Co = " & MyCoSelection
frm.FitlterOn = True

If this code is running in the outer form, then you can say:

Set frm = Me!frmOracleHeaderAndDetailSubform.Form

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Keith said:
The SQL for the query on which my subform is based is as
follows:

SELECT tblOracleAccountHeader.Hyperion_Account,
tblOracleAccountHeader.AcctAcctType AS
tblOracleAccountHeader_AcctAcctType,
tblOracleAccountHeader.Account_Description,
tblOracleDetail.Date, tblOracleDetail.Co,
tblOracleDetail.AcctAcctType AS
tblOracleDetail_AcctAcctType,
tblOracleDetail.Prior_Amount,
tblOracleDetail.Current_Amount, tblOracleDetail. [Variance
Explanation]
FROM tblOracleAccountHeader INNER JOIN tblOracleDetail ON
tblOracleAccountHeader.AcctAcctType =
tblOracleDetail.AcctAcctType;

The problem I'm having is this: If I put a parameter in
the query, it asks again each time I move to a new record
in the main form. What I want is to be able to filter the
subform upon entering. I've tried several methods,
DoCmd.ApplyFilter, DoCmd.OpenForm (setting the filter),
but it doesn't work. Here's one example of the code I've
tried:

Dim MyCoSelection As String

MyCoSelection = InputBox("Choose your company...", "Choose
Company Input Box", "301", 100, 100)

DoCmd.ApplyFilter , "Forms!
frmHyperionHeaderWQueryAsSubform!
frmOracleHeaderAndDetailSubform!Co = MyCoSelection"

Any help you can give me would be greatly appreciated.

Thanks

Keith Lorenzen
(e-mail address removed)
-----Original Message-----
Keith-

What's the SQL of the query you're using in the
Click to expand...
subform?
What VBA code are
you trying to use to filter the subform?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Whenever I try to use VBA or a Macro to filter my subform,
it doesn't work. I want to filter the subform when I open
the main (parent) form. When I put parameters in the
query that the subform is based on, it keeps asking me
each time I go to a new Parent Form record for the
parameters again.

Any suggestions?

Thanks,

Keith


.
Click to expand...
Click to expand...


.
Click to expand...
 
Back
Top