many to many relationship DB advice

[Kovacs]

I have a many to many relationship database using 3 tables:

units - tab1(junction table) - addons

A unit can have many addons and each addon can be related to many units.

Now, on the form I have 3 listboxes:
list1: shows all the units. Selecting a unit form list1 sets the form current record, since the form itself is bound to the the unit table.
list2: shows the single unit's addons, based on list1 selected unit.
list3: shows all the addons

I wish to add some others addons to the selected unit on double click on list3 (the addons list) and consequentially requery the list2. I'm using the following code:

Private Sub list3_DblClick(Cancel As Integer)

Dim ctrl As Control
Dim strSQL As String
Dim varItem As Variant

Set ctrl = Me.list3

If ctrl.ItemsSelected.Count > 0 Then
For Each varItem In ctrl.ItemsSelected
strSQL = "INSERT INTO tab1(addID) " & _
"SELECT addons.addID " & _
"FROM addons " & _
"WHERE addons.addID = " & ctrl.ItemData(varItem)

CurrentDb.Execute strSQL

Next varItem

End If

Me.list2.Requery

End Sub

It works partially, since the new addon is added to the junction table, but the connection field "unitID" is blank, and the event has no effect on list2.
My question is: how can I also fill the unitID value, based on the list1 selected unit (which is also the form current record) and requery the list2?
Besides, is it possible to prevent access inserting the same addon twice? I tried the WHERE NOT EXISTS method, but I really don't know the proper syntax...
Please, even a little advice on how approach this problem would be appreciated.
I'm also posting the database.
Many thanks to the forum people.

 

[Kovacs]

Ok, I was able to write the following code:

Private Sub list3_DblClick(Cancel As Integer)

Dim a1 As String
Dim a2 As String
Dim strSQL As String
Dim i As Variant

For Each i In Me.list3.ItemsSelected
a1 = Forms!Form!unitID
a2 = addID & Me.list3.ItemData(i)
strSQL = "INSERT INTO tab1(unitID, addID) " & _
"VALUES('" & a1 & "','" & a2 & "') ;"

DoCmd.RunSQL strSQL
Me.list2.Requery

Next i

End Sub

It works perfectly, on double click event on list3 the new addon is added to the junction table and the list2 is correctly refreshed.
I only need a way to prevent user from adding the same addon if a unit already has it. I'm trying to understand how DLookup function works, in order to check in to the tab1 table and add the record only if the criteria is matching.

I've tried:

If Not IsNull(DLookup("addID", "tab1", "unitID = " & [unitID] And "addID = ???selected item addID???)) Then
For Each i In Me.list3.ItemsSelected
a1 = Forms!Form!unitID
a2 = addID & Me.list3.ItemData(i)
strSQL = "INSERT INTO tab1(unitID, addID) " & _
"VALUES('" & a1 & "','" & a2 & "') ;"

DoCmd.RunSQL strSQL
Me.list2.Requery

Next i

Else
DoCmd.CancelEvent

End If

The "blue" part seems good to me, but I need a second criteria for DLookup (the "red" part). I've tried a lot of solutions, but I can't find the right syntax. Someone could give me a little advice? Please, I am a newbie, I'm really struggling here. :banghead:
p.s. - I'm posting the database again