I'd really appreciate some help with the following. The bottom line is the function HmhCostCalc (h,d,depth) returns a cost
which is then inserted into the appropriate field via an update query. The user determines if it's field A and A_Cost, B and
B_Cost etc. They also supply h and d (args for the function). However depth is not a field on the form and the update happens
to a group of assets at a time.
The line with the issue is:
strMH = "UPDATE tbl_MHRecommendation INNER JOIN tbldef_updtMH ON tbl_MHRecommendation.Manhole_ID = tbldef_updtMH.Manhole_ID "
& "SET " & yr & " = '" & h & "'," & yC & " = " & HmhCostCalc(h, dia, Manhole_Depth) & ";"
Q: How do I pass a field, Manhole_Depth, from the table tbl_MHRecommendation to the function HmhCostCalc?
If I use it as pasted above, I get no error but the function returns a cost of 0 (meaning the value passed is Null);
If I use tbl_MHRecommendation.Manhole_Depth, I get err 424 'Object required';
If I enclose it in  -> [tbl_MHRecommendation].[Manhole_Depth] - the module will not compile and I get - 'external name not
if I substitute a number ex. 5, the correct costs are calculated and the records are updated.
Therefore, I think, my problem lies in how to pass that value in that field to the function when I call it here.
I did make the assumption that a sql statement in a module would work like it does when in a query design window - as long as
the field name is valid and I supply the correct args, the update will occur. Is this incorrect?
Any pointers, corrections, advice etc is appreciated and thank you very much for your time.