Primary Key in a Multivalue Field
Primary Key in a Multivalue Field
(OP)
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????
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????
Talk To Other Members
RE: Primary Key in a Multivalue Field
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
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
CODE
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 SubThe 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
http://www.techrepublic.com/blog/msoffice/how-to-r...
"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