Object Required

[bifteki via AccessMonster.com]

I have a subform that used to have it's recordsource -a stored procedure- set
in its property
sheet. The departments of the company appeared inside the subform and, when
someone double
clicked on the name of the department, a small form showed up with the
details of the
department.

Now I have written code, so that the recordsource of the subform is set
through code. However,
the department form won't appear when I double click the name. I get a "Run-
Time Error '424':
Object Required". According to the debugger, the problem seems to be on a
line where I set the
value of a string which is the rowsource for a combo on the small form. The
code is as follows:


Dim rs As String

rs = "SELECT ""tbl_company_places"".""fld_place_id""," & _
"""tbl_company_places"".""fld_place_description""" & _
"FROM ""tbl_company_places"" where fld_company_id =" & fld_company_id.
Value

DoCmd.OpenForm "frm_add_company_department", acNormal, , "fld_department_id="
&

Me.txt_department_id.Value

With Forms!frm_add_company_department
.txt_department_place_id.Value = txt_department_place_id.Value
.txt_company_name.Value = Forms!frm_Companies.fld_company_name.Value
.txt_company_id.Value = fld_company_id.Value
.txt_department_name.Value = txt_department_name.Value
.cmbo_department_place.RowSource = rs
.cmbo_department_place.Value = txt_department_place_id.Value
.txt_department_phone.Value = txt_department_phone.Value
.txt_department_fax.Value = txt_department_fax.Value
.txt_department_e_mail.Value = txt_department_e_mail.Value
.txt_department_notes = txt_department_notes
End With


I noticed that when I make the line where I set rs a comment, the code runs,
until the line which
says '.txt_company_id.Value = fld_company_id.Value'. When I set both lines as
comments, the
small form appears, with the correct details, only without the department
place. So I realised
that the problem is with fld_company_id. This is -obviously- the ID of the
company and is
included in the recordsource.

I also tried to replace the field with the value of a textbox in the larger
form which displays
the ID of the company (txt_company_id.value) but I get the same error.

I guess the change I made affected this part of the code? Could someone give
me a solution?

Thank you and I'm sorry for the -really- long post.

 

[bifteki via AccessMonster.com]

I found the solution to this.
I should have referenced the txt_company_id as part of the frm_companies (the
larger) form -->Forms!frm_companies. txt_company_id.value.
It works just fine.

Thank you anyway.

 

[Douglas J. Steele]

The quotes are definitely incorrect in that SQL. You're also missing a space
between the last field name and the keyword FROM. Try:

rs = "SELECT tbl_company_places.fld_place_id, " & _
"tbl_company_places.fld_place_description " & _
"FROM tbl_company_places where fld_company_id =" & _
Me.fld_company_id

If the table name or field name had spaces in it, you'd use square brackets,
not quotes:

rs = "SELECT [tbl_company_places].[fld_place_id], " & _
"[tbl_company_places].[fld_place_description] " & _
"FROM [tbl_company_places] where fld_company_id =" & _
Me.fld_company_id