Datasheet subform

[rpm]

I'm new to Access/VBA so I hope I can expalin what I'm after to experts... I have a datasheet subform that displays one to many records of a table. I would like the user to double-click on one record and a new form opens up to edit the field values on that record. Currently, the form opens up on the last record displayed no matter which record I select...

Any advice is welcomed.
Thanks,
Peter

 

[SydneyGeek]

Hi rpm, the trick here is to use an extra control in the main form. Do this:
1. Drag a textbox onto the main form, anywhere. Call it txtKey, and make its Control Source [subformName].Subform![ID_fieldname], where ID_fieldname is the key field in the subform.
2. Now, time to cheat. With the Control Wizards on, drag a CommandButton onto the form. When it launches, select Form operations, Open Form, Matching ... and use txtKey in the main form to match the key field in the other form. Click OK.
3. Try it out: select a record in the subform, click the button, and see how you go. If OK, right-click the Command Button (in Design view), select Properties, and go to the Click event code. Copy this code to the double-click event of the subform, but be careful to match up the control names ofr the error handling.
4. Test out again, by double-clicking the subform. If still OK, go back to Design view and delete the Command Button. Also, set the Visible property of txtKey to False.

Denis