Solved Return a specific field from a recordset query

[bastanu]

I think using saved totals queries like I showed you would be faster than VBA. I only used one dMax in the qryWklyPayroll and even that could probably be replaced by a TOP 1 subquery. You would need to experiment with your real data of course to accurately judge the performance. But even with the class it will still have to run at least once per record...
Cheers,

 

[Pat Hartman]

@MarkK , please add instructions so that the user and anyone else who reads this understands WHEN he needs to reinitiate the class. Otherwise, he'll get the same values for all 10 employees.

 

[raziel3]

@raziel3
Can you provide some instructions for readers to reproduce the error situation?
What were you doing? What functions/buttons etc?

I was just debugging in the intermediate window using this

Public Function z() As Double

Dim cew As New NIRates

With cew
    .Load 1000, #12/12/2021#
    Debug.Print .CNIS, .ZNIS, .ENIS
End With

End Function

 

[MarkK]

My GetData routine fails to create a new collection. Code should be amended as follows.

Private Sub GetData()
    Dim fld As DAO.Field
   
    Set m_data = New VBA.Collection ' add this line
    With Recordset
        For Each fld In .Fields
            m_data.Add fld.Value, fld.Name
        Next
        .Close
    End With
End Sub

Also, there is no field in the table called 'ZNIS', so you need to amend the property as follows...

Property Get ZNIS() As Double
    If Not IsLoaded Then GetData
    ZNIS = m_data("ClassZ")
End Property

To run the code and see how it works, extract the attached database and check out the mTest module, which has one method that creates an instance, loads it, and returns a result without error.

But again, you can almost certainly get a faster simpler result--if you need multiple rows of return values--by using a query.

 

[Pat Hartman]

If the class is created every time it is referenced, then it isn't reusing the same recordset.

 

[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,

 

[Pat Hartman]

@markk We are not reusing the recordset.

You originally said that the class did NOT rerun the query so with one opening of the recordset, all three values could be obtained using the three gets. Then I pointed out that the user needed to know how to force the class to open a new recordset because otherwise, in a loop, he would get the same values for every item in the loop.

The original question

My question is can I return !CNIS within the function instead of having to do over a whole new function just to get that field?

was regarding NOT using multiple queries to get data from the same row. Two suggestions were made that solved that problem and you said the class module solved it also. I'm just trying to clarify HOW the class module solves the problem. Because if it doesn't, it is just a more complicated solution to a not complicated problem.

 

[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.

 

[Pat Hartman]

A class doesn't help solve this problem. It is just more complicated than the two suggestions which DO solve the op's problem, each of which only take a couple of lines of code that anyone can write and understand.

Access forms/reports are already classes. To add additional classes on top of that, the classes should be used to reuse or simplify code, not complicate it. Just because code exists as a class doesn't make it inherently better than standard code.

 

[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

 

[Pat Hartman]

It runs the query once for the lifetime of the instance, and it returns values for all the fields in the row, three of which are exposed as properties of the class.

What is the lifetime of the instance?
If you call the function from a query and select three different fields, one at a time, is the recordset being opened ONCE or THREE times?
So for each record returned by the query, Is the class opening the recordset ONCE or three times?

 

[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]