=> Drop Down list box to determine data in subform

  • Thread starter Thread starter Rhonda Fischer
  • Start date Start date
R

Rhonda Fischer

Hello,

I have a main form with a subform. The subform is
populated with data based on it's record source query.

I would like the user to have the flexibility of
changing the data displayed in the subform based on
which drop down item they select.

On the click event of the combo box I would either
like to:

(1) Set the focus on the subform and Requiry this with a
new query not the one in it's record source.

OR MAYBE

(2) Close the subform and open a new form
that looks the same with a different record source.

The combo select will be 'All', 'Tesco', 'Asda' for
which different data is displayed.

If combo = Tesco
load subform with replacement query
'qryInteractiveTrackingTesco'

I'm not sure how I can achieve this, any suggestions would
be terrific.

Cheerio
Rhonda
 
Hi Rhonda,

This is a pretty common thing to do - first I would create a table
containing the option and the query that is used based on the selection.
You could use a Select Case structure instead but this still requires a
design change when the options change. If the options ("All", "Tesco" and
"Asda") are already coming from a table you could probably just add another
field to that table.

If you can include this in your combo in the AfterUpdate event of the combo,
you simply set the RecordSource of the subform using column n-1 of the
combo. Lets say that the query name is in the 2nd column of the combo then
your AfterUpdate event would be something like:

If combo is on the mainform
--------------------------------------
me.sfrmMySub.Form.Recordsource=me.MyCombo.column(1)

If combo is on the subform
----------------------------------
me.Recordsource=me.MyCombo.column(1)

If you don't want to add the query name to the combo then you could still do
this using the Select Case statement

If combo is on the mainform
--------------------------------------
Select Case me.Mycombo
case "Tesco"
me.sfrmMySub.form.recordsource="qryInteractiveTrackingTesco"
case "Asda"
me.sfrmMySub.form.recordsource="qryInteractiveTrackingAsda"
case "All"
me.sfrmMySub.form.recordsource="qryInteractiveTrackingAll"
end select


If combo is on the subform
----------------------------------
Select Case me.Mycombo
case "Tesco"
me.recordsource="qryInteractiveTrackingTesco"
case "Asda"
me.recordsource="qryInteractiveTrackingAsda"
case "All"
me.recordsource="qryInteractiveTrackingAll"
end select

Note that if your combo is on the mainform then replace 'sfrmMySub' with the
name of the subform control on the main form. This is not necessarily the
same as the name of the form object that is referenced in the ControlSource
of the subform control. To be sure, open the main form and click once on the
subform then check the name property under the Other tab. Whatever you find
there is what belongs in place of sfrmMySub.
 
Hello Sandra,

Thank you very much for your very helpful response,
I now have it working, very happy.

I placed the query name in the combo box row source
and called the line of code:

******************************************************
Me.FrmInteractiveTrackingDetails.Form.RecordSource =
Me.cboSupermarket.Column(1)
******************************************************

I was wondering why I received the error message:
Runtime error '3251' Operation is not supported for
this type of object, when I tried to use the case
structure below, just curiousity really, as I have it
working.

Again, thank you very much for your help.

Kindest Regards
Rhonda

*******************************************************
Private Sub cboSupermarket_AfterUpdate()

Select Case Me.cboSupermarket
Case "Tesco"

Me.FrmInteractiveTrackingDetails.Form.Recordset
= "QryInteractiveTrackingTesco"
Case "Asda"

Me.FrmInteractiveTrackingDetails.Form.Recordset
= "QryInteractiveTrackingAsda"
Case "All"

Me.FrmInteractiveTrackingDetails.Form.Recordset
= "QryInteractiveTracking"
End Select

End Sub
*********************************************************



-----Original Message-----
Hi Rhonda,

This is a pretty common thing to do - first I would create a table
containing the option and the query that is used based on the selection.
You could use a Select Case structure instead but this still requires a
design change when the options change. If the options ("All", "Tesco" and
"Asda") are already coming from a table you could probably just add another
field to that table.

If you can include this in your combo in the AfterUpdate event of the combo,
you simply set the RecordSource of the subform using column n-1 of the
combo. Lets say that the query name is in the 2nd column of the combo then
your AfterUpdate event would be something like:

If combo is on the mainform
--------------------------------------
me.sfrmMySub.Form.Recordsource=me.MyCombo.column(1)

If combo is on the subform
----------------------------------
me.Recordsource=me.MyCombo.column(1)

If you don't want to add the query name to the combo then you could still do
this using the Select Case statement

If combo is on the mainform
--------------------------------------
Select Case me.Mycombo
case "Tesco"
me.sfrmMySub.form.recordsource="qryInteractiveTrackingTesco
"
case "Asda"
me.sfrmMySub.form.recordsource="qryInteractiveTrackingAsda"
case "All"
me.sfrmMySub.form.recordsource="qryInteractiveTrackingAll"
end select


If combo is on the subform
----------------------------------
Select Case me.Mycombo
case "Tesco"
me.recordsource="qryInteractiveTrackingTesco"
case "Asda"
me.recordsource="qryInteractiveTrackingAsda"
case "All"
me.recordsource="qryInteractiveTrackingAll"
end select

Note that if your combo is on the mainform then replace 'sfrmMySub' with the
name of the subform control on the main form. This is not necessarily the
same as the name of the form object that is referenced in the ControlSource
of the subform control. To be sure, open the main form and click once on the
subform then check the name property under the Other tab. Whatever you find
there is what belongs in place of sfrmMySub.
--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Rhonda said:
Hello,

I have a main form with a subform. The subform is
populated with data based on it's record source query.

I would like the user to have the flexibility of
changing the data displayed in the subform based on
which drop down item they select.

On the click event of the combo box I would either
like to:

(1) Set the focus on the subform and Requiry this with a
new query not the one in it's record source.

OR MAYBE

(2) Close the subform and open a new form
that looks the same with a different record source.

The combo select will be 'All', 'Tesco', 'Asda' for
which different data is displayed.

If combo = Tesco
load subform with replacement query
'qryInteractiveTrackingTesco'

I'm not sure how I can achieve this, any suggestions would
be terrific.

Cheerio
Rhonda
Click to expand...

.
Click to expand...
 
Hi Rhonda,

I think the error was because in the Select...Case statement you were
assigning a string to the *recordset* property of the subform's form. The
Recordset property can only be set to a recordset object. What you should
have had was a string assignment to the RecordSource property as you have in
the assignment using the combo.

Me.FrmInteractiveTrackingDetails.Form.Recordset =
"QryInteractiveTrackingTesco"

Should be

Me.FrmInteractiveTrackingDetails.Form.Recordsource =
"QryInteractiveTrackingTesco"

Glad you have it working :-)

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Rhonda said:
Hello Sandra,

Thank you very much for your very helpful response,
I now have it working, very happy.

I placed the query name in the combo box row source
and called the line of code:

******************************************************
Me.FrmInteractiveTrackingDetails.Form.RecordSource =
Me.cboSupermarket.Column(1)
******************************************************

I was wondering why I received the error message:
Runtime error '3251' Operation is not supported for
this type of object, when I tried to use the case
structure below, just curiousity really, as I have it
working.

Again, thank you very much for your help.

Kindest Regards
Rhonda

*******************************************************
Private Sub cboSupermarket_AfterUpdate()

Select Case Me.cboSupermarket
Case "Tesco"

Me.FrmInteractiveTrackingDetails.Form.Recordset
= "QryInteractiveTrackingTesco"
Case "Asda"

Me.FrmInteractiveTrackingDetails.Form.Recordset
= "QryInteractiveTrackingAsda"
Case "All"

Me.FrmInteractiveTrackingDetails.Form.Recordset
= "QryInteractiveTracking"
End Select

End Sub
*********************************************************



-----Original Message-----
Hi Rhonda,

This is a pretty common thing to do - first I would create a table
containing the option and the query that is used based on the
selection. You could use a Select Case structure instead but this
still requires a design change when the options change. If the
options ("All", "Tesco" and "Asda") are already coming from a table
you could probably just add another field to that table.

If you can include this in your combo in the AfterUpdate event of
the combo, you simply set the RecordSource of the subform using
column n-1 of the combo. Lets say that the query name is in the 2nd
column of the combo then your AfterUpdate event would be something
like:

If combo is on the mainform
--------------------------------------
me.sfrmMySub.Form.Recordsource=me.MyCombo.column(1)

If combo is on the subform
----------------------------------
me.Recordsource=me.MyCombo.column(1)

If you don't want to add the query name to the combo then you could
still do this using the Select Case statement

If combo is on the mainform
--------------------------------------
Select Case me.Mycombo
case "Tesco"
me.sfrmMySub.form.recordsource="qryInteractiveTrackingTesco
"
case "Asda"
me.sfrmMySub.form.recordsource="qryInteractiveTrackingAsda"
case "All"
me.sfrmMySub.form.recordsource="qryInteractiveTrackingAll"
end select


If combo is on the subform
----------------------------------
Select Case me.Mycombo
case "Tesco"
me.recordsource="qryInteractiveTrackingTesco"
case "Asda"
me.recordsource="qryInteractiveTrackingAsda"
case "All"
me.recordsource="qryInteractiveTrackingAll"
end select

Note that if your combo is on the mainform then replace 'sfrmMySub' with the
name of the subform control on the main form. This is not
necessarily the same as the name of the form object that is
referenced in the ControlSource of the subform control. To be sure,
open the main form and click once on the subform then check the name
property under the Other tab. Whatever you find there is what
belongs in place of sfrmMySub. --
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Rhonda said:
Hello,

I have a main form with a subform. The subform is
populated with data based on it's record source query.

I would like the user to have the flexibility of
changing the data displayed in the subform based on
which drop down item they select.

On the click event of the combo box I would either
like to:

(1) Set the focus on the subform and Requiry this with a
new query not the one in it's record source.

OR MAYBE

(2) Close the subform and open a new form
that looks the same with a different record source.

The combo select will be 'All', 'Tesco', 'Asda' for
which different data is displayed.

If combo = Tesco
load subform with replacement query
'qryInteractiveTrackingTesco'

I'm not sure how I can achieve this, any suggestions would
be terrific.

Cheerio
Rhonda
Click to expand...

.
Click to expand...
Click to expand...
 
Terrific, Thank you very much :)

Cheerio
Rhonda


-----Original Message-----
Hi Rhonda,

I think the error was because in the Select...Case statement you were
assigning a string to the *recordset* property of the subform's form. The
Recordset property can only be set to a recordset object. What you should
have had was a string assignment to the RecordSource property as you have in
the assignment using the combo.

Me.FrmInteractiveTrackingDetails.Form.Recordset =
"QryInteractiveTrackingTesco"

Should be

Me.FrmInteractiveTrackingDetails.Form.Recordsource =
"QryInteractiveTrackingTesco"

Glad you have it working :-)

--
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Rhonda said:
Hello Sandra,

Thank you very much for your very helpful response,
I now have it working, very happy.

I placed the query name in the combo box row source
and called the line of code:

******************************************************
Me.FrmInteractiveTrackingDetails.Form.RecordSource =
Me.cboSupermarket.Column(1)
******************************************************

I was wondering why I received the error message:
Runtime error '3251' Operation is not supported for
this type of object, when I tried to use the case
structure below, just curiousity really, as I have it
working.

Again, thank you very much for your help.

Kindest Regards
Rhonda

*******************************************************
Private Sub cboSupermarket_AfterUpdate()

Select Case Me.cboSupermarket
Case "Tesco"

Me.FrmInteractiveTrackingDetails.Form.Recordset
= "QryInteractiveTrackingTesco"
Case "Asda"

Me.FrmInteractiveTrackingDetails.Form.Recordset
= "QryInteractiveTrackingAsda"
Case "All"

Me.FrmInteractiveTrackingDetails.Form.Recordset
= "QryInteractiveTracking"
End Select

End Sub
*********************************************************



-----Original Message-----
Hi Rhonda,

This is a pretty common thing to do - first I would create a table
containing the option and the query that is used based on the
selection. You could use a Select Case structure instead but this
still requires a design change when the options change. If the
options ("All", "Tesco" and "Asda") are already coming from a table
you could probably just add another field to that table.

If you can include this in your combo in the AfterUpdate event of
the combo, you simply set the RecordSource of the subform using
column n-1 of the combo. Lets say that the query name is in the 2nd
column of the combo then your AfterUpdate event would be something
like:

If combo is on the mainform
--------------------------------------
me.sfrmMySub.Form.Recordsource=me.MyCombo.column(1)

If combo is on the subform
----------------------------------
me.Recordsource=me.MyCombo.column(1)

If you don't want to add the query name to the combo then you could
still do this using the Select Case statement

If combo is on the mainform
Click to expand...
me.sfrmMySub.form.recordsource="qryInteractiveTrackingTesco
"
case "Asda"
Click to expand...
me.sfrmMySub.form.recordsource="qryInteractiveTrackingAsda"
case "All"
Click to expand...
me.sfrmMySub.form.recordsource="qryInteractiveTrackingAll"
end select


If combo is on the subform
----------------------------------
Select Case me.Mycombo
case "Tesco"
me.recordsource="qryInteractiveTrackingTesco"
case "Asda"
me.recordsource="qryInteractiveTrackingAsda"
case "All"
me.recordsource="qryInteractiveTrackingAll"
end select

Note that if your combo is on the mainform then replace 'sfrmMySub' with the
name of the subform control on the main form. This is not
necessarily the same as the name of the form object that is
referenced in the ControlSource of the subform control. To be sure,
open the main form and click once on the subform then check the name
property under the Other tab. Whatever you find there is what
belongs in place of sfrmMySub. --
Sandra Daigle
[Microsoft Access MVP]
For the benefit of others please post all replies to this newsgroup.


Rhonda Fischer wrote:
Hello,

I have a main form with a subform. The subform is
populated with data based on it's record source query.

I would like the user to have the flexibility of
changing the data displayed in the subform based on
which drop down item they select.

On the click event of the combo box I would either
like to:

(1) Set the focus on the subform and Requiry this with a
new query not the one in it's record source.

OR MAYBE

(2) Close the subform and open a new form
that looks the same with a different record source.

The combo select will be 'All', 'Tesco', 'Asda' for
which different data is displayed.

If combo = Tesco
load subform with replacement query
'qryInteractiveTrackingTesco'

I'm not sure how I can achieve this, any suggestions would
be terrific.

Cheerio
Rhonda

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

.
Click to expand...
 
Back
Top