Refering to an Access Field in VBA

Hello Experts:

I am trying to create an Access function to perform a calculation and would like to reduce the number or variables a user will have to assign when entering the function. Only the "forecast" variable changes and the rest stay the same. Is it possible to assign the remaining variables in the VBA code as opposed to entering them when entering the function. For example, I think it should look something like this:

set WAC = currentDb.table1.WAC

The function I have so far is posted below.

Function baseRebate(WAC, rebateBase, rebatePct, qtyDisp, numOfClaims, forecast, utilLICS, rateLICS, adminFee, priceProtPctInc, PP_BaseWac) As Double

'ignore errors

On Error Resume Next

'convert string fields into Double for calculations

rebatePct = CDbl(rebatePct)
qtyDisp = CDbl(qtyDisp)
numOfClaims = CDbl(numOfClaims)
forecast = CDbl(forecast)
utilLICS = CDbl(utilLICS)
rateLICS = CDbl(rateLICS)
adminFee = CDbl(adminFee)
priceProtPctInc = CDbl(priceProtPctInc)
PP_BaseWac = CDbl(PP_BaseWac)
Who is Participating?
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Build a Function that calls your Function:

Function CallMyFunction(vForecast As Double)
  baseRebate Function yourvariableshere
End Function

Your users will on need to supply the single "vForecast" variable.
You could pass the function as recordset as opposed to passing it 12 variables.

Function baseRebate(ByVal rst as dao.recordset, forecast as string)

adminFee = CDbl(rst!adminFee)

QlemoBatchelor, Developer and EE Topic AdvisorCommented:
This question has been classified as abandoned and is closed as part of the Cleanup Program. See the recommendation for more details.
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.