Update a set of data values using a textbox in a form

user_10829353 0
Viewed 99

I am using a "Multiple items" form to display the records from a query.

The "Date" field/column is empty.

On the form header I use a textbox where the user has to select or type a date (by default calendar).

On the form header is also a button that I would like to update all the date records with the one typed in TextBox.

One way, that I did not yet test, was to let the button run an update query:
UPDATE Table1 SET MyDate = [Forms]![Form1]![MyTextBox]

Any suggestions for the ideal method?

ms-access vba sql database
1 Answers

As this is Access, use the RecordsetClone that already holds all the records:

Private Sub YourButton_Click()

    Dim Records As DAO.Recordset
    Dim NewDate As Date

    Set Records = Me.RecordsetClone
    
    If IsNull(Me!MyTextBox.Value) Then
        ' Nothing to do.
    ElseIf Recordset.RecordCount > 0 Then
        Records.MoveFirst
        NewDate = Me!MyTextBox.Value
        With Records
            .MoveFirst
            While Not .EOF
                If DateDiff("d", !MyDate.Value, NewDate) <> 0 Then
                    ' Edit the record.
                    .Edit
                        !MyDate.Value = NewDate
                    .Update
                End If
                .MoveNext
            Wend
            .Close
        End With
    End If
    
End Sub
user_35272970
Related