Primary Key in a Multivalue Field

What you've tried is the correct route to go. Here's how it will work:

Table structure:

CaseTable (CaseID, CaseDate, OtherCaseDetailFields that are absolutely 100% case specific)
AttorneyTable (AttorneyID, Name, Address, Phone, etc)
CaseAttorneyMatch (CaseAttorneyMatchID, CaseID, AttorneyID)

So to find what attorney is matched to what case, you'll need to look at your new table, CaseAttorneyMatch. You want to keep the new ID in that table so you do have a UniqueID.

Then if you have a situation where one Attorney is the Lead Attorney, then you could add another field to that third table so you'd have something like:
CaseAttorneyMatch (CaseAttorneyMatchID, CaseID, AttorneyID, [highlight]IsLeadAttInCase[/highlight])

And the IsLeadAttInCase could be set as a Number - Byte value field, using 0 and 1 values... 0 = not lead att, 1 = lead att, and either set 1 as default, only allow one per case/att match, or else only set it when indeed an att is the lead attorney.

Make sense?

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

Thank you for the quick reply!

I have this setup now as you stated, however the big issue is with the interface. The interface currently is setup as follows: There is a form called "Case" this form works off a query and allows a user to enter all of the details for a new case and assign it to an attorney in the "assign to" field, which is essentially a Lookup field that displays a list of attorneys from the AttorneyTable.

I'm now trying to figure out how to leave this simple form structure the same, and with the "assign to" field now being deleted from the CaseTable what field will be used as the multivalue lookup list?

You simply create a new record in your CaseAttorneyMatch table. I'd just use an INSERT SQL statement from VBA like this:


The only other thing you might want to add in would be checking to see whether that match/pair already exists in the CaseAtt table. You could build in a WHERE clause, or else build a SELECT statement and use a recordset to check for record-count <= 0 to continue. It's up to you.

Also, how you refer to the controls will depend upon where your button is, and how your fields are laid out. I'm assuming the button is on the main form, and then you have the caseID and attorneyID on their own subforms.


"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

By the way, I probably listed the syntax incorrectly on referencing your subforms. Here's one good reference. I regularly have to look it up, b/c I forget:

"But thanks be to God, which giveth us the victory through our Lord Jesus Christ." 1 Corinthians 15:57

Thank you for your help!