If record exists, update, Else Insert Into table

[wily_wolf]

I need to be able to update a record if it already exists in my table. Currently, if I select the record from the form, update the information and click "Save Record," the data is inserted into the table as a new record, which creates multiples of the same record, with slight variations.

My Access form uses data that is pulled from a linked table, as well as data that the user must input. Once the form is filled out, a "Save Record" button is pressed to save the data into a table specifically created to house this data (tblECLUData). I have figured out how to insert the information on the form into the table, however, if the user tries updating the information on the form and saving/updating that data that is already in the table, it will create a new record rather than updating the record that is already there.

Here is my code below for the event when the button is triggered. This will insert all of the information on the form into tblECLUData:
Private Sub cmdSaveRecord_Click()


Dim dbs As Database
Dim strMsg As String

Set dbs = CurrentDb

'add data to tblECLUData
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 & "')"

strMsg = "Data successfully entered into database."

MsgBox strMsg, vbOKOnly, "Entry Successful"

End Sub

So, I'm just not sure how to incorporate the UPDATE statement into this code. If the record exists, update, else, insert into table as new record. How do I write that?

Any help would be appreciated. Thanks.

[CJ_London]

you haven't supplied enough information so based on guesswork, if one or a combination of fields is supposed to be unique - perhaps claim number? - ensure it is indexed, no duplicates. Then you should get an error on trying to insert a duplicate which you can trap and then execute an update query if required. Also look at the dbfailonerror parameter for the execute command.

alternatively, before saving, use a dlookup or dcount to see if the record already exists and then execute the appropriate query

but you seem to have a fairly complex method for managing a simple situation - why not just load all records from your external table to tblECLUData so all subsequent changes are updates?

[wily_wolf]

The external table is linked to another database whose information is continuously being updated. How much more information is needed? I supplied what I would like to do and even the code that does part of what I want to do. Would it be a matter of an If-Then-Else statement?

The claim number would be the best unique field to index. When working with Access 2010, is it a matter of just selecting the field (in this case, ClaimNumber within tblECLUDate) and checking "Index" under the Field Validation tab?

[CJ_London]

It may be clear to you, but what is not clear is you say

the data is inserted into the table as a new record, which creates multiples of the same record, with slight variations.

and

if the user tries updating the information on the form and saving/updating that data that is already in the table, it will create a new record rather than updating the record that is already there.

which do you want to do? only have a single record for each claimnumber or multiple records? how do you decide when what is in the external data is sufficiently different to require an additional record?

Or are you asking how to write an update query?

Would it be a matter of an If-Then-Else statement?

Yes - which I answered

....Then you should get an error on trying to insert a duplicate which you can trap and then execute an update query if required. Also look at the dbfailonerror parameter for the execute command.

alternatively, before saving, use a dlookup or dcount to see if the record already exists and then execute the appropriate query

[wily_wolf]

I want a single record for each claim number. If the record is already in the table, I want the form to update the data after hitting save. If the record isn't in the table, I want the data to be inserted. For that, I would think an UPDATE statement would be sufficient, but I'm not sure how to construct it in VBA.

[CJ_London]

but I'm not sure how to construct it in VBA

in the same way as you would for an insert query - the syntax is

UPDATE myTable
SET fld1=x, fl2=y, fld3=z

[wily_wolf]

Okay, I understand the syntax behind the UPDATE statement, but how would I incorporate it into what I have now. What would my If...Then...Else statement look like?

Private Sub cmdSaveRecord_Click()
Dim dbs As Database
Dim strMsg As String

Set dbs = CurrentDb

'add data to tblECLUData
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 & "')"

strMsg = "Data successfully entered into database."

MsgBox strMsg, vbOKOnly, "Entry Successful"

End Sub

[davegri]

Brute force. Behind the Save button, do a Dlookup on the claim number. If found do an update, if not do an insert.

[CJ_London]

as suggested in post #2 in more detail

something like

on error resume next
dbs.execute "UPDATE....",dbfailonerror
if err.number<>0 then 'record does not exist
  dbs.execute "INSERT..."
end if

or

if dcount("*","tblECLUData","Claimnumber=" & Me.txtClaimNumber)=0 then 'record does not exist
    'insert record
    dbs.execute "INSERT...."
else 'record exists
    dbs.execute "UPDATE..."
end if

[wily_wolf]

Okay, so I tried the second coding method and I was hitting a snag with my DCOUNT statement having a missing operator. After clearing up that issue (properly, I think), now I seem to be getting an error with my UPDATE statement. Any thoughts?

Private Sub cmdSaveRecord_Click()
Dim dbs As Database
Dim strMsg As String

Set dbs = CurrentDb
'add/update data to tblECLUData

If DCount("[ClaimNumber]", "tblECLUData", "ClaimNumber = '" & Me.txtClaimNumber & "'") = 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
dbs.Execute " UPDATE tblECLUData" & _
"SET (State = me.txtState.Value, ClaimStatus = me.cboClaimStatus.Value, ECLUAnalyst = me.cboLITRep.Value, ECLUMgmt = me.cboECLUMgmt.Value, Insured = me.cboRptType.Value, LitAssoc = me.cboLitAssoc.Value, AsgmntRec = me.AsgmntRec.Value, ClmFileSnt = me.txtClmFileSnt, LitAnalystCal = me.txtLitAnalystCal.Value, AsgmntClo = me.txtAsgmntClosed.Value, TMDiaryDate = me.txtDiary.Value)"
End If

strMsg = "Data successfully entered into database."

MsgBox strMsg, vbOKOnly, "Entry Successful"

' DoCmd.GoToRecord , , acNewRec
' txtClaimNumber.SetFocus
End Sub

[davegri]

  Else 'record exists
dbs.Execute "UPDATE tblECLUData " & _

Remove the space at the beginning and add that space at the end.

[wily_wolf]

I didn't think spaces really mattered in VBA as they aren't really "read" when the code is run. Anyways, I tried what you said and still got the "syntax error is UPDATE statement."

[davegri]

dim sSQL as string
dim sq as string
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 & AsgmntRec & sq & "," _
    & "ClmFileSnt = " & sq & txtClmFileSnt & sq & "," _
    & "LitAnalystCal = " & sq & txtLitAnalystCal & sq & "," _
    & "AsgmntClo = " & sq & txtAsgmntClosed & sq & "," _
    & "TMDiaryDate = " & sq & "#" & txtDiary & "#" & sq & ")"
Debug.Print sSQL
    CurrentDb.Execute sSQL, dbFailOnError

String variables need to be enclosed in quotes. I used the string variable sq as single quotes. Numbers don't need to be enclosed in quotes, but I couldn't tell which of your data were numeric.
Dates in string variables need to be enclosed in Hash "#".
The debug.print is very helpful in debugging your syntax and to see exactly what the SQL data being presented to the interpreter is.

[wily_wolf]

I have tried your suggestions but it doesn't seem to be working. Back to the drawing board.

[davegri]

Please post the SQL that you used AND what the debug.print displayed.