Passing Recordset of Form to Function

[Guest]

I have a function declared as:
Public Function F_CalculateSales(rst As Recordset, Week_ID As Integer,
Sales_ID As Integer) As Single

I am trying to have a field on a form with the expression:
=F_CalculateSales(Me.Recordset,[fk_Week_ID],[fk_Sales_ID])
also tried
=F_CalculateSales(Me.RecordsetClone,[fk_Week_ID],[fk_Sales_ID])

This is producing a #Name error, i seem to be unable to pass the recordset
to that public function.

I also tried
=F_CalculateSales(Forms![SalesForm].Recordset,[fk_Week_ID],[fk_Sales_ID])
also tried
=F_CalculateSales(Forms![SalesForm].RecordsetClone,[fk_Week_ID],[fk_Sales_ID])

Which is asking me for the value of Recordset or RecordsetClone...

I would like to be able to pass the recordset from the form containing that
field to this function. Is there any way to do that?

I could open a new recordset within the function with the same query, but
that will evaluate for every row and be extremely non-optimized.

Thanks,
-Ben

 

[Guest]

Create a function in the form that will pass the parameters to the global
function, and call this function from the text box

Text Box
=NewFunctionName()

Function NewFunctionName()
Dim Rs as RecordSet
Set Rs = Me.RecordsetClone
NewFunctionName = F_CalculateSales(Rs,Me.[fk_Week_ID],Me.[fk_Sales_ID])
End Function

Note: I didn't try it, but it's an idea

 

[Jeff L]

You might try using the OnCurrent Event for the form.

There you could put
Me.YourFieldName = F_CalculateSales(Me.RecordSource, Me.[fk_Week_ID],
Me.fk_Sales_ID])