MSAccess: Write one value to multiple records

Question

I have a form with multiple records. The records come from a query, the criteria for the query conversely comes from the form. So far, so good. Works just fine.

Now I have a textbox (tbx_Uebergabe_Std) in the header and want its value to be written to a certain field (Uebergabe_SS) of all the shown records when a command button is being clicked (cbt_Ubergabe). What I have tried:

Private Sub cbt_Ubergabe_Click() Me.Uebergabe_SS.Value = Me.tbx_Uebergabe_Std.Value End Sub

This is only going to change the value of the topmost record. The problem here might be that the text field for showing the value from the record is named the same the field itself.

So I thought I could directly write to the records from the query (qry_Dose) via:

Public Sub cbt_Ubergabe_Click() qry_Dose.Uebergabe_SS.Value = Me.tbx_Uebergabe_Std.Value End Sub

But this is only causing a runtime error (424, if you want to know.).

What am I doing wrong and how to fix this?

Answer 1

You can use the forms .RecordsetClone property to iterate through all items:

Public Sub cbt_Ubergabe_Click()
    With Me.RecordsetClone
        .MoveFirst
        Do While Not .EOF
            .Edit
            !Uebergabe_SS.Value = Me.tbx_Uebergabe_Std.Value
            .Update
            .MoveNext
        Loop
    End With
End Sub

Note that this might cause locking conflicts. You can try using .Recordset to avoid them, but then you'll be visibly moving through all items on the form.