Trouble selecting active record in datasheet

[Phred]

Access 2007, not compiled, not split, skill level medium.



The form shows the default MealItems set for production. At the last minute the end user may need to select an alternate meal item, and substitute corn for green beans.

I have it set up so that the end user double clicks on the ItemName field on the data sheet and that opens a modal form with a query behind it.

I want the query behind the modal form to pick up the Meal3ID which is the primary key of the table behind the data sheet. and pull that record from the underlying table Tbl_ScheduledMealItems and display the fields on the modal form.

Everything works fine except:

The double click only calls up the first record in the underlying table. It does not pull up the active record (The one the end user clicked on.)

How can I make my double click on the ItemName field capture the Active Record clicked on and pull up that specific record in my Modal form?

Please see attached pic.

Thanks,

Phred

[John_G]

Hi -

Two things. First, what is the code being run when you double-click the record?
Second - is there any code in the On Click event for the field? If there is, it will run first, before the On Doubleclick code.

[Phred]

There is no code in the on click, only on the double click. The code on the double click is:

docmd.openform "myform"

Phred

[John_G]

Hi -

Your problem is that when you open the form "Myform" you are not telling it which record to display, so it will always open at the first record. What you want to do is open the form with a Where condition that effectively sets the form filter to display only that record.

Your command would look something like this:

docmd.openform "myform", , , "Fieldname = " & me![Meal3ID]

[Meal3ID] must be in the underlying recordset of the form you have illustrated, but it does not have to be on the form. It will have the value of the Key of the record you are on when you double-click.

Are both the forms based on the same table / query? If so, your DoCmd will be:

docmd.openform "myform", , , "Meal3ID = " & me![Meal3ID]

HTH

John