Cascading combo box between two forms

A

Aaron Howe

I have been getting some information about cascading cbo's
from here: 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?
 
W

Wayne Morgan

What is the syntax you are using to refer to the combo box on the parent or
subform (depending on which direction you're trying to go)?

To refer to a control on a subfrom from the main form:
NameOfSubformControl.Form!ctlNameOfControl

To refer to a control on a main form from a subform:
Parent!ctlNameOfControl

From a form to a subform on another, separate form:
Forms!frmOtherForm!NameOfSubformControl.Form!ctlNameOfControl

Please paste the Row Source into your message. If the Row Source is the name
of a query, please paste the SQL view of the query into your message.
 
A

Aaron Howe

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.

TIA!
Aaron
 
W

Wayne Morgan

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.
 
A

Aaron Howe

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


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.
Click to expand...


.
Click to expand...
 
A

Aaron Howe

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
Click to expand...
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
Click to expand...
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
Click to expand...
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


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.
Click to expand...


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

Wayne Morgan

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 said:
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
Click to expand...
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
Click to expand...
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
Click to expand...
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


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.


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

Aaron Howe

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


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


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.


.

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


.
Click to expand...
 
A

Aaron Howe

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.
Click to expand...
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


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.


.

.
Click to expand...


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

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filter subform by mainform Combo box 2
Multiple cascading combo boxes all clone each other 3
Combo Cascades 2
Need combo box to refresh when moving to new record 2017 1
Cascading Combo Box 2
Third Cascading Combo Box Displaying too Much Data 2
Cascading Combo boxes using CASE command 12
Cascading Combo Boxes in Main Form 8

Top