if exists UPDATE else INSERT

[lloyddobler]

Hi all, I've been scouring net and can't find anything that helps ver well. I'm very weak with VB so need all help I can get. I have a form with 2 fields: CONTROLNUMBER and COMMENTS. What I'd like to do is when a CONTROLNUMBER is entered, the COMMENTS fields populates, which works fine with a DLOOKUP. But now I'd like to add a BEFORE UPDATE event that if the CONTROL NUMBER entered already exists, then UPDATE the current record, otherwise, INSERT a new one. I have no idea where to begin and any help would be appreciated. My table has those 2 fields as the only 2 columns as well, as i'd like the CONTROLNUMBER to be unique/primary key.

 

[MStef]

Hello lloyddobler!
Look at "DemoUpdateInsertA2000.mdb" (attachment, zip).
The Form1 works as you want.
The Form2 works the same but there is an another possibility.
Look at Table1, Query1UPD, Query2UPD, VBA.

 

[lloyddobler]

Mstef, while that does suit the need, I'm looking for something a little more user friendly, in that our end users are not at all tech saavy. With the help of others, I've come up with an onclick event for a command button. I've also kept a DLOOKUP. Here's the code for IF EXISTS UPDATE ELSE INSERT

Private Sub cmdSaveRecord_Click()
If DCount("controlnumber", "tbl_Claim_Overview", "[controlnumber] = '" & Me.[Control Number] & "'") > 0 Then
DoCmd.SetWarnings False
Dim string1 As String
string1 = "UPDATE tbl_Claim_Overview" & _
  "  Set MyTable.claimoverview = '" & Forms![MYFORM]![ClaimOverview] & _
  "' WHERE MyTable.[controlnumber] = '" & Forms![MYFORM]![Control Number] & "'"
 
Debug.Print string1
DoCmd.RunSQL string1
 
  Else
 
DoCmd.RunSQL "INSERT INTO MyTable (controlnumber, claimoverview)" & _
  " VALUES(forms![MyFORM]![control number],forms![MYFORM]![claimoverview])"
End If
DoCmd.SetWarnings True