Add Record from Current Form to a Table

[Smtz]

I have a database that includes the following items related to my question:


- Tables: tblCases, tblPeople, tblPeoplePerCase,
- Forms: frmCases, frmPeople, subfrmPeoplePerCase, frmSearchPeople

What I'm trying to do is automate the process of adding a selected person to a particular case. What I'm thinking (and please feel free to chime in if you have any other suggestions) is that frmCases (which includes subfrmPeoplePerCase) will include a button to add a person to the case. Once clicked, frmSearchPeople would open. Once a person is selected and frmPeople displays their information, a button would then be displayed/enabled on frmPeople to add them to the current case in frmCases. Once this button is clicked, it would display a message box asking to confirm if PersonID should be added to CaseID. If confirmed, the information is then appended to tblPeoplePerCase (which includes PersonID, CaseID). The user is then brought back to frmCases and the subfrmPeoplePerCase displays the newly related information.

I know this is possible since I used to use a previous database with similar functionality, I'm just not too savvy in figuring out how to get all of it to work.

Any suggestions would be appreciated.

[RuralGuy]

It sounds like you don't trust your users very much. I would have a ComboBox in the SubForm in the "Person" field and have the tblPeople (actually a query of same) as the RowSource of this ComboBox. No search needed, just start typing in the cbo or use if for lookup. If the Person does not yet exist, you could use the NotInList event to open an "Add a new person" form if you want. Since I have my SubForms in Continuous Form view (never datasheet) I add a delete button for that record to make it real easy for the user to kill that mistake they just made.