Call a looping function from VBA into MS Access query

Question

I’m really a newbie with VBA. I’ve got a fairly elaborate function for Access VBA that loops through a table, does a bunch of calculations and conditional statements, and returns a value. When I run the function and use “Debug.Print” to get the function values it spits out exactly what it should in the Immediate Window. However, when I try to call the function to an Access query on the same table, the query returns only the last value in the loop.

I built a much simpler looping function to try to figure out what I was doing wrong (see below), and the same thing happens – correct values in the immediate window, only the last value returned in the expression in the query. Can someone tell me what obvious mistake I’m making? If I can fix it here, I ought to be able to fix it in the real project. Greatly appreciate any assistance!

Function TestID()
    Dim d As Database
    Dim r As Recordset
    Dim id As Field
    Set d = CurrentDb()
    Set r = d.OpenRecordset (“tblAllocation”)
    r.MoveFirst
    r.MoveLast
    r.MoveFirst
    Do Until r.EOF
        Set id = r.Fields(“IDAllocation”)
        TestID = id
        Debug.Print TestID ‘This lists all the correct values in the Immediate Window'             
        r.MoveNext
        If r.EOF Then Exit Do
    Loop
    r.Close
    Set r = Nothing
End Function

I just realized I wasn't entirely clear. I want the function to return a value for each record in tblAllocation, and it does this in the Immediate window, but not when I call the function in a query. — CMT
– CMT, Commented Oct 29, 2018 at 20:00
Please be a bit more explicit in what you want. Try adding sample data and expected output. A function can only return a single value or object by design, but that can be a string containing multiple concatenated values, for example. — Erik A
– Erik A, Commented Oct 29, 2018 at 20:51

Andre · Accepted Answer · 2018-10-30 06:35:39Z

1

To return a different value per input row, your function needs to use one or more parameters from the input table/query.

E.g.

Function TestID(ID As Long, someField As Double) As Double
    ' do calculations on parameters here
    TestID = CalcResult
End Function

and pass column values as parameters in the query, e.g.

SELECT foo, bar, TestID([ID], [someField]) AS Calculation
FROM myTable

answered Oct 30, 2018 at 6:35

Andre

27.7k7 gold badges43 silver badges85 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

CMT Over a year ago

Andre, this also helps. I think I know what I have to do with the larger project moving forward. Thanks much.

Lee Mac · Accepted Answer · 2018-10-29 23:04:23Z

0

The function will only ever return one value.

You are printing the value assigned to the TestID symbol for each iteration of the Do loop, but this value is merely overwritten on the next iteration of the loop, it will not be returned by the function.

If you want multiple values to be returned, consider either building & returning an array of values, or append the values to another table - though, this seems rather redundant given that you are sourcing the values from an existing table/query.

answered Oct 29, 2018 at 23:04

Lee Mac

16.1k6 gold badges38 silver badges83 bronze badges

1 Comment

CMT Over a year ago

Thanks much, this helps me understand what I was missing. I'll have to work on an array, I guess. Appreciate the help.

Collectives™ on Stack Overflow

Call a looping function from VBA into MS Access query

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related