Updating Another Table Value Based on Form Value in AfterUpdate 1

[larrydavid]

Hello, I have linked SQL Server 2005 tables in an MS Access 2003 (sp3) database. I've been able to do queries before but am having a problem with this little update:

Option Compare Database

Private Sub Text37_AfterUpdate()
Dim strsql As String
Dim dbs As Database
Set dbs = CurrentDb()

If IsNull(Me.NOTES) = False Or Me.NOTES <> "" Or Len(Me.NOTES) > 0 Then
strsql = "UPDATE dbo_test_Table SET [Test_Notes] = " & Me.NOTES & " WHERE dbo_test_Table.CLMNO = " & Me.Claim_Number.Value & ""
dbs.Execute (strsql)
End If
Set dbs = Nothing
End Sub

In my debug print statement in the watch window the query appears to be formed correctly:
"UPDATE dbo_test_Table SET [Test_Notes] = test2 WHERE dbo_test_Table.CLMNO = 12345678"

Yet, I am getting this error message I've never encountered before:

Run-time error '3061':
Too few parameters. Expected 2.

If someone could please tell me what I might be overlooking I would greatly appreciate it and save a few gray hairs.

Thanks,
Larry

 

[PHV]

Use single quotes for text values:
strsql = "UPDATE dbo_test_Table SET [Test_Notes] = [tt][!]'[/!][/tt]" & Me.NOTES & "[tt][!]'[/!][/tt] WHERE dbo_test_Table.CLMNO = " & Me.Claim_Number.Value & ""

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[larrydavid]

THANK YOU!!!

 

[Andrzejek]

You may get en error if you type something with the ' in Me.NOTES.

To fix this, try:
[tt]
strsql = "UPDATE dbo_test_Table SET [Test_Notes] = '" & [blue]Replace([/blue]Me.NOTES[blue], "'", "''")[/blue] & "' WHERE dbo_test_Table.CLMNO = " & Me.Claim_Number.Value
[/tt]

BTW, if dbo_test_Table.CLMNO is a number, you don't need the last [tt]& ""[/tt]

Have fun.

---- Andy