Solved Problem with using ByRef with a recordset field (1 Viewer)

[nrgins]

So, I have a function that's meant to emulate += in Access to add a value to another value without having to repeat the first value. It's a simple function:

Public Function pe(ByRef varBaseAmt As Variant, varNewAmt As Variant)

    varBaseAmt = Nz(varBaseAmt, 0) + Nz(varNewAmt, 0)

End Function

The function works fine except when I pass it a recordset field object. E.g.:

Dim rs As Recordset
   
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
   
rs.Edit
pe rs!Field1, 1
rs.Update

This doesn't throw an error, but it simply doesn't work.

If I step through the pe function, then varBaseAmt is indeed updated with the correct total. But it's not passed back to the calling function.

I would think that since varBaseAmt was ByRef variable of type Variant that it would work with a recordset field. Trying to understand why this doesn't work.

Thanks.

 

[The_Doc_Man]

As an experiment, try substituting the actual reference in your code.

...
rs.edit
rs!Field1 = rs!Field1 + 1
rs.update

See if that works for the same recordset and same table. Be sure that you DON'T have error notifications disabled.

 

[Gasman]

So, I have a function that's meant to emulate += in Access to add a value to another value without having to repeat the first value. It's a simple function:

Public Function pe(ByRef varBaseAmt As Variant, varNewAmt As Variant)

    varBaseAmt = Nz(varBaseAmt, 0) + Nz(varNewAmt, 0)

End Function

The function works fine except when I pass it a recordset field object. E.g.:

Dim rs As Recordset
 
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
 
rs.Edit
pe rs!Field1, 1
rs.Update

This doesn't throw an error, but it simply doesn't work.

If I step through the pe function, then varBaseAmt is indeed updated with the correct total. But it's not passed back to the calling function.

I would think that since varBaseAmt was ByRef variable of type Variant that it would work with a recordset field. Trying to understand why this doesn't work.

Thanks.

Try using .Value for the field.

 

[jdraw]

I mocked it up with the following. I has a table1 with a numeric field "header"

Public Function pe(ByRef varBaseAmt As Variant, varNewAmt As Variant) As Variant

    pe = Nz(varBaseAmt, 0) + Nz(varNewAmt, 0)
Debug.Print "PE" & "  " & pe
End Function

Sub testpe()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
rs.MoveLast
Debug.Print "A" & vbTab & rs!header
rs.Edit
rs!header = pe(rs!header, 19)
rs.Update
Debug.Print "B" & vbTab & rs!header
End Sub

Result:
A 9
PE 28
B 28

I did not get this to work:

Sub testpe()
Dim rs As DAO.Recordset
  
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
rs.MoveLast
Debug.Print "A" & vbTab & rs!header
rs.Edit
 pe rs!header, 228 '<=================
rs.Update
Debug.Print "B" & vbTab & rs!header
End Sub

Result:
A 10
PE 238
B 10

 

[nrgins]

As an experiment, try substituting the actual reference in your code.

...
rs.edit
rs!Field1 = rs!Field1 + 1
rs.update

See if that works for the same recordset and same table. Be sure that you DON'T have error notifications disabled.

Yes, that's what I ended up doing. And, yes, it works if you do it the standard way.

And, no, error notification's in place. In fact, both functions have error handlers in place. I just removed them for posting here.

 

[nrgins]

Try using .Value for the field.

Same results.

 

[nrgins]

I mocked it up with the following. I has a table1 with a numeric field "header"

Public Function pe(ByRef varBaseAmt As Variant, varNewAmt As Variant) As Variant

    pe = Nz(varBaseAmt, 0) + Nz(varNewAmt, 0)
Debug.Print "PE" & "  " & pe
End Function

Sub testpe()
Dim rs As DAO.Recordset

Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
rs.MoveLast
Debug.Print "A" & vbTab & rs!header
rs.Edit
rs!header = pe(rs!header, 19)
rs.Update
Debug.Print "B" & vbTab & rs!header
End Sub

Result:
A 9
PE 28
B 28

I did not get this to work:

Sub testpe()
Dim rs As DAO.Recordset
  
Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
rs.MoveLast
Debug.Print "A" & vbTab & rs!header
rs.Edit
pe rs!header, 228 '<=================
rs.Update
Debug.Print "B" & vbTab & rs!header
End Sub

Result:
A 10
PE 238
B 10

Yes, it works if you return a value from the function. But then that defeats the purpose of using the function in the first place, since you're still repeating the value to be added to twice.

 

[jdraw]

nrgins,
Agreed. But even without the returned value, I could not get the "header" value to change??

 

[Gasman]

Differences Between Modifiable and Nonmodifiable Arguments - Visual Basic

Learn more about: Differences Between Modifiable and Nonmodifiable Arguments (Visual Basic)

learn.microsoft.com

 

[nrgins]

nrgins,
Agreed. But even without the returned value, I could not get the "header" value to change??

You had changed the function so that it didn't increase the value of the first parameter, but only added them together as a return value. The original function simply had:

varBaseAmt = Nz(varBaseAmt, 0) + Nz(varNewAmt, 0)

 

[nrgins]

Differences Between Modifiable and Nonmodifiable Arguments - Visual Basic

Learn more about: Differences Between Modifiable and Nonmodifiable Arguments (Visual Basic)

learn.microsoft.com

Thanks! That certainly addresses the issue. The only is, that article lists fields as modifiable, and only lists "Read-only variables, fields, and properties" as non-modifiable. The field I passed was definitely modifiable.

??

 

[ebs17]

rs.Edit
rs!Field1 = pe(rs!Field1, 1)
rs.Update

You now really have to assign a value to a table field. The memory structure in the database engine is different than just a pointer to an area in memory, which is now a variable.

 

[nrgins]

You now really have to assign a value to a table field. The memory structure in the database engine is different than just a pointer to an area in memory, which is now a variable.

That makes sense. I'm curious, though, what you think of the article that gasman shared, above. Unless I'm reading it wrong, it seems to say that fields are modifiable. What is your take on it?

 

[ebs17]

First of all, database world (tables, queries, anything related to SQL and managed by a database engine) and VBA are very different things. You can't mix and match as you like.

As a special case for Jet-SQL, it is possible to make VBA functions evaluable for the DB machine via the so-called Expression Service, but this remains a limited space. SQL and therefore the recordset cannot do anything with variables and cannot read changed values from memory.

My counter question would be: What do you ACTUALLY do?
At the moment it looks like experimental programming without the inclusion of basic knowledge.

 

[MajP]

Unless I'm reading it wrong, it seems to say that fields are modifiable. What is your take on it?

That is not ado or dao "fields". They are clearly using that term to be more generic. Especially since the source has nothing to do with Access or databases. They are not going to call out all possible object modules and exception.

Fields (member variables of modules, classes, and structures)

Also what are you really trying to accomplish? I assume this is a generic question not a specific example. Seems to me you are writing sloppy code in an attempt to be clever. There is a time and place for variants, but this is just sloppy. You are guessing at how the reference variables will get cast. Write clear simple functions using strong type whenever possible.

 

[nrgins]

@ebs17:

Ouch! OK, never mind.

Have a nice day!

 

[nrgins]

Also what are you really trying to accomplish? I assume this is a generic question not a specific example. Seems to me you are writing sloppy code in an attempt to be clever. There is a time and place for variants, but this is just sloppy. You are guessing at how the reference variables will get cast. Write clear simple functions using strong type whenever possible.

I used variants to account for the possibility of Null values being passed.

And not trying to be clever. Just trying to save keystrokes.

Sorry you think my coding is sloppy.

 

[CJ_London]

There is no need to specify ByRef as that is the default. Also Variants can only be of types string, date, time, Boolean, or numeric. Not a field which is what you are passing. See this link

Understanding Variants (VBA)

Office VBA reference topic

learn.microsoft.com

Just curious as I don't see the purpose - what if you were to use the following?

Public Function pe(varBaseAmt As DAO.Field, ByVal varNewAmt As Variant)

Just trying to save keystrokes.

have you considered the optional parameter?

Public Function pe(varBaseAmt As DAO.Field, Optional varNewAmt As Long=0)

 

[nrgins]

Variants can only be of types string, date, time, Boolean, or numeric. Not a field which is what you are passing.

Yes, of course. I guess I just didn't think it through.

I mean, I would never set an object to a variant datatype within a procedure. I would use an object variable.

But somehow the "ByRef" threw me off, and I didn't see it the same as setting a variable. But it is, since the parameter is a variable.

So I think that explains it. Dumb mistake. Thanks!

So, apparently, when I passed the field to the variant parameter, Access assumed I meant to pass the field's value, and so the value was passed, not the object itself. And then the value was modified within the procedure. But since the value wasn't a variable, there was nothing modified on the calling side. Same as though I'd just passed the number 1234 rather than a variable. So that makes sense. Thanks!

Just curious as I don't see the purpose

The purpose was just to save keystrokes. I got tired of doing things like:

intSomeVariable = intSomeVariable + 1

instead of just being able to do:

intSomeVariable += 1

as you can do in most other languages. So created a function that emulates the += operator.

And it works fine. For the most part. Just not with fields.

have you considered the optional parameter?

Public Function pe(varBaseAmt As DAO.Field, Optional varNewAmt As Long=0)

I don't believe I would need an optional parameter. I believe that using a variant for the NewAmt parameter would work fine, along with Nz() to convert to 0 if needed.

But you gave me an idea here. I could just create a separate function that takes an object as the first parameter and works the same way. That would definitely work.

Like I said, I'm just tired of having to repeat variable names when adding values to them. So I may just do that.

So I think that resolves it. Thank you for your input. And thank you for being kind and helpful, rather than insulting. It's appreciated!

 

[ebs17]

Set rs = CurrentDb.OpenRecordset("Table1", dbOpenDynaset)
rs.Edit
   ' ...
rs.Update

Are you also aware that only one record is edited, namely the first of the recordset created?