CODEWITHSUNDEEP
ms-accessvbams-access-2010
T-Rex
T-Rex

Reputation: 161

Access 2010 Update Table from Form Field using strUpdate

I have a form, by which the user selects the current fiscal month-end date. Frm_SetBuckets

When they user clicks on the check mark, I want that date to update the field CurrentFiscalMonthEnd in the table Tbl_Calendar_SetBucketDates by the following Code: Option Compare Database

Private Sub Image_BucketSelector_Click()
Dim db As DAO.Database
Dim strUpdate As String

'FirstBucketDate = Forms!Frm_SetBuckets!CurrentFiscalMonthEndDate_Selected
'FirstBucketDate = "05/29/2015"
FirstBucketDate = Date

  message = MsgBox("Did you set the Current Fiscal Month-End Date Selector?" & vbCrLf, vbYesNo, "Are you sure?")
  If message = vbYes Then
        DoCmd.SetWarnings False


'strUpdate = "Update Tbl_Calendar_SetBucketsDates SET [CurrentFiscalMonthEnd] = Forms!Frm_SetBuckets!CurrentFiscalMonthEndDate_Selected"
strUpdate = "Update Tbl_Calendar_SetBucketsDates SET [CurrentFiscalMonthEnd] = FirstBucketDate"
Debug.Print strUpdate
Set db = CurrentDb
Debug.Print strUpdate
db.Execute strUpdate, dbFailOnError
Debug.Print db.RecordsAffected & " rows updated"
Set db = Nothing
DoCmd.SetWarnings True
End If

If message = vbNo Then Exit Sub
 MsgBox "You have successfully set the bucket date!"

End Sub

I get a multitude of errors depending on which code I untick. Should I be using some other code to update the field or is there a simple change. Your keen eye is much appreciated. Thanks

Upvotes: 0

Views: 141

Answers (1)

kismert
kismert

Reputation: 1692

Change this line: 

strUpdate = "Update Tbl_Calendar_SetBucketsDates SET [CurrentFiscalMonthEnd] = FirstBucketDate"

To: 

strUpdate = "Update Tbl_Calendar_SetBucketsDates SET [CurrentFiscalMonthEnd] = #" & Format(FirstBucketDate,"mm\/dd\/yyyy") & "#;"

This will format your date properly in Access SQL.

Also, make sure you put an appropriate WHERE clause in this string, or you could wind up updating way more rows than you intended.

Upvotes: 1

Related Questions