Synchronised combo boxes

B

Banterista

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
 
A

Allen Browne

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] &
""";"
 
B

Banterista

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

Allen Browne said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

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

Allen Browne

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.

Reply to group, rather than allenbrowne at mvps dot org.

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

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

Banterista

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 said:
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.

Reply to group, rather than allenbrowne at mvps dot org.

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

Allen Browne said:
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]
&
""";"

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