Form add problems

[socko139]

I created a form which allows me to add multiple systems to one Work center.. I dreated an add button that attaches each system with a work center ID See code below:

Private Sub cmd_Add_Click()
'Define all of your variables dim = dimension - old termonolgy
Dim Sys As Integer
Dim sql As String
Dim source As String

'turn off warnings so it does not tell you it is adding rows to a table
DoCmd.SetWarnings False


'This code checks to see that the user has selected a name before trying to add an system
If lbSys.ItemsSelected.Count = 0 Then
MsgBox "Please select a system"
Me.lbSys.SetFocus
End If

'Create an insert query to write the new record to the xref table
sql = "insert into tbl_WC_Sys_xref (WC_ID, Sys_ID) values (" & Me.WC_ID & ", " & Me.lbSys & ")"

'run the query you just wrote
DoCmd.RunSQL sql

'Reset the values in the list box to display the new entries
'source = "SELECT distinct [tbl_systems].[Sys_ID], [tbl_systems].[Sys_Name] FROM tbl_systems, tbl_general_information, tbl_WC_Sys_xref WHERE [tbl_systems].[Sys_ID]=[tbl_WC_Sys_xref].[Sys_ID] And [tbl_WC_Sys_xref].[WC_ID]=" & Me.WC_ID

'Set the source of the listbox to the query you just wrote
'lb_assigned.RowSource = source
lb_assigned.Requery
'turn the warnings back on
DoCmd.SetWarnings True

End Sub

My question is what code would I write to prevent multiple records. Example If I sit on system 1 and click add to assign it in my table...when go back to my table it says
Wc_ID Sys_ID
1 1
1 1
1 1
1 1

I would like it to just be listed once..Any suggestions?

Thanks

 

[KevinM]

Why don't you just create a one to many relationship between the two tables and enforce referrencial integrity rather than write several lines of code.

HTH