Sorry for the length on this one.
OK, so I finally got to it.
All I changed from the code you gave me is from this:
.Open "SELECT intYear FROM tbl WHERE lngProjectID=" & _
lngProjectID & " ORDER BY intYear;"
The bold 'tbl' was changed to Production (the table containing the year data, ProjectID is fk). I also tried it with Project (the table with ProjectID as pk).
My query looks like this:
SELECT Project.ProjectID, Project.ProjectName, YearsNoMO([Production.ProjectID]) AS NoMO
FROM Project INNER JOIN Production ON Project.ProjectID = Production.ProjectID
WHERE Project.PrimaryMO = 1;
The 'PrimaryMO' simply restricts it to Primary MO producers. Once I get this straight, I will be running one for 'PrimaryMO = 2'; bi-product producers.
The query results in an error message:
Run-time error '-2147217904 (80040e10)'
No value given for one or more required parameters.
Clicking "Debug" results in this bit being highlighted:
.Open "SELECT intYear FROM Production WHERE lngProjectID=" & _ lngProjectID & " ORDER BY intYear;"
This is where I changed the original 'tbl' to 'Production' or 'Project' (tried both).
Not sure what to do now. The debugger seems to have locked me out of/frozen everything else. When I try to close the VB window, I get a message that doing this will stop the debugger. Click 'OK' and I get the same error message as above.