Adding people to case table from people table.

[fuonge]

I have a system where there could be multiple people connected to a case.



If I want to add a person to the case or multiple people, I have a popup that allows for searching the system for existing people or adding an unexisting person. Both are frequently used.

What I want to do is, instead of having that person copy and paste the ID number to sync up the other details, I want there to be a button that once found or added the right person, to just insert the id number into the "add person to case" form. The person form is just a database that keeps track of existing people and is a way to not allow for duplicates.


How would I do that to automatically, when the person form is closed to insert the ID number into the add person to case form. If that's not the best way to do that, what is a better way to set this up?

Thank youa

[orange]

So you have at least 2 tables, hopefully 3.

Case, People and PeopleAssignedToCase

Case
-Case1
-case2

...


People
-1 Bob G
-2 Ken H
-3 Jim J
-4 Omar K
-5 Jane Q

Suppose
Case1 has Ken H and Bob G already assigned, and Ken H is also working on Case2
eg

Case1, Bob G
Case1, Ken H
Case2, Ken H

So if you have a new Case, you must add the Case to the Case table before you can assign someone(s) to it.
Also, if you have a new person, you ad the Person to the Person table, before you add them to a Case.

Suppose a new person Felix Z is hired. You add Felix Z to the People table. And also suppose you want to assign him to Case2 (to work along with Ken)

You could have a combobox of Cases, and a combobox of People, where
cboCase has rowsource Select caseInfo from tblCase and
cboPeople has rowsource Select PeopleInfo from tblPeople.

You Select Case2 from cboCase and Select Felix from cboPeople.
In the afterUpdate event of cboPeople, you take the CaseID from cboCase,
and the PeopleID for Felix X from cboPeople to insert/Append a new record to
tbl PeopleAssignedToCase.

I'm sure there are other ways, but that's one way.
Good luck.

[fuonge]

I do have 3 tables. I have no idea why I made this so difficult. Seems to work fine this way vs using the ID. Thank you!