Synchronised combo boxes

I have two combo boxes, WorkType and WorkNat in a subform where WorkNat
contents are dependent on WorkType. The following code works fine when I load
and test the subform on its own, but when I load the form in which the
subform is embedded I get a message "Enter Parameter Value" for Forms!Cases
Subform!WorkType, both when I load the form and when I select a new value for
the WorkType combo box. Also the WorkNat combo box dropdown list is blank.
Why is this happening?

Private Sub WorkNat_AfterUpdate()
Dim strSQL As String
strSQL = "Select WorkNature"
strSQL = strSQL & " from [Work Nature] Where WorkTRef = [Forms]![Cases
Subform]![WorkType];"
End Sub

Private Sub WorkType_AfterUpdate()
Me!WorkNat.Requery
End Sub

Subforms are not part of the Forms collection. But Access should be able to
find the reference on the immediate form anyway.

If WorkTRef is a Number type field, try:
strSQL = strSQL & " from [Work Nature] Where WorkTRef = " & Nz([WorkType],0)
& ";"

If it is a Text type:
strSQL = strSQL & " from [Work Nature] Where WorkTRef = """ & [WorkType] &
""";"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Banterista" wrote in message
...
I have two combo boxes, WorkType and WorkNat in a subform where WorkNat
contents are dependent on WorkType. The following code works fine when I
load
and test the subform on its own, but when I load the form in which the
subform is embedded I get a message "Enter Parameter Value" for
Forms!Cases
Subform!WorkType, both when I load the form and when I select a new value
for
the WorkType combo box. Also the WorkNat combo box dropdown list is blank.
Why is this happening?

Private Sub WorkNat_AfterUpdate()
Dim strSQL As String
strSQL = "Select WorkNature"
strSQL = strSQL & " from [Work Nature] Where WorkTRef = [Forms]![Cases
Subform]![WorkType];"
End Sub

Private Sub WorkType_AfterUpdate()
Me!WorkNat.Requery
End Sub

I'm sorry but I get the same result with your code!

"Allen Browne" wrote:

Subforms are not part of the Forms collection. But Access should be able to
find the reference on the immediate form anyway.

If WorkTRef is a Number type field, try:
strSQL = strSQL & " from [Work Nature] Where WorkTRef = " & Nz([WorkType],0)
& ";"

If it is a Text type:
strSQL = strSQL & " from [Work Nature] Where WorkTRef = """ & [WorkType] &
""";"

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Banterista" wrote in message
...
I have two combo boxes, WorkType and WorkNat in a subform where WorkNat
contents are dependent on WorkType. The following code works fine when I
load
and test the subform on its own, but when I load the form in which the
subform is embedded I get a message "Enter Parameter Value" for
Forms!Cases
Subform!WorkType, both when I load the form and when I select a new value
for
the WorkType combo box. Also the WorkNat combo box dropdown list is blank.
Why is this happening?

Private Sub WorkNat_AfterUpdate()
Dim strSQL As String
strSQL = "Select WorkNature"
strSQL = strSQL & " from [Work Nature] Where WorkTRef = [Forms]![Cases
Subform]![WorkType];"
End Sub

Private Sub WorkType_AfterUpdate()
Me!WorkNat.Requery
End Sub

Add the line:
Debug.Print strSQL

After it fails (asking for the parameter), open the Immediate Window
(Ctrl+G).
Copy the SQL statement.
Create a new query.
Switch it to SQL View (View menu.)
Paste in the SQL statment.
Switch to design view.
See if you can determine what's wrong with the SQL string.

I am assuming here that the code does something more than merely assigning
this to ths strSQL string, i.e. that it actully sets the RowSource of the
combo at some point.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Banterista" wrote in message
...
I'm sorry but I get the same result with your code!

"Allen Browne" wrote:

Subforms are not part of the Forms collection. But Access should be able
to
find the reference on the immediate form anyway.

If WorkTRef is a Number type field, try:
strSQL = strSQL & " from [Work Nature] Where WorkTRef = " &
Nz([WorkType],0)
& ";"

If it is a Text type:
strSQL = strSQL & " from [Work Nature] Where WorkTRef = """ & [WorkType]
&
""";"

"Banterista" wrote in message
...
I have two combo boxes, WorkType and WorkNat in a subform where WorkNat
contents are dependent on WorkType. The following code works fine when
I
load
and test the subform on its own, but when I load the form in which the
subform is embedded I get a message "Enter Parameter Value" for
Forms!Cases
Subform!WorkType, both when I load the form and when I select a new
value
for
the WorkType combo box. Also the WorkNat combo box dropdown list is
blank.
Why is this happening?

Private Sub WorkNat_AfterUpdate()
Dim strSQL As String
strSQL = "Select WorkNature"
strSQL = strSQL & " from [Work Nature] Where WorkTRef =
[Forms]![Cases
Subform]![WorkType];"
End Sub

Private Sub WorkType_AfterUpdate()
Me!WorkNat.Requery
End Sub

Yes it does set the RowSource as you suggest. I've found the problem which is
that you have to fully define the combo control on the subform as a subform
control. That was why it worked as a standalone form but not as a subform.
Thanks for your help as you pointed me in the right direction.

"Allen Browne" wrote:

Add the line:
Debug.Print strSQL

After it fails (asking for the parameter), open the Immediate Window
(Ctrl+G).
Copy the SQL statement.
Create a new query.
Switch it to SQL View (View menu.)
Paste in the SQL statment.
Switch to design view.
See if you can determine what's wrong with the SQL string.

I am assuming here that the code does something more than merely assigning
this to ths strSQL string, i.e. that it actully sets the RowSource of the
combo at some point.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Banterista" wrote in message
...
I'm sorry but I get the same result with your code!

"Allen Browne" wrote:

Subforms are not part of the Forms collection. But Access should be able
to
find the reference on the immediate form anyway.

If WorkTRef is a Number type field, try:
strSQL = strSQL & " from [Work Nature] Where WorkTRef = " &
Nz([WorkType],0)
& ";"

If it is a Text type:
strSQL = strSQL & " from [Work Nature] Where WorkTRef = """ & [WorkType]
&
""";"

"Banterista" wrote in message
...
I have two combo boxes, WorkType and WorkNat in a subform where WorkNat
contents are dependent on WorkType. The following code works fine when
I
load
and test the subform on its own, but when I load the form in which the
subform is embedded I get a message "Enter Parameter Value" for
Forms!Cases
Subform!WorkType, both when I load the form and when I select a new
value
for
the WorkType combo box. Also the WorkNat combo box dropdown list is
blank.
Why is this happening?

Private Sub WorkNat_AfterUpdate()
Dim strSQL As String
strSQL = "Select WorkNature"
strSQL = strSQL & " from [Work Nature] Where WorkTRef =
[Forms]![Cases
Subform]![WorkType];"
End Sub

Private Sub WorkType_AfterUpdate()
Me!WorkNat.Requery
End Sub