Overwrite records if exists

[Tep]

Hi,

I have a code that will put values from several unbound textboxes and two listboxes into a table 'dbo_residunorm_nieuw' for all the selected records in the listboxes 'ListStoffen2' and 'ListKAPprodukten'. The code below works fine, but now I would like to add this:
If the selected records in 'ListStoffen2' and 'ListKAPprodukten' do already exists in table 'dbo_residunorm_nieuw' (compare with vor_id and pro_id) I would like to: 1. show a warning message 2. delete the records from 'dbo_residunorm_nieuw' and 3. add the records from the listboxes and textboxes again to 'dbo_residunorm_nieuw'.
But how? I do not have the knowledge to make it myself, so I hope someone can help me.
Thank you in advance!

Tep

Private Sub BTtoevoegen_Click()
Dim strSQL As String
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim ctl As Control
Dim varItem As Variant
Dim varItem2 As Variant
On Error GoTo ErrorHandler
Set db = CurrentDb()
 
Set rs = db.OpenRecordset("dbo_residunorm_nieuw", dbOpenDynaset, dbAppendOnly)
For Each varItem2 In Me.ListStoffen2.ItemsSelected
For Each varItem In Me.ListKAPprodukten.ItemsSelected
rs.AddNew
rs!pro_id = Me.ListKAPprodukten.Column(0, varItem)
rs!vor_id = Me.ListStoffen2.Column(0, varItem2)
rs!ren_produktvorm_cd = Me.TBproduktvorm
rs!ren_dimensie = Me.TBdimensie
rs!ren_waarde = Me.TBwaarde
rs!ren_ingang_dt = Me.TBingangdt
rs!ren_einde_dt = Me.TBeindedt
rs!ren_status = Me.TBstatus
rs.Update
Next varItem
Next varItem2
[SF dbo_residunorm_nieuw].Requery

ExitHandler:
Set rs = Nothing
Set db = Nothing
Exit Sub
ErrorHandler:
Select Case Err
Case Else
MsgBox Err.Description
DoCmd.Hourglass False
Resume ExitHandler
End Select
End Sub

 

[KenHigg]

You should be abe to do a nested recordset loop or if there aren't that many records do a dlookup() function.