Update record count from one table into recordcount field in another table

[hfreedman1957]

Hi,
I have a Project table, and for each project record identified by Project_ID, there are numerous records in an Services table, relationship by Project_ID key.


I'm hoping I can loop through Project table, and for each record, perform an OpenRecordset operation on the Services table based on a Select statement that return the set of records for that project. All I need is the count of the number of Services records returned and to update a "recordcount" field in the Project table record. Please help! I'm still a bit of a newbie for Access VBA but am usually able to look at other code, understand it, and modify it for my needs. I'm currently doing this manually by running a query with a counter and entering the count into the project table record. Any help would be appreciated! Thanks so much!

[orange]

And if you add a few Services to a Project, the Count will be incorrect.

Suggest you look at your relationships/design.

1 Project may have 1 or many Services (a 1 to many set up)

Then use form/subform construct where form relates to the 1 side and subform represents the Services for that Project.

[hfreedman1957]

I should have explained a little further. You are correct of course. I will always run this "job" just before I run a report I have, to ensure the record count is accurate. The report only pulls information from the project table. I want to keep it that way as the report is complex (for me) by looping through the project records and generating an email to the project manager for each project. Attached to the email is a PDF format report, which includes information for just that one project taken entirely from the project record fields, then repeating for each project. I now need each project record to include the services record count. I already have the one to many relationship set up require for all the other functionality. I hope that provide sufficient rational for my approach. Please help!

[hfreedman1957]

Can someone debug this SQL statement please....

strSQL = "SELECT Project_ID, Environment_ID"
strSQL = strSQL & " FROM tblActions"
strSQL = strSQL & " WHERE Project_ID = '" & "rsProjects.Project_ID'" & " AND " & "Environment_ID = 1"
Set rsActions = CurrentDb.OpenRecordset(strSQL, dbOpenSnapshot)

rsProjects is from an earlier OpenRecordset statement. I get very confused with the quotes :-)
Thanks!

[orange]

My guess is that rsProjects.Project_ID is a number, so you do not need the single quote ' for numbers

In fact you do not need rsProjects.Project_ID within quotes of any sort since your want Access to render the value.

strSQL = strSQL & " WHERE Project_ID = '" & "rsProjects.Project_ID'" & " AND " & "Environment_ID = 1"

becomes

strSQL = strSQL & " WHERE Project_ID = " & rsProjects.Project_ID & " AND Environment_ID = 1"

Suggestion though is to always do a debug.print strSQL to see/check how Access has interpreted your code before running it.

[hfreedman1957]

Perfect!!! Thanks so much. That put me past the error and I now have the code working perfectly, updating the counts exactly. Thanks so very vey much!

[orange]

You're welcome. Happy to help.