Select Active Record in Datasheet SubForm

[Phred]

Access 2007, moderate skill level, not compiled, not split, no errors. I need to select the active record in data sheet and launch a modal form based on a query of the record selected.

I have a Main Form which has criteria the end user selects, MealName, StartDate, EndDate and then clicks Find Records button and the data sheet returns the queried data.

The end user needs to select the record they want, and pick a new Food Item to replace the existing item. A lot of inventory data has to change to reflect the change in Food Item.

I would like the end user to select the row he wants to change. And somehow (this is what I am looking for) launches a Modal Form calling up all the fields of the selected row. Then he can select the Alternage Item on the Modal Form and all the data associated with it. Then when he is happy with his selection on the Modal Form he can click an update button and run an Update Query replacing the data in the Active Record.

See Picture

I am unsure how to reference the active row in the datasheet or how to launch a form in some manner from the selected row. I can obtain the Primary Key of the data sheet to make the update query exact.

Thanks

Fred

[pbaldy]

Does this technique fit the situation?

BaldyWeb wherecondition

I'd probably use the double click event of the subform textboxes.

[Phred]

I have a Select Query that is set to display as a Datasheet. That Query then displays inside a SubForm set to display as a data sheet.

I tried to set the SubForm field MealItem for Double Click and the Event property is there DoCmd.OpenForm "myform". But when the SubForm with the Double Click is in Form View on the Main Form the Field with the Double Click doesn't respond to anything. Item field is not active to the double click. Is there a Property I need to change on the Data Sheet Sub Form?

I read your link. But the same problem applies.

Thanks

Phred

[pbaldy]

The double click event of a textbox should work regardless of view. Can you post the db with that effort in it?

[Phred]

Yes, it does work. It helps if you put the double click command on the correct field.

Sheesh, sorry.

[pbaldy]

LOL! We've all done it. Glad it worked for you.