Cascading combo box between two forms

I have been getting some information about cascading cbo's
from he http://www.blueclaw-db.com/filter_combo_box.htm

This example works fine where both cbo's are on the same
form. However, what I would like to do is have the
primary cbo on a main form and the secondary (cascaded
result) cbo on a subform. I edited the commands to
reflect this using the example given and although Access
appears to accept the SQL statement no entries appear in
the cbo.

Should this, in theory work? Or should there be extra
code to cascade between two sheets?

Aaron,

Judging by the name you have used in referring to the combo box, it appears
that the combo box is on the subform. If so, the syntax does NOT include the
name of the subform. The subform is actually held in a control on the main
form called a subform control. You need to refer to this control instead. It
may or may not have the same name as the subform, depending on whether you
changed it or on how you added the subform to the main form. Also, since you
are referring to a combo box, how many columns are in the combo box that you
are referring to? If there is more than one, be aware that you are referring
to the Value property and that will come from the Bound column, which is not
necessarily the column that is displayed in the textbox portion of the combo
box. If that is the problem, you can either change which field you are
filtering on or use the Column property of the combo box to get the value
from the desired column.

To get the name of the subform control, open the main form in design mode
and open the Properties sheet. Click on the subform ONE time. The properties
sheet should show the name of the subform control. If you click more than
once, you'll be in the subform and the properties sheet will show the name
of the subform or its controls. Once you have the name of the subform
control, the syntax would be:

[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]![cboCltName]

I tried referring to a particular column in the SQL, but I don't know if I
wasn't getting the bracketing correct or is SQL just doesn't like specifying
a property of the combo box. But I couldn't get it to work. So, if the Value
of the combo box isn't the Client Name, you'll need to filter on the field
correlates to the bound column.

--
Wayne Morgan
MS Access MVP


"Aaron Howe" wrote in message
...
Yes that would have helped wouldn't it :-)

SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltName)=[Forms]![frmSubStatic]!
[cboCltName]));

Hope this makes sense.

You're right, I was referencing the second column of
cboCltName. However when I altered the query to reflect
this (qryWorkerLookup) it still didn't work. Here is the
SQL so far:

SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltNumber)=[Forms]![frmSubStatic]!
[frmSubDynamic].[Form]![cboCltName]));

Both cboCltName and cboTempName are referencing the second
column (to save a numerical value in the underlying
table), but all fields are available to the query. Is
there any further info I can post, because so far it's
still blanking me...


-----Original Message-----
Aaron,

Judging by the name you have used in referring to the
combo box, it appears
that the combo box is on the subform. If so, the syntax
does NOT include the
name of the subform. The subform is actually held in a
control on the main
form called a subform control. You need to refer to this
control instead. It
may or may not have the same name as the subform,
depending on whether you
changed it or on how you added the subform to the main
form. Also, since you
are referring to a combo box, how many columns are in the
combo box that you
are referring to? If there is more than one, be aware
that you are referring
to the Value property and that will come from the Bound
column, which is not
necessarily the column that is displayed in the textbox
portion of the combo
box. If that is the problem, you can either change which
field you are
filtering on or use the Column property of the combo box
to get the value
from the desired column.

To get the name of the subform control, open the main
form in design mode
and open the Properties sheet. Click on the subform ONE
time. The properties
sheet should show the name of the subform control. If you
click more than
once, you'll be in the subform and the properties sheet
will show the name
of the subform or its controls. Once you have the name of
the subform
control, the syntax would be:

[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]!
[cboCltName]

I tried referring to a particular column in the SQL, but
I don't know if I
wasn't getting the bracketing correct or is SQL just
doesn't like specifying
a property of the combo box. But I couldn't get it to
work. So, if the Value
of the combo box isn't the Client Name, you'll need to
filter on the field
correlates to the bound column.

--
Wayne Morgan
MS Access MVP


"Aaron Howe" wrote
in message
...
Yes that would have helped wouldn't it :-)

SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltName)=[Forms]!
[frmSubStatic]!
[cboCltName]));

Hope this makes sense.


.

Forget that, I have it working now with some tweaking.
Problem is now I can't seem to figure out the Requery
command to make it refresh the list - it's only showing an
abbreviated list from one client number...

-----Original Message-----
You're right, I was referencing the second column of
cboCltName. However when I altered the query to reflect
this (qryWorkerLookup) it still didn't work. Here is the
SQL so far:

SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltNumber)=[Forms]!
[frmSubStatic]!
[frmSubDynamic].[Form]![cboCltName]));

Both cboCltName and cboTempName are referencing the
second
column (to save a numerical value in the underlying
table), but all fields are available to the query. Is
there any further info I can post, because so far it's
still blanking me...


-----Original Message-----
Aaron,

Judging by the name you have used in referring to the
combo box, it appears
that the combo box is on the subform. If so, the syntax
does NOT include the
name of the subform. The subform is actually held in a
control on the main
form called a subform control. You need to refer to this
control instead. It
may or may not have the same name as the subform,
depending on whether you
changed it or on how you added the subform to the main
form. Also, since you
are referring to a combo box, how many columns are in
the
combo box that you
are referring to? If there is more than one, be aware
that you are referring
to the Value property and that will come from the Bound
column, which is not
necessarily the column that is displayed in the textbox
portion of the combo
box. If that is the problem, you can either change which
field you are
filtering on or use the Column property of the combo box
to get the value
from the desired column.

To get the name of the subform control, open the main
form in design mode
and open the Properties sheet. Click on the subform ONE
time. The properties
sheet should show the name of the subform control. If
you
click more than
once, you'll be in the subform and the properties sheet
will show the name
of the subform or its controls. Once you have the name
of
the subform
control, the syntax would be:

[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]!
[cboCltName]

I tried referring to a particular column in the SQL, but
I don't know if I
wasn't getting the bracketing correct or is SQL just
doesn't like specifying
a property of the combo box. But I couldn't get it to
work. So, if the Value
of the combo box isn't the Client Name, you'll need to
filter on the field
correlates to the bound column.

--
Wayne Morgan
MS Access MVP


"Aaron Howe" wrote
in message
...
Yes that would have helped wouldn't it :-)

SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltName)=[Forms]!
[frmSubStatic]!
[cboCltName]));

Hope this makes sense.


.

.

In the AfterUpdate event of the combo on the main form try,

Me.frmSubDynamic.Form!cboCltName.Requery

Do this after you have assigned the SQL.

--
Wayne Morgan
MS Access MVP


"Aaron Howe" wrote in message
...
Forget that, I have it working now with some tweaking.
Problem is now I can't seem to figure out the Requery
command to make it refresh the list - it's only showing an
abbreviated list from one client number...

-----Original Message-----
You're right, I was referencing the second column of
cboCltName. However when I altered the query to reflect
this (qryWorkerLookup) it still didn't work. Here is the
SQL so far:

SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltNumber)=[Forms]!
[frmSubStatic]!
[frmSubDynamic].[Form]![cboCltName]));

Both cboCltName and cboTempName are referencing the
second
column (to save a numerical value in the underlying
table), but all fields are available to the query. Is
there any further info I can post, because so far it's
still blanking me...


-----Original Message-----
Aaron,

Judging by the name you have used in referring to the
combo box, it appears
that the combo box is on the subform. If so, the syntax
does NOT include the
name of the subform. The subform is actually held in a
control on the main
form called a subform control. You need to refer to this
control instead. It
may or may not have the same name as the subform,
depending on whether you
changed it or on how you added the subform to the main
form. Also, since you
are referring to a combo box, how many columns are in
the
combo box that you
are referring to? If there is more than one, be aware
that you are referring
to the Value property and that will come from the Bound
column, which is not
necessarily the column that is displayed in the textbox
portion of the combo
box. If that is the problem, you can either change which
field you are
filtering on or use the Column property of the combo box
to get the value
from the desired column.

To get the name of the subform control, open the main
form in design mode
and open the Properties sheet. Click on the subform ONE
time. The properties
sheet should show the name of the subform control. If
you
click more than
once, you'll be in the subform and the properties sheet
will show the name
of the subform or its controls. Once you have the name
of
the subform
control, the syntax would be:

[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]!
[cboCltName]

I tried referring to a particular column in the SQL, but
I don't know if I
wasn't getting the bracketing correct or is SQL just
doesn't like specifying
a property of the combo box. But I couldn't get it to
work. So, if the Value
of the combo box isn't the Client Name, you'll need to
filter on the field
correlates to the bound column.

--
Wayne Morgan
MS Access MVP


"Aaron Howe" wrote
in message
.. .
Yes that would have helped wouldn't it :-)

SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltName)=[Forms]!
[frmSubStatic]!
[cboCltName]));

Hope this makes sense.


.

.

That works, thank you very much Wayne. I've learned a lot
from those pieces of code, hopefully I'll have the
confidence to fix the next few problems I'm going to
encounter now!


-----Original Message-----
In the AfterUpdate event of the combo on the main form
try,

Me.frmSubDynamic.Form!cboCltName.Requery

Do this after you have assigned the SQL.

--
Wayne Morgan
MS Access MVP


"Aaron Howe" wrote
in message
...
Forget that, I have it working now with some tweaking.
Problem is now I can't seem to figure out the Requery
command to make it refresh the list - it's only showing
an
abbreviated list from one client number...

-----Original Message-----
You're right, I was referencing the second column of
cboCltName. However when I altered the query to reflect
this (qryWorkerLookup) it still didn't work. Here is
the
SQL so far:

SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltNumber)=[Forms]!
[frmSubStatic]!
[frmSubDynamic].[Form]![cboCltName]));

Both cboCltName and cboTempName are referencing the
second
column (to save a numerical value in the underlying
table), but all fields are available to the query. Is
there any further info I can post, because so far it's
still blanking me...


-----Original Message-----
Aaron,

Judging by the name you have used in referring to the
combo box, it appears
that the combo box is on the subform. If so, the syntax
does NOT include the
name of the subform. The subform is actually held in a
control on the main
form called a subform control. You need to refer to
this
control instead. It
may or may not have the same name as the subform,
depending on whether you
changed it or on how you added the subform to the main
form. Also, since you
are referring to a combo box, how many columns are in
the
combo box that you
are referring to? If there is more than one, be aware
that you are referring
to the Value property and that will come from the Bound
column, which is not
necessarily the column that is displayed in the textbox
portion of the combo
box. If that is the problem, you can either change
which
field you are
filtering on or use the Column property of the combo
box
to get the value
from the desired column.

To get the name of the subform control, open the main
form in design mode
and open the Properties sheet. Click on the subform ONE
time. The properties
sheet should show the name of the subform control. If
you
click more than
once, you'll be in the subform and the properties sheet
will show the name
of the subform or its controls. Once you have the name
of
the subform
control, the syntax would be:

[Forms]![NameOfMainForm]![NameOfSubformControl].[Form]!
[cboCltName]

I tried referring to a particular column in the SQL,
but
I don't know if I
wasn't getting the bracketing correct or is SQL just
doesn't like specifying
a property of the combo box. But I couldn't get it to
work. So, if the Value
of the combo box isn't the Client Name, you'll need to
filter on the field
correlates to the bound column.

--
Wayne Morgan
MS Access MVP


"Aaron Howe"
wrote
in message
. ..
Yes that would have helped wouldn't it :-)

SELECT qryWorkerLookup.TempName,
qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltName)=[Forms]!
[frmSubStatic]!
[cboCltName]));

Hope this makes sense.


.

.



.

And to prove the point, I've just added a second
validation on the field to another criteria to filter it
further - and it works perfectly after I moved the
AfterUpdate event! I hope Santa brings you something
extra this Christmas in recognitition of your good deed ;-)


-----Original Message-----
That works, thank you very much Wayne. I've learned a
lot
from those pieces of code, hopefully I'll have the
confidence to fix the next few problems I'm going to
encounter now!


-----Original Message-----
In the AfterUpdate event of the combo on the main form
try,

Me.frmSubDynamic.Form!cboCltName.Requery

Do this after you have assigned the SQL.

--
Wayne Morgan
MS Access MVP


"Aaron Howe" wrote
in message
...
Forget that, I have it working now with some tweaking.
Problem is now I can't seem to figure out the Requery
command to make it refresh the list - it's only
showing
an
abbreviated list from one client number...

-----Original Message-----
You're right, I was referencing the second column of
cboCltName. However when I altered the query to
reflect
this (qryWorkerLookup) it still didn't work. Here is
the
SQL so far:

SELECT qryWorkerLookup.TempName, qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltNumber)=[Forms]!
[frmSubStatic]!
[frmSubDynamic].[Form]![cboCltName]));

Both cboCltName and cboTempName are referencing the
second
column (to save a numerical value in the underlying
table), but all fields are available to the query. Is
there any further info I can post, because so far it's
still blanking me...


-----Original Message-----
Aaron,

Judging by the name you have used in referring to the
combo box, it appears
that the combo box is on the subform. If so, the
syntax
does NOT include the
name of the subform. The subform is actually held in a
control on the main
form called a subform control. You need to refer to
this
control instead. It
may or may not have the same name as the subform,
depending on whether you
changed it or on how you added the subform to the main
form. Also, since you
are referring to a combo box, how many columns are in
the
combo box that you
are referring to? If there is more than one, be aware
that you are referring
to the Value property and that will come from the
Bound
column, which is not
necessarily the column that is displayed in the
textbox
portion of the combo
box. If that is the problem, you can either change
which
field you are
filtering on or use the Column property of the combo
box
to get the value
from the desired column.

To get the name of the subform control, open the main
form in design mode
and open the Properties sheet. Click on the subform
ONE
time. The properties
sheet should show the name of the subform control. If
you
click more than
once, you'll be in the subform and the properties
sheet
will show the name
of the subform or its controls. Once you have the name
of
the subform
control, the syntax would be:

[Forms]![NameOfMainForm]![NameOfSubformControl].
[Form]!
[cboCltName]

I tried referring to a particular column in the SQL,
but
I don't know if I
wasn't getting the bracketing correct or is SQL just
doesn't like specifying
a property of the combo box. But I couldn't get it to
work. So, if the Value
of the combo box isn't the Client Name, you'll need to
filter on the field
correlates to the bound column.

--
Wayne Morgan
MS Access MVP


"Aaron Howe"
wrote
in message
.. .
Yes that would have helped wouldn't it :-)

SELECT qryWorkerLookup.TempName,
qryWorkerLookup.TempNo
FROM qryWorkerLookup
WHERE (((qryWorkerLookup.CltName)=[Forms]!
[frmSubStatic]!
[cboCltName]));

Hope this makes sense.


.

.



.

.