ms access auto fill main form based on focus of subform

Question

i have a data entry/editing form with two combo boxes (Name and Group). Each Group correlates to multiple names but each name is in only one group. At the bottom of this form is a subform which is a continuous form displaying a query of the associated table filtered based on an unbound combo from which you select the group. Basically you choose which group you want to see and it displays a list of all the Names in that Group. I want to make this form able to add and delete Names from the table (which it does with buttons already), but I also want to be able to select a Name from the subform and have the main form focus on that entry and auto fill the two bound combos. and then from there I would like to be able to edit and save that entry or just delete the entry. Similarly, I would like to be able to add a new entry without worrying about writing over a current entry. Access might do this automatically; if this is the case, is it even possible to "edit" an entry without deleting and then replacing it. Suggestions?

Answer 1

I also want to be able to select a Name from the subform and have the main form focus on that entry and auto fill the two bound combos.

To do that, you can do a FindRecord on the main form's recordset, using the ID from the subform. The form will move to the correct record. From the subform's OnCurrent Event:

 Forms!MyMainForm.Recordset.FindFirst "MyID = " & desiredRecordID

or

 Forms!MyMainForm.Recordset.FindFirst "MyID = '" & desiredRecordID & "'"

Similarly, I would like to be able to add a new entry without worrying about writing over a current entry.

To do that, execute the following code:

 DoCmd.GoToRecord acDataForm, "MyMainForm", acNewRec