If record exists, update, Else Insert Into table

[wily_wolf]

Here is the VBA:
Private Sub cmdSaveRecord_Click()
Dim dbs As Database
Dim sSQL As String
Dim strMsg As String

Set dbs = CurrentDb
sq = Chr$(39) 'single quote
sSQL = "UPDATE tblECLUData " _
& "SET (State = " & sq & txtState & sq & "," _
& "ClaimStatus = " & sq & cboClaimStatus & sq & "," _
& "ECLUAnalyst = " & sq & cboLITRep & sq & "," _
& "ECLUMgmt = " & sq & cboECLUMgmt & sq & "," _
& "Insured = " & sq & cboRptType & sq & "," _
& "LitAssoc = " & sq & cboLitAssoc & sq & "," _
& "AsgmntRec = " & sq & txtAsgmntRec & sq & "," _
& "ClmFileSnt = " & sq & txtClmFileSnt & sq & "," _
& "LitAnalystCal = " & sq & txtLitAnalystCal & sq & "," _
& "AsgmntClo = " & sq & txtAsgmntClosed & sq & "," _
& "TMDiaryDate = " & sq & txtDiary & sq & ")" _
& "WHERE ClaimNumber = '" & cboFind.value & "'"

'add/update data to tblECLUData
If DCount("*", "tblECLUData", "ClaimNumber = '" & Me.txtClaimNumber.value & "'") = 0 Then 'record does not exist
'insert record
dbs.Execute " INSERT INTO tblECLUData (State , ClaimNumber , ClaimStatus, ECLUAnalyst, ECLUMgmt, Insured, ReportType, LitAssoc, AsgmntRec, ClmFileSent, LitAnalystCal, AsgmntClo, TMDiaryDate) " & _
" VALUES('" & Me.txtState & "','" & Me.txtClaimNumber & "','" & Me.cboClaimStatus & "','" & Me.cboLITRep & "','" & Me.cboECLUMgmt & "','" & _


Me.txtIns & "','" & Me.cboRptType & "','" & Me.cboLitAssoc & "','" & Me.txtAsgmntRec & "','" & _
Me.txtClmFileSnt & "','" & Me.txtLitAnalystCal & "','" & Me.txtAsgmntClosed & "','" & Me.txtDiary & "')"
Else
'record exists
Debug.Print sSQL
dbs.Execute sSQL, dbFailOnError
End If

strMsg = "Data successfully entered into database."

MsgBox strMsg, vbOKOnly, "Entry Successful"
End Sub

This is what the debug.print displayed:
UPDATE tblECLUData SET (State = 'Alabama',ClaimStatus = 'Closed',ECLUAnalyst = 'Dan Einslinger',ECLUMgmt = 'Allen Shockey',Insured = 'Supplemental Report',LitAssoc = 'Alice Urome',AsgmntRec = '6/27/2016',ClmFileSnt = '6/27/2016',LitAnalystCal = '6/27/2016',AsgmntClo = '6/27/2016',TMDiaryDate = '6/27/2016')WHERE ClaimNumber = '01-234N-958'

The debug.print seems to be displaying all of the information on the form, but it still doesn't update the record. The "Run-time error '3144': Syntax error in UPDATE statement" still comes up.

[davegri]

& "TMDiaryDate = " & sq & txtDiary & sq & ")" _
& " WHERE ClaimNumber = '" & cboFind.value & "'"

Add the space.

[wily_wolf]

I'm still getting the syntax error.

[davegri]

sSQL = "UPDATE tblECLUData " _
& "SET State = " & sq & txtState & sq & "," _
& "ClaimStatus = " & sq & cboclaimstatus & sq & "," _
& "ECLUAnalyst = " & sq & cboLITrep & sq & "," _
& "ECLUMgmt = " & sq & cboECLUMgmt & sq & "," _
& "Insured = " & sq & cboRptType & sq & "," _
& "LitAssoc = " & sq & cboLitAssoc & sq & "," _
& "AsgmntRec = " & sq & AsgmntRec & sq & "," _
& "ClmFileSnt = " & sq & txtClmFileSnt & sq & "," _
& "LitAnalystCal = " & sq & txtLitAnalystCal & sq & "," _
& "AsgmntClo = " & sq & txtAsgmntClosed & sq & "," _
& "TMDiaryDate = " & sq & "#" & txtDiary & "#" & sq _
& " WHERE ClaimNumber = " & sq & cboFind & sq


Let's try it without the parentheses.

[CJ_London]

are you sure all the single quotes are required - assumes all destination fields are text - including TMDiaryDate?

[wily_wolf]

There are multiple date fields. So, if I were to update those fields with "#", then it would look like this.

sSQL = "UPDATE tblECLUData " _
& "SET (State = " & sq & txtState & sq & "," _
& "ClaimStatus = " & sq & cboClaimStatus & sq & "," _
& "ECLUAnalyst = " & sq & cboLITRep & sq & "," _
& "ECLUMgmt = " & sq & cboECLUMgmt & sq & "," _
& "Insured = " & sq & cboRptType & sq & "," _
& "LitAssoc = " & sq & cboLitAssoc & sq & "," _
& "AsgmntRec = " & "#" & txtAsgmntRec & "#" & "," _
& "ClmFileSnt = " & "#" & txtClmFileSnt & "#" & "," _
& "LitAnalystCal = " & "#" & txtLitAnalystCal & "#" & "," _
& "AsgmntClo = " & "#" & txtAsgmntClosed & "#" & "," _
& "TMDiaryDate = " & "#" & txtDiary & "#" & ")" _
& "WHERE ClaimNumber = '" & cboFind.value & "'"

This is the debug.print that I get:

UPDATE tblECLUData SET (State = 'Alabama',ClaimStatus = 'Closed',ECLUAnalyst = 'Dan Einslayer',ECLUMgmt = 'Allen Stenson',Insured = 'Supplemental Report',LitAssoc = 'Alice Novak,AsgmntRec = #6/27/2016#,ClmFileSnt = #6/27/2016#,LitAnalystCal = #6/27/2016#,AsgmntClo = #6/27/2016#,TMDiaryDate = #6/27/2016#)WHERE ClaimNumber = '01-234N-958'

[CJ_London]

other than missing a space before WHERE and a single quote after Novak, that looks like it should work

[wily_wolf]

Right? But for some reason, it doesn't like the UPDATE statement and always points to the "dbs.Execute sSQL, dbFailOnError" after execution.

[davegri]

To debug this, I would try error by elimination.
Try it with just updating STATE. Then when that works, add the next field and so on until you encounter the error. Then you have narrowed down the problem. Fix that and continue with adding fields.

[wily_wolf]

Hey friends, I got in touch with someone who was able to provide a different (and simpler) way of doing what I wanted. This is what I have:

Private Sub cmdSaveRecord_Click()
Dim rst As DAO.Recordset
Dim strMsg As String
Set rst = CurrentDb.OpenRecordset("tblECLUData")

If DCount("ID", "tblECLUData", "ClaimNumber = '" & Me.cboFind.value & "'") = 0 Then 'record does not exist
rst.AddNew
Else
rst.Edit
End If

rst![State] = Me.txtState
rst![ClaimNumber] = Me.txtClaimNumber
rst![ClaimStatus] = Me.cboClaimStatus
rst![ECLUAnalyst] = Me.cboLITRep
rst![ECLUMgmt] = Me.cboECLUMgmt
rst![Insured] = Me.txtIns
rst![ReportType] = Me.cboRptType
rst![LitAssoc] = Me.cboLitAssoc
rst![AsgmntRec] = Me.txtAsgmntRec
rst![ClmFileSent] = Me.txtClmFileSnt
rst![LitAnalystCal] = Me.txtLitAnalystCal
rst![AsgmntClo] = Me.txtAsgmntClosed
rst![TMDiaryDate] = Me.txtDiary


rst.Update

rst.Clone
Set rst = Nothing
strMsg = "Data successfully entered into database."

MsgBox strMsg, vbOKOnly, "Entry Successful"
End Sub
--------------------------------------------------------------------------------------
Like I mentioned, this has been working. However, there are certain instances where it saves a duplicate record. I marked the ClaimNumber field as both "Unique" and "Indexed" and in doing so, was prompted whenever a duplicate record was about to be saved, but is there anything that I could do to prevent this from happening altogether? Any input would be beneficial.

Thank you.

[CJ_London]

but is there anything that I could do to prevent this from happening altogether?

that's what your dcount is supposed to do. However so far as I can see, you are always editing the first record so not sure your code is working as you think it is.

But stick with it. Good luck with your project. Regret I don't have any more time to spend on this