What is the magic code?

[BennyWong]

Hello All,
I am working with Microsoft Access 2000. I am working on a data entry form. The form contains a listbox named ContactType and is set to extended which supports multi-select feature. The ContactType field is on the many-side of the relationship. I have a button named cmdSave to save the user selection. My question is the code does work in saving the selected items into the ContactType table but if the user clicks on the button multiple of times it will also save the selected items multiple times. What will be the code to prevent the user from saving multiple times after they made their inital saved selection. Here is the present code:

Private Sub CmdSave_Click()

DoCmd.RunCommand acCmdSaveRecord
Dim db As Database
Dim rs As Recordset
Dim ctl As Control
Dim itm As Variant

Set db = CurrentDb
Set rs = db.OpenRecordset("tblContactType&quot
Set ctl = Me.ContactID
For Each itm In ctl.ItemsSelected
rs.AddNew
rs!ContactType = ctl.ItemData(itm)
rs!Client_id = Me.Client_id
rs.Update
Next itm
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70
rs.Close
Set rs = Nothing
Set db = Nothing
Set ctl =Nothing

End Sub

Thanks in advance for any advice or support.

 

[danvlas]

Define a unique index on the table based on ContactType and Client_id. Then trap error 3022 (duplicate value in index or primary key). For testing, you can even use On Error Resume Next

Why do you use
DoCmd.RunCommand acCmdSaveRecord
and
DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

They do the same thing...

Good luck

Daniel Vlas
Systems Consultant
danvlas@yahoo.com