code efficiency updating recordset on form close 1

[NewTexican]

I need some advice on the following code. The user checks a box and selects an address in a combo box for various mailouts. Closing the form saves user selection to a table. My problem is when user opens form up and edits checkboxes or address, it tries to save a new record and I get a duplicate entry error. Would someone give me some advice on some logic that would edit a record that already exists instead of saving a new one? Thank you.



'called procedure for adding checks and deleting them from mailout table

Sub form_close()

Dim rs As DAO.Recordset
Dim db As Database
Dim sqls As String
Dim compnum As Long
Dim mlout As Long
Dim mloutyn As Boolean
Dim addtype As Variant
Dim i As Integer

Set db = CurrentDb
Set rs = db.OpenRecordset("Company-Mailout T", dbOpenDynaset)
compnum = Forms![common company].[Company Numeric]

For i = 1 To 6

mlout = i

compnum = Forms![common company].[Company Numeric]
addtype = Me("combo" & mlout + 10).Value
mloutyn = Me("check" & mlout + 10).Value

If mloutyn = True Then
With rs
.AddNew
![Company Numeric] = compnum
![Address Type] = addtype
![company Mailout num] = mlout
![Send Mailout] = mloutyn
.Update
End With
End If

If mloutyn = False Then
sqls = "[company numeric] = " & compnum & " and [company mailout num] = " & mlout
rs.FindFirst sqls
rs.Delete
End If
Next i

rs.Close
Set rs = Nothing
Set db = Nothing
End Sub

 

[PHV]

Perhaps something like this inside the loop (brute force method):
With rs
sqls = "[company numeric] = " & compnum & " and [company mailout num] = " & mlout
.FindFirst sqls
If Not .NoMatch Then .Delete
If mloutyn = True Then
.AddNew
![Company Numeric] = compnum
![Address Type] = addtype
![company Mailout num] = mlout
![Send Mailout] = mloutyn
.Update
End If
End With

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[NewTexican]

works for me. thanks.