Using ByRef to update recordset fields-CodePudding

Is there a way to update fields in a recordset via ByRef function? I feel like this example should work, but tbl![Field1] does not get updated by the UpdateTable sub. Can this be done, or do I have to pass the field to a variable first?

Private Sub DeclareTable()
    Dim tbl As Recordset
    Set tbl = CurrentDb.OpenRecordset("Table1")
    tbl.MoveFirst
    
    tbl.Edit
    UpdateTable tbl![field1], 5, 2
    tbl.Update
    
End Sub
Private Sub UpdateTable(ByRef tblField, X, Y)
    tblField = X * Y
End Sub

CodePudding user response：

You may have to be more specific as you wish to use DAO:

Private Sub DeclareTable()

    Dim tbl     As DAO.Recordset
    
    Set tbl = CurrentDb.OpenRecordset("Table1")
    tbl.MoveFirst
    
    tbl.Edit
    UpdateTable tbl![Field1], 5, 2
    tbl.Update
    
    tbl.Close
    
End Sub


Private Sub UpdateTable( _
    ByRef tblField As DAO.Field, _
    ByVal X As Long, _
    ByVal Y As Long)

    tblField.Value = X * Y

End Sub

This runs nicely here.

CodePudding user response：

Your mistake is to use value of field instead of field itself. tbl![field1] returns value, which is no more connected with field. So you have to "pass the field", but you can pass it to function directly.

Private Sub DeclareTable()
Dim tbl As Recordset
Set tbl = CurrentDb.OpenRecordset("Table1")
tbl.MoveFirst

tbl.Edit
UpdateTable tbl.fields("field1"), 5, 2
tbl.Update

End Sub
Private Sub UpdateTable(tblField As Field, X, Y)
    tblField.Value = X * Y
End Sub