I have a question about forms and subforms. Here are the forms and tables I have in my database that pertain to my question:
Tables:
tblCases
tblPeople
tblPeoplePerCase
Forms:
frmCases
frmPeople
frmSearchPeople
subfrmPeoplePerCase
I need to be able to relate a person(s) to a case that displays on the Cases form. The Cases form currently has a subform that lets me manually add people to the case and the info then populates the People per Case table. The information being stored in the People Per Case table is the Case ID, Person ID, and Person Type (they select this once the person is added to the subform). The subform has a dropdown list of everyone in the People table, but only shows some of their information (Person ID, Employee Number (not everyone has an Employee Number), Last Name, First Name, and Middle Name). If a new person has to be added, the user can click the edit button on the list (which sometimes disappears once the person starts scrolling through the list), and the People form is opened and the information is then added and linked to the case manually. This has been working ok, however the list of people is growing so I thought it would be better to do something a little more sophisticated. What I'm thinking of is adding a button to the Cases form that would open the Search People form. The user would then search and select a particular person or create a new person. This would take them to the People form so the user can either verify the person's information or add/update information. The People form would have a button to relate the person to the current case on the Cases form. Once clicked, it would ask the user if they want to add the selected person (would like the message box to show their name) to the current case (Case ID on the Cases form). If they select no, then the action would be canceled. If they select yes, then a new record with the Person ID from would be added to the People Per Case subform.
I'd like to do this in Visual Basic, but am not sure how to approach this.
Any help would be appreciated!
Thanks