Error 424 Object required. OR How do I pass a field to a function in a SQL statement?

[agathogo]

Hi guys,
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) & ";"

specifically 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 defined' error;
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.

 

[vbaInet]

Welcome to AWF

Are you positive your function is actually performing the correct operations? This is because you say that it doesn't throw an error.

Try Me.Manhole_Depth but I don't see how that would make a difference.

 

[agathogo]

Hi
Thanks for your response.
Yes. When I substitute a number for Manhole_Depth in the argument list, the correct cost is calculated and the records are updated with that value.
It seems like this should work I'm just not sure why it doesn't. I pasted it in query design view and supplied values h and d and left manhole_depth (so it would read it from the table) and the query worked. I don't know why it doesn't in the module.

 

[vbaInet]

Can you post a stripped-down version of your db? Here's how:

http://www.access-programmers.co.uk/forums/showthread.php?t=140587

 

[agathogo]

Hi vbaInet,
Unfortunately I can't - company restrictions and all that. I was wondering if I'd have to create a recordset and update that way...
Thanks.

 

[vbaInet]

Alright, use this:

HmhCostCalc(h, dia, Val(Manhole_Depth))

Provide Manhole_Depth is a field that is present in the form's underlying record source. In other words, is Manhole_Depth linked to a text box on your form?

 

[agathogo]

I rewrote the necessary parts (see below) and it performs as expected. I opened 2 recordsets - used one to 'filter' the other using the Seek function. I suspect I could have used Filter function and perhaps had more elegant code but this is working so...
I have posted this just in case it helps someone else out of a similar jam.

strSql3 = "SELECT tbldef_updtMH.Manhole_ID, tbldef_updtMH.Width FROM tbldef_updtMH;"
Set db3 = CurrentDb()
' filter table
Set rsMH3 = db3.OpenRecordset(strSql3)
' table to be updated
Set rsMH4 = db3.OpenRecordset("tbl_MHRecommendation")
strfld = "Manhole_ID"
strfld1 = "Width"
'these are values that depend on user selections
strfld3 = yr
strfld4 = yC
'set bookmark for when NoMatch = true
rsMH4.MoveFirst
bkMark = rsMH4.Bookmark

rsMH3.MoveLast
rsMH3.MoveFirst

Do While Not rsMH3.EOF
rsMH4.Index = "PrimaryKey"
rsMH4.Seek "=", rsMH3(strfld).Value
If Not rsMH4.NoMatch Then
'MsgBox ("found " & rsMH4!Manhole_ID)
'd is a user supplied value sometimes else...
If d = 0 Then d = rsMH3(strfld1).Value
rsMH4.Edit
rsMH4(yr) = h
rsMH4(yC) = HmhCostCalc(h, d, rsMH4!Manhole_Depth)
rsMH4.Update
rsMH3.MoveNext
Else
'MsgBox ("no match")
rsMH4.Bookmark = bkMark
rsMH3.MoveNext
End If
Loop
rsMH3.Close
rsMH4.Close

Thanks for your help.