Query to count records

[jpkeller55]

I am trying to run an update query to count the number of records in a related table and store the count in the main table. For example: I have a table with inquiries (tblInquiry) with a field ResponseCount. I have another table (tblInquiryReply). I am trying to run the attached code but apparently I am doing something wrong once again.

DoCmd.RunSQL "Update tblInquiry set tblInquiry.ResponseCount = Count(InquiryRespID) from tblInquiryReply where tblInquiry.InquiryID = " & Me.InquiryID

I am getting an error that states: The expresion you entered refers to an object that is closed or dosen't exist. (Run-time error 2467). Is my code way off or am I close?

Thanks.

[slave138]

Try something like this:

DoCmd.RunSQL "Update tblInquiry set tblInquiry.ResponseCount = Count(InquiryRespID) from tblInquiryReply where tblInquiry.InquiryID = " & Chr(34) &  Me.InquiryID & Chr(34)

[jpkeller55]

Thanks Slave for taking a look. I tried the code as you suggested. I am now getting the following error message:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Count(InquiryRespID) from tblInquiryReply

Any thoughts anybody?

[slave138]

I may be missing something but I don't think you can use an aggregate function (Count) as part of an Update query.

[jpkeller55]

OK, I will try some other way to accomplish what I need. Thanks for your input.