Problem with Duane Hookom's Concatenate function in a textbox on a sub 1

[PeterMAS]

Access 2003, XP
I used Duane Hookom's Concatenate function to list a single field from a table in a text box on my main form and it works perfectly.

FAQ701-4233

I decided to put it on a subform and it seemed to work, but when I moved the main form to another record, the text box didn't update. I saw that I hadn't linked the subform to the mainform and did so. Now that I had linked the forms, I received an error message:

Run time error '3075':
Syntax error (missing operator) in query expression 'RecID ='.

This is the function on the control source of the text box on the subform:
=Concatenate("SELECT Cat FROM qlkRecCat WHERE RecID =" & [RecID])

This is the query qlkRecCat used in the concatenate function above:
SELECT tjnRecCat.RecID, tjnRecCat.CatID, tlkCat.Cat
FROM tlkCat INNER JOIN tjnRecCat ON tlkCat.CatID = tjnRecCat.CatID
WHERE (((tlkCat.CatClsID)=4));

RecID: Long Integer
CatID: Integer
Cat: Text
CatClsID: Byte

The fields on the join table on the subform are: RecID, Cat ID

The master/child link between the forms is on RecID

Any suggestions would be appreciated.

 

[dhookom]

If there is a possibility that RecID might be null the try:

=Concatenate("SELECT Cat FROM qlkRecCat WHERE RecID =" & Nz([RecID],0))

Duane
Hook'D on Access
MS Access MVP

 

[PeterMAS]

Magic Duane. Obviously the Access fire brigade works all hours.

I don't quite understand why the RecID would be null, but you got it. It works perfectly now.

Thank you.

 

[Andrzejek]

"why the RecID would be null"
I would check the seetings on that field in your table. It looks like it may be a Primary Key field and if you allow NULL in there, then that's what you get.

Have fun.

---- Andy

 

[PeterMAS]

Thanks Andy. This was months ago, but I will reply to your post just in case someone encounters the same error. I never allow nulls in Primary key fields but the original SQL worked perfectly until I moved it from the main form to a subform. I suspect the main form record set hadn't loaded and that caused the error.