Update Table dari form

[TioAdjie]

Dear Access Master,
I have a problem about updating table from a form.
I have a table named TableData and want to update the value in TableData from my form named formA with subform named subformB. In my subformB there textbox named Record No and No. I place the cursor on subformB and click the button, it works until msgbox, but doesn't work on the next code. Do you have any idea what's missing in my VBA code ? Thanks,

Here's my VBA code :

Private Sub Command104_Click()


Dim iStartNo As Long
Dim IStartRecordNo As Long
Dim IPrevRecordNo As Long

iStartNo = [Forms]![FormA]![SubformB].[Form]![No]
IStartRecordNo = [Forms]![FormA]![SubformB].[Form]![RecordNo]
IPrevRecordNo = IStartRecordNo - 1

MsgBox ("" & iStartNo & "")
MsgBox ("" & IStartRecordNo & "")
MsgBox ("" & IPrevRecordNo & "")

DoCmd.SetWarnings False
strSql = "UPDATE TableData SET TableData.RecordNo = '" & IPrevRecordNo & "' WHERE (((TableData.[No])='" & iStartNo & "'));"
DoCmd.RunSQL strSql
DoCmd.SetWarnings True
[Forms]![FormA]![SubformB].[Form].Requery

End Sub

[lfpm062010]

I have tested your code. The only error is that variable "strSql" is not defined.

I did not test the update statement. The syntax looks good.

[amrut]

strSql = "UPDATE TableData SET TableData.RecordNo = '" & IPrevRecordNo & "' WHERE (((TableData.[No])='" & iStartNo & "'));"

The single quote delimeter should be used only when the data type is "Text". You have declared the RecordNo as long, performed calculations too.
Till you get the correct result, remove the WArnings off code. Further, you can use instead of DoCmd.RunSQL

CurrentDb.Execute strSQL,dbfailonError

[lfpm062010]

amrut is right about the text vs number compare. Good catch.

[ssanfu]

Also, be aware that "No" is a reserved word in Access and shouldn't be used for object names....

http://allenbrowne.com/AppIssueBadWord.html