Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

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

Status
Not open for further replies.

PeterMAS

Technical User
Apr 3, 2012
18
GT
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.

 
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.
 

"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
 
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.
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top