Update Query... No error message, but table not updated

[domibud]

Hi...
I'm trying to update several fields in several records in a table.
I use a form for user input.
Here's the code that I use.

Private Sub ReleaseVoucher_Click()
On Error GoTo Err_ReleaseVoucher_Click
Dim SelectVoucher As Control
Dim CurrentRow As Integer
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set SelectVoucher = Forms!AdmSubFrmVoucherRelease!AvailableVoucher
For CurrentRow = 0 To SelectVoucher.ListCount - 1
If SelectVoucher.Selected(CurrentRow) Then
strSQL = "UPDATE [AdmUsrTblBlueBirdMonitoring] SET
[AdmUsrTblBlueBirdMonitoring].[Employee] = 'Me.EmployeeName',
[AdmUsrTblBlueBirdMonitoring].[DateRelease] = #" & Me.DateRelease & "# WHERE
[#2-#8] = 'Me.AvailableVoucher'"
End If
dbs.Execute strSQL, dbFailOnError
Next CurrentRow
Exit_ReleaseVoucher_Click:
Exit Sub
Err_ReleaseVoucher_Click:
MsgBox Err.Description
Resume Exit_ReleaseVoucher_Click
End Sub

When I hit ReleaseVoucher button in form, I don't get any error message.
When I checked the table, the fields are not updates.

Any suggestion on how to make this works?

Thanks for your help.

 

[domibud]

I just realise that's some mistakes in the strSQL statement.
Here's the updated one.

strSQL = "UPDATE [AdmUsrTblBlueBirdMonitoring] " & _
"SET [AdmUsrTblBlueBirdMonitoring].[Employee] = " & _
"'" & Me.EmployeeName & "', [AdmUsrTblBlueBirdMonitoring]." & _
"[DateRelease] = #" & Me.DateRelease & "# WHERE [#2-#8] = " & _
"'" & Me.AvailableVoucher & "'"

Although, I still can't figure it out why the table won't update, though
access didn't display any error messages.

Thanks.

Regards,

Budi.

 

[Duane Hookom]

I would add a line of code to write the SQL to the debug window.

strSQL = "UPDATE [AdmUsrTblBlueBirdMonitoring] " & _
"SET [Employee] = " & _
"'" & Me.EmployeeName & "', " & _
"[DateRelease] = #" & Me.DateRelease & "# WHERE [#2-#8] = " & _
"'" & Me.AvailableVoucher & "'"
Debug.Print

You can then review the code and paste it into a blank query SQL view.
--
Duane Hookom
Microsoft Access MVP

I just realise that's some mistakes in the strSQL statement.
Here's the updated one.

strSQL = "UPDATE [AdmUsrTblBlueBirdMonitoring] " & _
"SET [AdmUsrTblBlueBirdMonitoring].[Employee] = " & _
"'" & Me.EmployeeName & "', [AdmUsrTblBlueBirdMonitoring]." & _
"[DateRelease] = #" & Me.DateRelease & "# WHERE [#2-#8] = " & _
"'" & Me.AvailableVoucher & "'"

Although, I still can't figure it out why the table won't update, though
access didn't display any error messages.

Thanks.

Regards,

Budi.

Hi...
I'm trying to update several fields in several records in a table.
I use a form for user input.
Here's the code that I use.

Private Sub ReleaseVoucher_Click()
On Error GoTo Err_ReleaseVoucher_Click
Dim SelectVoucher As Control
Dim CurrentRow As Integer
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set SelectVoucher = Forms!AdmSubFrmVoucherRelease!AvailableVoucher
For CurrentRow = 0 To SelectVoucher.ListCount - 1
If SelectVoucher.Selected(CurrentRow) Then
strSQL = "UPDATE [AdmUsrTblBlueBirdMonitoring] SET
[AdmUsrTblBlueBirdMonitoring].[Employee] = 'Me.EmployeeName',
[AdmUsrTblBlueBirdMonitoring].[DateRelease] = #" & Me.DateRelease & "# WHERE
[#2-#8] = 'Me.AvailableVoucher'"
End If
dbs.Execute strSQL, dbFailOnError
Next CurrentRow
Exit_ReleaseVoucher_Click:
Exit Sub
Err_ReleaseVoucher_Click:
MsgBox Err.Description
Resume Exit_ReleaseVoucher_Click
End Sub

When I hit ReleaseVoucher button in form, I don't get any error message.
When I checked the table, the fields are not updates.

Any suggestion on how to make this works?

Thanks for your help.

 

[domibud]

Thanks. I know where I make my mistake now.

I would add a line of code to write the SQL to the debug window.

strSQL = "UPDATE [AdmUsrTblBlueBirdMonitoring] " & _
"SET [Employee] = " & _
"'" & Me.EmployeeName & "', " & _
"[DateRelease] = #" & Me.DateRelease & "# WHERE [#2-#8] = " & _
"'" & Me.AvailableVoucher & "'"
Debug.Print

You can then review the code and paste it into a blank query SQL view.
--
Duane Hookom
Microsoft Access MVP

I just realise that's some mistakes in the strSQL statement.
Here's the updated one.

strSQL = "UPDATE [AdmUsrTblBlueBirdMonitoring] " & _
"SET [AdmUsrTblBlueBirdMonitoring].[Employee] = " & _
"'" & Me.EmployeeName & "', [AdmUsrTblBlueBirdMonitoring]." & _
"[DateRelease] = #" & Me.DateRelease & "# WHERE [#2-#8] = " & _
"'" & Me.AvailableVoucher & "'"

Although, I still can't figure it out why the table won't update, though
access didn't display any error messages.

Thanks.

Regards,

Budi.

Hi...
I'm trying to update several fields in several records in a table.
I use a form for user input.
Here's the code that I use.

Private Sub ReleaseVoucher_Click()
On Error GoTo Err_ReleaseVoucher_Click
Dim SelectVoucher As Control
Dim CurrentRow As Integer
Dim strSQL As String
Dim dbs As DAO.Database
Set dbs = CurrentDb
Set SelectVoucher = Forms!AdmSubFrmVoucherRelease!AvailableVoucher
For CurrentRow = 0 To SelectVoucher.ListCount - 1
If SelectVoucher.Selected(CurrentRow) Then
strSQL = "UPDATE [AdmUsrTblBlueBirdMonitoring] SET
[AdmUsrTblBlueBirdMonitoring].[Employee] = 'Me.EmployeeName',
[AdmUsrTblBlueBirdMonitoring].[DateRelease] = #" & Me.DateRelease & "# WHERE
[#2-#8] = 'Me.AvailableVoucher'"
End If
dbs.Execute strSQL, dbFailOnError
Next CurrentRow
Exit_ReleaseVoucher_Click:
Exit Sub
Err_ReleaseVoucher_Click:
MsgBox Err.Description
Resume Exit_ReleaseVoucher_Click
End Sub

When I hit ReleaseVoucher button in form, I don't get any error message.
When I checked the table, the fields are not updates.

Any suggestion on how to make this works?

Thanks for your help.