Runtime Error 424 in setting rowsource

[Dagon]

I am trying to set the rowsource of a combo box in Visual Basic. This works fine using setvalue in a macro but when I try to do something similar in VBA, I get a runtime error "424, Object required". Unfortunately, my SQL statement is too long to use in a macro.

The code is:

Dim sql_string As String
sql_string = _
"SELECT DISTINCTROW rej_code," & _
"cstr(REJ_CODE) & space(4 - len(cstr(rej_code))) & space(1) & REJ_CODE_TEXT as rej_text " & _
"FROM REJ_CODES WHERE REJ_GROUP_CODE = " & _
[Forms]![CASE OFFICER ENTRY FORM]![PRODUCT_REJ_CODES].[Form]![GROUP_REJ_CODE] & _
" ORDER BY REJ_CODE"
Debug.Print sql_string

[Forms]![CASE OFFICER ENTRY FORM]![PRODUCT_REJ_CODES].[Form]![REJECTION_CODE].[RowSource] = sql_string

 

[jhall156]

Is REJECTION_CODE a subform, if so what is the object immediately in front of it in the expression? JHall

 

[Dagon]

Yes, REJECTION_CODE is a subform. Do you mean the word "form" ? The expression was what I obtained from the MACRO builder. I tried taking "FORM" out but it didn't make any difference. I've also tried:

Me![REJECTION_CODE].[RowSource] = sql_string

and that gets the same error.

 

[jhall156]

It's been a while since I've done this kindof thing but try:

[Forms]![CASE OFFICER ENTRY FORM].[Form]![REJECTION_CODE].[RowSource] = sql_string JHall

 

[Dagon]

No, that doesn't work. It says "could not find the field REJECTION_CODE", presumably because REJECTION_CODE belongs to the subform rather than the main form.

I've managed to get round it by using MACROs. I write part of the SQL statement to a hidden field and then use SetValue to combine the hidden field with the rest of the SQL statement, thereby getting round the limited line length.

 

[jhall156]

What is the subform's name? The expression should read something like this
Forms!YourMainForm.controls("Yoursubformcontrolname&quot.controls("yourcontrolname&quot.RowSource

the "Yoursubformcontrolname" means the name of the actual control on the main form that contains the subform.


JHall