CODEWITHSUNDEEP
ms-accessms-access-2007vba
Chuck
Chuck

Reputation: 1226

Updating a field for an entire table via VBA

I am trying to add a received date to my table for all the files imported. We receive files and process them up to a week later. I have my import set up and everything but I added a column called "Receive Date". I also added a Date Picker and have it setup in VBA to grab it. I am not sure how to change ALL the records in the table to be the date selected.

Private Sub Command2_Click()
    Dim Rec As String

    Rec = Text0

End Sub

As you can tell I am just starting this but I do not know which direction I should be going from here. I would assume call the record set and table but I am unsure. Any assistance would be greatly appreciated. Thanks in advance

Upvotes: 1

Views: 678

Answers (1)

HansUp
HansUp

Reputation: 97101

Sounds like you want the [Receive Date] in all rows of your table set to the date value selected in your Text0 text box. If that is correct, you can execute a SQL UPDATE statement from Command2_Click().

Private Sub Command2_Click()
    Dim strUpdate As String
    Dim db As DAO.database
    Dim qdf As DAO.QueryDef
    strUpdate = "PARAMETERS which_date DateTime;" & vbCrLf & _
        "UPDATE YourTable" & vbCrLf & _
        "Set [Receive Date] = which_date;"
    Debug.Print strUpdate
    Set db = CurrentDb
    Set qdf = db.CreateQueryDef("", strUpdate)
    qdf.Parameters("which_date") = Me.Text0
    qdf.Execute dbFailOnError
    Set qdf = Nothing
    Set db = Nothing
End Sub

Upvotes: 1

Related Questions