Referring to a field in a subform

[Richard1941]

I'm trying to use cascading combo boxes, where the second box is on a subform. I've found several examples of code to put on the first box after update action that require setting the record source for the second box. However, every structure I've tried for referring to the second box fails with a compile error unable to find the control. Here is the current code:

Private Sub ServiceRequested_AfterUpdate()
Dim strSQL As String
strSQL = "SELECT " & Me!ServiceRequested & " FROM LUtblMemberServiceCategories"
[tblRequestVolunteers subform.Volunteer].RowSourceType = "Table/Query"
[tblRequestVolunteers subform.Volunteer].RowSource = strSQL

End Sub

It fails on the line that sets the row source type.

Suggestions?

 

[pbaldy]

This should help:

http://access.mvps.org/access/forms/frm0031.htm

 

[Richard1941]

I changed the first tblRequest line to:

Me![tblRequestVolunteers subform].Form.RowSourceType = "Table/Query"

Now I get the following error message: Application defined or object defined error.

What now?

 

[John Big Booty]

I'm not sure that this line in your code is required;

Me![tblRequestVolunteers subform].Form.RowSourceType = "Table/Query"

It's certainly not used in this example

 

[Richard1941]

Doesn't matter. I took it out and got the same error message on the next line.

 

[John Big Booty]

Can you post a copy of your DB?

 

[ChrisO]

A Form does not have a RowSourceType property.

Aren’t you missing the name of the combo box after the Form?

Chris.

 

[John Big Booty]

A Form does not have a RowSourceType property.

Aren’t you missing the name of the combo box after the Form?

Chris.

Doh !!!!

 

[Richard1941]

You're right, of course. I changed the offending statement to:

Me![tblRequestVolunteers subform].Form!Volunteer.RowSource = strSQL

Now the error message says "Object doesn't support this property or method."

Still stymied.

 

[ChrisO]

Is Volunteer the name of the combo box or the field it is bound to?

Chris.

 

[Richard1941]

Yes, combo box.

 

[ChrisO]

Please upload a small demo of the problem in Access 2003.

Chris.

 

[Richard1941]

I don't have 2003. I have 2010.

 

[ChrisO]

Well, I can offer no further assistance.

Chris.

 

[John Big Booty]

I don't have 2003. I have 2010.

You should still be able to save back to '03, but upload a '10 version and I have a look at it when I get to a machine with '10 on it

 

[Richard1941]

Thanks for your help, but I solved the problem by renaming the subform without the space so I would not need the brackets in the expression.

 

[John Big Booty]

Yep; a good naming protocol makes life so much easier