Delete row from a list box

[CD Tom]

I'm probably going about this the wrong way so any suggestions are appreciated.
I have a club data base with members I want to associate family members to a member. I created a associates table with the members club number, the associates club number, and the associates Name. I've created a combo box on the main members form where the user can select the associate and add it to the associates table, also on the main members form I have a list box where the associate members show up. This works fine, what I need to do is be able to double click on one of the associates and be able to go to that persons record or remove that associate from the list box. Because the associates member number is the second column in the Associates table and there can be multiple associates for any given member how do I find the correct associate to look up or remove.

I hope this is clear what I'm looking to do. If not let me know and I'll try again.

[zmbd]

What is the primary key in the table? This is what you'll need to be able to remove the record.
-z

[CD Tom]

The primary key in the associates table is the main members clubmembernbr the associates number is the second in the table. There really isn't a primary key because there can be more than one associate assigned to the main club member. Is there a way to tell when you select a item from a list box what record we are pointing to? If I knew that then I could get the associates clubmembernbr and us it to either look up the record or delete it.
I hope this is a little clearer.

[zmbd]

One more question:

In one case you talk about a cmbobox where in the [associateclubnumber] is entered.
Then you have a list box with the [membersclubnumber]. Later in the post you talk about removing the [associateclubnumber] from the list box... do you mean the combobox? We also need to have an idea as to the data source for the combobox and listbox.

To handle finding the record you're interested in:

Without a primary-key , either as a single field within the table or as a composite key, you cannot ensure that you have a unique record.

So let's look at how we can determine how to locate your record.

For the sake of my sanity lets try something

[membersclubnumber] = 100 (for member 1)
[membersclubnumber] = 200 (for member 2)
[associateclubnumber] = 911 and [associatename] = john
[associateclubnumber] = 912 and [associatename] = jane
[associateclubnumber] = 921 and [associatename] = rob
[associateclubnumber] = 922 and [associatename] = reba

Case 1 - this is what I expect your table to look like:

[membersclubnumber][associateclubnumber][associatename]
[100][911][john]
[100][912][jane]
[200][921][rob]
[200][922][reba]

Case 2 - however, without some logic to prevent duplications then you could get

[membersclubnumber][associateclubnumber][associatename]
[100][911][john]
[100][912][jane]
[200][921][rob]
[200][922][reba]
[100][912][jane]
[200][921][rob]
[100][912][jane]
[200][921][rob]
[200][922][reba]

This is a mess. In order to prevent this, and even though I dislike them, I would use a composite primary-key against [membersclubnumber] and [associateclubnumber] this ensures that case 2 cannot happen. (if you use an autonumber field etc... you still risk case 2 without any logic to prevent it from happening) The only advantage to using the composite key is that it doesn't require any VBA/Macro code to prevent duplicate records... it does however, make the VBA code and SQL more difficult so I often add an indexed autonumber field to such tables... easy enough to find the record, pull the field's value... not really the best practice; however, a compromise I make for my sanity.

Now you only need to create a query that finds the record based on [membersclubnumber] and [associateclubnumber]. From there, there are several methods to edit the record (directly from the detail section of a form for one)... as for deleting the record, there's the delete query.

-z

[CD Tom]

After going over your comments I realize that the associates table is designed wrong. By making the Assoc mbr number the primary key everything works. There can only be one Associate member assigned to an individual member. By changing this everything works great. Thanks for your comments they helped.