Double click subform row to bring that record into form.

[Stephenson]

I have a form, TimeCard that has a subform, TimeCardSub that is set to data sheet view. Is there a way to double click on a row in TimeCardSub and have that record populate TimeCard for editing?

I've tried a double click event on one of the fields (ComboBox, Job) using the following code.

Private Sub cboJob_DblClick(Cancel As Integer)    DoCmd.OpenForm "TimeCard", , , "[JobID] = " & Me!JobID
    Me.FilterOn = False
End Sub

This does bring up the correct job but not always the right record.

I'm looking to in some way modify this code or something different/better so I can just double click anywhere on the specific (Row) record in TimeCardSub and bring it up in TimeCard. Please let me know if you have a solution to my problem or a better way to achieve this.

As a bonus I'm also looking for a way to filter or code TimeCardSub to show the most recent entry on the top rather than the bottom as a double check for the person entering the data. This way they'll be able to quickly look back at their entries. Thoughts?

Thanks for looking.

[ItsMe]

This does bring up the correct job but not always the right record.

I use similar code in the actual form object (the subform). However, I do not understand why you are using Me.FilterOn = False.

[pbaldy]

If that code doesn't bring up the correct record, your wherecondition isn't correct. It should either refer to a different field that is unique to the record being clicked on, or include a second field to narrow down the result.

As to the order, you can base the subform on a query that sorts descending, so the most recent record will be on top.

[Stephenson]

Paul,

Thanks for the suggestions. I took a closer look and it does come up with multiple records with the same Job Is there a way to just bring up one the one that is clicked on? I would also like it to only show in the form and not in the subform.

As for the

Me.FilterOn = False

That's a head scratcher. I'm sure I had a reason, but for now I'm not sure what it was.


I like the idea of basing my subform off of a query, but have little to no experience with them. Could you point me in the right direction on how to do this as it sounds like exactly what I need.

Thanks for the quick responses and sorry I'm so slow getting to them.

[pbaldy]

Like I said, use the field that uniquely identifies the record:

DoCmd.OpenForm "TimeCard", , , "[ID] = " & Me!ID

Instead of listing the table in the record source, try

SELECT * FROM EmployeeWorkLog ORDER BY DateWorked DESC

[Stephenson]

Thank you so much. This works great!

[pbaldy]

Happy to help!