Getting a combobox to show data based on another combobox ----

[Pat]

OK.

I created a form that has two combos on it. The second combo box
filters it's data based on the first one. Then I put this into a page
on a Tab Control, and now am unable to get the syntax for the
rowsource.

It's like this:

Form with the Tab Control in it = "MainForm"

Form on page in Tab Control = "MySubForm"

On "MySubForm" Combo 1 = "MyItemName" - 3 columns:
Desc,CommonName,NDB_No.

On "MySubForm" Combo 2 = "MyPortions" - 3 columns:
Grams,Msre_desc,NDB_no

The query for "MyPortions" has
"=Forms!MainForm!MySubForm.form!MyItemName.[column](2)"
as criteria for NDB_No

It comes up null

What am I doing wrong, folks?

Please help?

Pat

 

[tina]

you could try
[Forms]![MainForm]![MySubForm]![MyItemName].[Column](2)

but that may not work either. for some reason i keep
thinking i read somewhere that you can't use a column
reference in a query criteria, but i honestly don't know
if that's true or not. at any rate, if you can't get a
direct reference to work, here's a work-around that might
fly:

put an unbound text control on the subform, with Visible
property set to No. we'll name the control ShowValue. set
the control's ControlSource property to
=MyItemName.Column(2)

change your query's criteria to

[Forms]![MainForm]![MySubForm]![ShowValue]

hth

 

[Pat]

I think the problem is the syntax for:
Form-->Tab Control-->Page-->Form-->Combobox.column(2)

I've spent several days in the knowledge base and help files trying to
find this. It worked before I put it in the tab control page as a
subform. Then what worked was (note: the Mysubform was not then a
subform)
=Eval("Forms!MySubForm!MyItemName.[column](2)")

Per the help files you have to eval it or access thinks it's a
function with the "( )"

I have also tried
Eval("[Forms]![MainForm]!tabctl0.page![My Food_
Tracker]![MySubForm]![MyItemName].[Column](2)")
to no avail.

you could try
[Forms]![MainForm]![MySubForm]![MyItemName].[Column](2)

but that may not work either. for some reason i keep
thinking i read somewhere that you can't use a column
reference in a query criteria, but i honestly don't know
if that's true or not. at any rate, if you can't get a
direct reference to work, here's a work-around that might
fly:

put an unbound text control on the subform, with Visible
property set to No. we'll name the control ShowValue. set
the control's ControlSource property to
=MyItemName.Column(2)

change your query's criteria to

[Forms]![MainForm]![MySubForm]![ShowValue]

hth

-----Original Message-----
OK.

I created a form that has two combos on it. The second combo box
filters it's data based on the first one. Then I put this into a page
on a Tab Control, and now am unable to get the syntax for the
rowsource.

It's like this:

Form with the Tab Control in it = "MainForm"

Form on page in Tab Control = "MySubForm"

On "MySubForm" Combo 1 = "MyItemName" - 3 columns:
Desc,CommonName,NDB_No.

On "MySubForm" Combo 2 = "MyPortions" - 3 columns:
Grams,Msre_desc,NDB_no

The query for "MyPortions" has
"=Forms!MainForm!MySubForm.form!MyItemName.[column](2)"
as criteria for NDB_No

It comes up null

What am I doing wrong, folks?

Please help?

Pat
.

 

[Pat]

Tina,

I'm sorry. I did try your suggestions. I (hopefully) clarified the
problem in my other message of this date.

Pat

you could try
[Forms]![MainForm]![MySubForm]![MyItemName].[Column](2)

but that may not work either. for some reason i keep
thinking i read somewhere that you can't use a column
reference in a query criteria, but i honestly don't know
if that's true or not. at any rate, if you can't get a
direct reference to work, here's a work-around that might
fly:

put an unbound text control on the subform, with Visible
property set to No. we'll name the control ShowValue. set
the control's ControlSource property to
=MyItemName.Column(2)

change your query's criteria to

[Forms]![MainForm]![MySubForm]![ShowValue]

hth

-----Original Message-----
OK.

I created a form that has two combos on it. The second combo box
filters it's data based on the first one. Then I put this into a page
on a Tab Control, and now am unable to get the syntax for the
rowsource.

It's like this:

Form with the Tab Control in it = "MainForm"

Form on page in Tab Control = "MySubForm"

On "MySubForm" Combo 1 = "MyItemName" - 3 columns:
Desc,CommonName,NDB_No.

On "MySubForm" Combo 2 = "MyPortions" - 3 columns:
Grams,Msre_desc,NDB_no

The query for "MyPortions" has
"=Forms!MainForm!MySubForm.form!MyItemName.[column](2)"
as criteria for NDB_No

It comes up null

What am I doing wrong, folks?

Please help?

Pat
.

 

[Pat]

I think the problem is the syntax for:
Form-->Tab Control-->Page-->Form-->Combobox.column(2)

The tab control has no effect on the reference.

I've spent several days in the knowledge base and help files trying to
find this. It worked before I put it in the tab control page as a
subform. Then what worked was (note: the Mysubform was not then a
subform)
=Eval("Forms!MySubForm!MyItemName.[column](2)")

Per the help files you have to eval it or access thinks it's a
function with the "( )"

I have also tried
Eval("[Forms]![MainForm]!tabctl0.page![My Food_
Tracker]![MySubForm]![MyItemName].[Column](2)")
to no avail.

This works for me:
=Eval("Forms!MainForm!MySubForm.Form!MyItemName.Column(2)")

I wish it were that easy. It works just fine, if I don't have it in
the tab control, copy the data from Combo 1, column 2 (NDB_No) to the
underlying field in the form (different from the bound field), then
use that field in Combo 2 as a Criteria.

I tried above, Marshall, with great hopes, but the combo box came up
null. I have double checked all names of forms and items and they are
ok.

MainForm is the form the Tab Control is in. Then MySubForm is on a
page of the Tab Control. Can you think of any other reason this might
fail?

Pat

 

[Hugh O'Neill]

Marshall Barton penned prosaically with:

trying to >>>find this. It worked before I put it in the tab control
page as a >>>subform. Then what worked was (note: the Mysubform was
not then a >>>subform)

=Eval("Forms!MySubForm!MyItemName.[column](2)")

Per the help files you have to eval it or access thinks it's a
function with the "( )"

I have also tried
Eval("[Forms]![MainForm]!tabctl0.page![My Food_
Tracker]![MySubForm]![MyItemName].[Column](2)")
to no avail.

This works for me:
=Eval("Forms!MainForm!MySubForm.Form!MyItemName.Column(2)")

I wish it were that easy. It works just fine, if I don't have it in
the tab control, copy the data from Combo 1, column 2 (NDB_No) to the
underlying field in the form (different from the bound field), then
use that field in Combo 2 as a Criteria.

I tried above, Marshall, with great hopes, but the combo box came up
null. I have double checked all names of forms and items and they are >ok.

MainForm is the form the Tab Control is in. Then MySubForm is on a
page of the Tab Control. Can you think of any other reason this might
fail?

What can I say Pat, I can't get that scenario to fail (A97
and AXP). You wouldn't by any chance be using something
other than the standard Access tab control???

What do you get if you use the Expression Builder to build the
reference to the control for you and does that work for you?

hth

Hugh

 

[Pat]

No, it's A97's built in Tab Control

Marshall Barton penned prosaically with:

Pat wrote:
I think the problem is the syntax for:
Form-->Tab Control-->Page-->Form-->Combobox.column(2)

The tab control has no effect on the reference.


I've spent several days in the knowledge base and help files trying to
find this. It worked before I put it in the tab control page as a
subform. Then what worked was (note: the Mysubform was not then a
subform)
=Eval("Forms!MySubForm!MyItemName.[column](2)")

Per the help files you have to eval it or access thinks it's a
function with the "( )"

I have also tried
Eval("[Forms]![MainForm]!tabctl0.page![My Food_
Tracker]![MySubForm]![MyItemName].[Column](2)")
to no avail.

This works for me:
=Eval("Forms!MainForm!MySubForm.Form!MyItemName.Column(2)")

I wish it were that easy. It works just fine, if I don't have it in
the tab control, copy the data from Combo 1, column 2 (NDB_No) to the
underlying field in the form (different from the bound field), then
use that field in Combo 2 as a Criteria.

I tried above, Marshall, with great hopes, but the combo box came up
null. I have double checked all names of forms and items and they are
ok.

MainForm is the form the Tab Control is in. Then MySubForm is on a
page of the Tab Control. Can you think of any other reason this might
fail?

What can I say Pat, I can't get that scenario to fail (A97
and AXP). You wouldn't by any chance be using something
other than the standard Access tab control???

 

[Marshall Barton]

Then I'm at a loss here. I don't what else to try except
for your workaround of copying the value to another control
(which is actually a decent way to do this).
--
Marsh
MVP [MS Access]

No, it's A97's built in Tab Control

Marshall Barton penned prosaically with:
Pat wrote:
I think the problem is the syntax for:
Form-->Tab Control-->Page-->Form-->Combobox.column(2)

The tab control has no effect on the reference.


I've spent several days in the knowledge base and help files trying to
find this. It worked before I put it in the tab control page as a
subform. Then what worked was (note: the Mysubform was not then a
subform)
=Eval("Forms!MySubForm!MyItemName.[column](2)")

Per the help files you have to eval it or access thinks it's a
function with the "( )"

I have also tried
Eval("[Forms]![MainForm]!tabctl0.page![My Food_
Tracker]![MySubForm]![MyItemName].[Column](2)")
to no avail.

This works for me:
=Eval("Forms!MainForm!MySubForm.Form!MyItemName.Column(2)")

I wish it were that easy. It works just fine, if I don't have it in
the tab control, copy the data from Combo 1, column 2 (NDB_No) to the
underlying field in the form (different from the bound field), then
use that field in Combo 2 as a Criteria.

I tried above, Marshall, with great hopes, but the combo box came up
null. I have double checked all names of forms and items and they are
ok.

MainForm is the form the Tab Control is in. Then MySubForm is on a
page of the Tab Control. Can you think of any other reason this might
fail?

What can I say Pat, I can't get that scenario to fail (A97
and AXP). You wouldn't by any chance be using something
other than the standard Access tab control???

 

[Pat]

On Mon, 01 Sep 2003 09:59:01 -0500, Marshall Barton
<[email protected]> penned prosaically with:

Actually I found the bug with a Search and Replace. Somehow some
object ended up with the name of the form, and when I changed the name
of the form, it didn't "take".

Thanks for your help!

Pat