Solved Virtual link between variable declared as field and recordset field?

[Gasman]

No sure how to call the title TBH, so will try and explain.

In attempting to help a user on another forum I find he is doing this.
Declaring a variable as a Field called Fld12
Opening a recordset and setting that Field variable to a recordset field.

He then issues an rs.Edit
He then overwrites this variable field Fld12
Then he issues an Rs.Update

He expects the new value of Fld12 to be in the recordset field that he initially set the Fld12 at the start, or that is my interpretation, otherwise I cannot see how it can be done.

Nowhere does he assign the value of Fld12 to the recordset field before the rs.Update.
He states that similar/identical code works fine.?

I myself have never even seen this.
So is this even possible.?

For complete disclosure the thread is at https://eileenslounge.com/viewtopic.php?f=29&t=39787&p=308456#p308456

TIA

 

[The_Doc_Man]

OK, a field variable is a type of object. So setting a field variable to something that IS a field is fine. But this is not a LET statement, it is a SET statement. So it is an address of something that is ephemeral.

I believe that the value would be in place only for as long as the recordset is open. Once the code is in its shutdown phase, that value - which depended on the recordset being open - is now de-referenced. Since all the code in question vanishes and the variables as well, I would say that there is no easy test. But have him put a breakpoint at the end of the code segment that was shown for date 20-Jun-2023 16:38

Rs.MovePrevious
Next I
Set Rs = Nothing
Set db = Nothing
End Function

Set a breakpoint on the SET RS = NOTHING and at that point do some DEBUG.PRINT commands in the Immediate window to determine what values are still set. Then single-step and try again.

 

[Gasman]

Well he is saying that fld12 has a value before the update, but it is not being passed to the rs field?
All this is new to me, I have always used a standard variable and assigned the recordset field to that.

 

[ebs17]

So is this even possible.?

Fast Code Sample

While looking for fast code i found this one sitting in a forgotten directory on my NAS. It has a different way of addressing a field in a recordset. I don't know where i got it from but here it is: tblStatus had 85K records in the sample database. DoitSlow took 1000msec DoitFast took 200msec...

www.access-programmers.co.uk

 

[Gasman]

So no .edit or .update required?

Edit: I just tried the code below and had to add an Edit and Update ?

Sub UpdateRst()
Dim db As DAO.Database
Dim fld As DAO.Field
Dim rst As DAO.Recordset

Set db = CurrentDb
Set rst = db.OpenRecordset("Select Description from TestTransactionsDelete")

Set fld = rst!Description
Do Until rst.EOF
    rst.Edit
    fld = "Test Description"
    rst.Update
    rst.MoveNext
Loop
Set fld = Nothing
Set rst = Nothing
Set db = Nothing
End Sub

 

[Gasman]

Well learnt something new today.

 

[MajP]

fld12 has a value before the update, but it is not being passed to the rs field?

I think the reason this may be confusing is by omitting the default "value" property it hides what is going on. Would you not expect the textbox to have the value 123?

dim x as textbox
set x = me.Sometextbox
x = 123

Does this make more sense?

dim x as textbox
set x = me.Sometextbox
x.value = 123

If you wrote this fully declared would it make more sense?

Fld12.value = Abs(Fld5) *...

Fld12 is a pointer to the field, so it is "passed" to the rs field since it is basically the same as the field.

 

[Gasman]

Yes, I have twigged that now with my little block of code I posted.
I have never done it that way TBH, I have used what I thought was the standard method as per this link

Modify an existing record in a DAO Recordset

Office VBA reference topic

learn.microsoft.com

 

[MsAccessNL]

Fast Code Sample

While looking for fast code i found this one sitting in a forgotten directory on my NAS. It has a different way of addressing a field in a recordset. I don't know where i got it from but here it is: tblStatus had 85K records in the sample database. DoitSlow took 1000msec DoitFast took 200msec...

www.access-programmers.co.uk

Funny, I had to think about the exactly the same post. Two minds with the same…

 

[The_Doc_Man]

Using pointers, you evaluate once and re-use many times in a loop. You save the repeated evaluation of the pointers, names, or indexes. Searches and indexing operations are pains in the toches so anything you can do to avoid the searches is a winning situation. Just remember that if the recordset to which your pointers pointed suddenly becomes closed, the pointers are now dereferenced - which would probably not be good for your program.

 

[Gasman]

Well there is an an easier method to **** that up, just do not Dim your variable objects.

That was the cause of that user's issue, in conjunction with no Option Explicit.