Solved Return a specific field from a recordset query

[MarkK]

We are not reusing the recordset. If you provide new parameters to the query, you must open a new recordset. Recordset re-use is not a valuable criteria in solving this problem. Also,

If the class is created every time it is referenced

Which class do you mean?

 

[MajP]

Going back to the original question. One of the easiest ways to create a function that can return multiple values is to use a "user defined type"

Public Type TableNIS
   StartDate As Date
   MRange As Variant
   ENIS As Double
   CNIS As Double
   ZNIS As Double
End Type

Public Function EmpWNIS(inpGross As Double, inpWDate As Date, inpRDate As Variant) As TableNIS
  'Some Code here
  EmpWNIS.StartDate = someValue
  EmpWNIS.CNIS = SomeOtherValue
  EmpWNIS.ENIS = SomeOtherValue2
  EmpWNIS.ZNIS =SomeOtherValue3
End Function

Public Sub Test()
  Dim TN As TableNIS
  TN = EmpWNIS(123, Date, Date = 2)
  Debug.Print TN.CNIS
  Debug.Print TN.ENIS
  Debug.Print TN.ZNIS
End Sub

 

[arnelgp]

you only Open the recordset when the "parameters" changes.
using your function (without using Class).

Public Function EmpWNIS(inpGross As Double, inpWDate As Date, inpRDate As Variant) As Double

    Static dblValue As Double
    Static strValue As String
    Dim strNewValue As String
    
    Dim strPeriodEnd As String
    Dim strWENIS As String
    Dim RetDate As Date

    RetDate = IIf(inpRDate = 0, inpWDate, inpRDate)

    strPeriodEnd = "#" & Format(inpWDate, "m/d/yyyy") & "#"

    ' arnelgp
    ' check if stValue has changed
    '_____________________________
    strNewValue = Format$(RetDate, "yyy-mm-dd") & Format$(inpWDate, "yyyy-mm-dd") & Format$(inpGross, "000000.00")
    
    If strValue <> strNewValue Then
        ' assign strNewValue to strValue
        strValue = strNewValue
        
        strWENIS = "SELECT TOP 1 * FROM tblNIS WHERE [EffDate] <= " & strPeriodEnd & " And WRange <= " & inpGross _
            & " ORDER BY [EffDate] DESC , WRange DESC"
                
        With CurrentDb.OpenRecordset(strWENIS)
        
            If Not (.BOF And .EOF) Then
                If RetDate < inpWDate Then
                    dblValue = 0
                Else
                    dblValue = !ENIS
                End If
            End If
        End With
    
    End If
    EmpWNIS = dblValue
End Function

 

[raziel3]

"user defined type"

I'm sorry @MajP have no idea how to implement this. I just started getting a handle on how to use recordsets, this and class objects are a whole new area to me. Do I put the EmpWNIS.CNIS, EmpWNIS.ENIS equals to the sql?

@MarkK I got it to work, will continue to test.

@bastanu you are right the queries will work very well but I always have this feeling at the back of my mind that someone can accidently delete the query table. That's why I like to hide away the tasks in vba.

 

[bastanu]

If that's the case you have a bigger problem on your hands.... Why would someone delete a query and not a module? Securing the interface to ensure users don't interact with the objects itself is a pretty common topic in this forum. For queries you can prefix their name with usys and they won't be visible until you turn on View System Objects under Options\Navigation (see attached).

Cheers,

 

[MajP]

I'm sorry @MajP have no idea how to implement this. I just started getting a handle on how to use recordsets, this and class objects are a whole new area to me. Do I put the EmpWNIS.CNIS, EmpWNIS.ENIS equals to the sql?

You have lots of other issues going on so you should figure that out first. In general, if you want to have a function that returns more than a single piece of information a User Defined Type is a good approach since a singe variable can hold multiple pieces of information. A UDT is a simple structure that can hold multiple pieces of information of different data types. It is not as robust as a class where you have properties methods and events. You can read up on these in the future, but they are a good arrow to have in your coding quiver and easy to learn.

Here is a simple one. You can drop it in a module and run the test. I define a "Person" type which has and name, age, and sex. Then I can define a variable as a person, and assign the properties

Type Person
  Name As String
  Age As Integer
  Sex As String
End Type

Public Sub Test()
  Dim John As Person
  John.Age = 20
  John.Name = "John Smith"
  John.Sex = "Male"
 
  Debug.Print John.Name & " " & John.Age & " " & John.Sex
End Sub

Then I can make many "Persons",
Dim Joan as Person
Dim X as Person
Dim Fred as person

You then could do stuff like see who is older
If Fred.age > John.Age then Do something

 

[The_Doc_Man]

From your post #15 of this thread:

I'm using the function in a query (qryWklPayroll). Here it is.

OK, NOW we have a problem - You are writing a function in VBA but you are using it in SQL context. SQL context does not understand VBA. It only understands returned values, one per function.

IN A QUERY you cannot ever return a multi-valued thing. In the context of SQL queries, one field has one and only one value at a time. If you wanted two values, you need to call something twice (or call two somethings).

The suggestion of using a call ByRef to allow returning multiple values through implied arguments won't work because you cannot update or modify another returned field in the record in this way.

Using some kind of class module to get you mutliple values won't work either, because in SQL context, you can't USE multiple values. You have no place to put the extras.

A User-Defined-Type won't work because SQL doesn't play well with UDTs. (Not impossible - but here, I think it wouldn't work.)

If you want to return multiple function values from SQL, you absolutely MUST make multiple calls.

Now, if there is a way to make a JOIN work, some flavor of JOIN might be useful. A sub-query might also be useful. But functions in SQL context must ALWAYS be single-valued.

 

[MarkK]

Pat, there is a working copy in the database in post #24 that you can run. If you don't see how that code solves the problem of running the query once to return all the values in the row, there seems slim chance I can make a better description here in language.

Another huge advantage of using a class is that you can debug it using the locals window, which shows you all the current values for all the members. In this case, we've paused execution on the Debug.Print line, and now we can expand the classes that our class exposes. Here we can see the VBA.Collection called m_data in which all the values from the queried row are stored.

In this regard as class module not only self-documents easily in the object browser, and exposes its members to intellisense, it is also dreamy to debug because if you pause code in the consuming routine, you can study your object's state as it changes during execution.

 

[MarkK]

A class is more complicated to write, and more difficult for a non-OOP programmer to understand, but it is simpler to use for all. It is easier to document how it works, because all the resources it consumes are encapsulated within the class's own code. This also make a class more portable and easier to re-use.
I think classes are interesting, and worthy of discussion and greater understanding, so thanks Pat for an interesting exchange on this topic.
Mark

 

[raziel3]

For queries you can prefix their name with usys and they won't be visible until you turn on View System Objects under Options\Navigation.

Thanks @bastanu, This is what I should have been doing all the time. Non-Domain Queries are always faster and now that I know how to hide them, I would start relying on them more.

 

[bastanu]