vba ADO question

[TomL]

I'm having a problem doing an update query using vba inside an access program. I have an unbound form and ran into a problem trying to do an up date of a table data. I tried to use a copy of code I found on the web, which first does a select query of the record based a primary key. The code is below,but I get an error condition when the recordset opens. The error description is as follows: "Syntax error in FROM clause."
Oddly enough the value for sqlStr, select * from FinancialCase where ID = 16, works fine if I copy and paste the value into query builder. If I change the syntax and actually type out the sql update ie update FinancialCase set xxx= yyy where ID = currentID, it works fine. Can someone point out where the syntax "select * from FinancialCase where ID = 16" is in error? Or if you have another way to do the update let me know.

Public Function EditNameForm() As Boolean
Dim sqlStr As String

On Error GoTo ErrorCheck
If Not GetConnection Then
Exit Function
End If

sqlStr = "select * from FinancialCase where ID = " & CurrentID
rs.Open sqlStr, Conn, adOpenKeyset, adLockOptimistic, adCmdTable
rs("DeputyID") = clsfinancial.DeputyID
rs("LastName") = clsfinancial.LastName
rs("FirstName") = clsfinancial.FirstName
rs("County") = clsfinancial.County
rs("PublicAssistance") = PublicAssistance
rs("TANF") = clsfinancial.TANF
rs("SANF") = clsfinancial.SANF
rs("Medicaid") = clsfinancial.Medicaid
rs("AssistanceOther") = clsfinancial.AssistanceOther
rs("AssistanceOtherAmt") = clsfinancial.AssistanceOtherAmt
rs.Update
EditNameForm = True
rs.Close

Exit Function
ErrorCheck:
MsgBox "Error In Editing Name Record. " & Err.Description, vbOKOnly, "Error"
EditNameForm = False

End Function

 

[MarkK]

Your adCmdTable option instructs the Open method to interpret the Source parameter as the name of a table, which is not the case here. Remove that parameter and the error will go away.

rs.Open sqlStr, Conn, adOpenKeyset, adLockOptimistic

Cheers,

 

[TomL]

Many thanks for your help.

Tom