Microsoft Access Tips for Serious Users

Provided by Allen Browne, allenbrowne.com

Returning more than one value from a function

A function can only have one return value. In Access 2, there were a couple of ways to work around this limitation:

• Use a parameter to define what you want returned. For example:

  Function MultiMode(iMode As Integer) As String
      Select Case iMode
      Case 1
          MultiMode = "Value for first Option"
      Case 2
          MultiMode = "Value for second Option"
      Case Else
          MultiMode = "Error"
      End Select
  End Function

• Another alternative was to pass arguments whose only purpose was so the function could alter them:

  Function MultiArgu(i1, i2, i3)
      i1 = "First Return Value"
      i2 = "Second Return Value"
      i3 = "Third Return Value"
  End Function

VBA (Access 95 onwards) allows you to return an entire structure of values. In database terms, this is analogous to returning an entire record rather than a single field. For example, imagine an accounting database that needs to summarize income by the categories Wages, Dividends, and Other. VBA allows you to declare a user-defined type to handle this structure:

    Public Type Income
        Wages As Currency
        Dividends As Currency
        Other As Currency
        Total As Currency
    End Type

You can now use this structure as the return type for a function. In a real situation, the function would look up your database tables to get the values, but the return values would be assigned like this:

    Function GetIncome() As Income
        GetIncome.Wages = 950
        GetIncome.Dividends = 570
        GetIncome.Other = 52
        GetIncome.Total = GetIncome.Wages + GetIncome.Dividends + GetIncome.Other
    End Function

To use the function, you could type into the Immediate Window:

    GetIncome().Wages

(Note: the use of "Public" in the Type declaration gives it sufficient scope.)

Programmers with a background in C will instantly recognize the possibilities now that user-defined types can be returned from functions. If you're keen, user-defined types can even be based on other user-defined types.