CHanging a row source with code

[Guest_imported]

The following code should limit combo2 list entries depending on what is
chosen in combo1.
(In combo 2 Got focus Event)

Dim str As String
Dim sqlstr As String
str = combo1
sqlstr = "SELECT DISTINCTROW table1 .[field1], table1 .[field2] FROM table1
WHERE (((table1 .[field2])= " & Chr(34) & str & Chr(34) & &quot);"
combo2.RowSource = sqlstr

This works in a form that is NOT a sub-form. However the message:
"The Microsoft Jet database engine cannot find the input table or
query'~sq_<subformname>~sq_c<combo2name>'. Make sure it exists and that its
name is spelled correctly.&quot;

occurs if this code is in a subform (datasheet view) within a main form.
Going into form design view and back onto form view solves the problem, but
we can not work out why this is not working?
Thanks in advance for any help,
Kate [sig][/sig]

 

[JulianG]

In the code make sure you refer to a field in a subform the correct way.
You may need to make the following changes

str = Forms!MainForm!Subform.Form!Combo1

Forms!MainForm!Subform.Form!Combo2.RowSource = sqlstr

Alternatively, you could have in the criteria of combo2s rowsource Forms!MainForm!Subform.Form!Combo1. And on the combo2 gotfocus event one line of code:
Forms!MainForm!Subform.Form!Combo2.Requery

I can be contacted at julian_@hotmail.com

 

[DougP]

I think when its on a su form it cnnot find the combo box.
Where is it on the sub form or the main form?

To refer to a control on a subform.

Me![YOURsubform].Form.combo2.RowSource = sqlstr

Or something similar
[sig]<p>DougP, MCP<br><a href=mailto: dposton@universal1.com> dposton@universal1.com</a><br><a href= > </a><br> Ask me how Bar-codes can help you be more productive.[/sig]