Update list boxes with a twist.

[IfButOnly]

I have two tables, Company and Keywords, which have a many-to-many relationship. I have build an index file which is keyed on the id of both tables.

To update the keywords associated with a particular company, I have a form with two listboxes. List1 is a select list of all keywords (from the Keywords table) and List2 is a select list of keywords for the selected company (select list from the index file). Both lists are RowSourceType - Table/Query - for the selects to work.

1) I want to be able to double click on List1 to add new Keywords to List2, or double click on List2 to remove Keywords for the company. I have done this before where list2 was a value list but not where list2 is built from a select list. I have searched the site for a similar query, but no luck.

2) I am not very familiar with the code required to add/delete records from VBA, and have struggled with Help to get the full picture.

I would appreciate any help with either problem - many thanks in advance.

 

[Travis]

This code assumes that the List Boxes are not set to Multi-Select

'List1 Double Click
'Add Keyword Record
Dim CompanyID as Long 'Unique ID of Company
Dim KeyWordID as Long 'Unique ID of KeyWord

CompanyID=0 'Need to change this to the current CompanyID
KeyWordID=List1

stSQL="INSERT INTO [Index Table] ( [Company ID],[Key Word ID] ) VALUES (" & CompanyID & ", " & KeyWordID & ")"
CurrentDb.Execute stSQL

'List2 Double Click
'Add Keyword Record
Dim CompanyID as Long 'Unique ID of Company
Dim KeyWordID as Long 'Unique ID of KeyWord

CompanyID=0 'Need to change this to the current CompanyID
KeyWordID=List2

stSQL="Delete * [Index Table] Where [CompanyID]=" & CompanyID & " and [KeyWordID]=" & KeyWordID
CurrentDb.Execute stSQL

 

[IfButOnly]

Thanks a lot, Travis - I will try it in the morning.

 

[IfButOnly]

Thanks Travis, except for a missing FROM in the Delete Statement it worked a treat.

thanks mate - you're a legend.