Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.


Primary Key in a Multivalue Field

Primary Key in a Multivalue Field

I have a completed database to manage attorneys cases and it works great! There is a Case_Table and an Attorney_Table with a one to many relationship connecting the two. I have a field in the Case_Table titled "Assigned To" which allows a user to see a drop-down list of every attorney in the office and choose ONE to assign to a case.

The issue im facing now is the need to assign MULTIPLE attorneys to a case. Simply switching the "Assign to" lookupfield to store multiple values fails because the "values" are the Primary Keys of each Attorney, and throws an error message.

What I've Tried:
Deleting the "Assign To" Field from the Case_Table and creating a Joining_Table which contains the Case_ID and Attorney_ID, which I cannot understand where the new "Assign To" field for the form would be????

RE: 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, IsLeadAttInCase)

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

RE: Primary Key in a Multivalue Field

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?

RE: Primary Key in a Multivalue Field

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


Private Sub cmdAssignAttorneyToCase_Click()
   Dim strSQL As String
   strSQL = "INSERT INTO CaseAttorneyMatch (CaseID ,AttorneyID ,IsLeadAttInCase) " & vbCrLf & _
            "SELECT " & Form.CaseSubForm.CaseID & ", " & Form.AttorneySubform.AttorneyID & ", " & Form.AttorneySubform.IsLeadAttorneyInCase
   DoCmd.SetWarnings Off
   DoCmd.RunSQL strSQL
   DoCmd.SetWarnings On
End Sub 

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

RE: Primary Key in a Multivalue Field

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

RE: Primary Key in a Multivalue Field

Thank you for your help!

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!


Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close