go to specific record based on choice in subform

[CuriousMark]

I have a form that lists in a subform multiple rows of data, each with a
unique key ("id") field. The main form is for entry/edit of the data that is
also shown these rows. I want to code the main form so that when the user
clicks on a row in the subform, the main form shows the chosen record based
on the unique key ("id"). When I write code to change the query in the main
forms recordsource to include "WHERE id = " & (id selected in subform), the
main form now is based on only one record and I can't use the record
navigation to move between all the records. The GoToRecord function only has
offset, first, last, etc., and doesn't allow going to a specific record.
Thanks.

 

[TonyT]

It's more usual to have the forms the other way around, so that the user
enters info on the main form and then the subform changes to display the
correct data, which it does automatically if you have the parent and child
fields correctly entered. If you want to go to a specific record, then look
at the help file on recordsetclone properties, from which you can use a
findfirst statement to search for the required record and then go to that
record, however this will Requery and change the recordsource for the subform
so long as it is linked to the main form.

 

[CuriousMark]

I will have to research your answer because I am not familiar with
recordsetclone. The other "workaround" I was considering is to navigate to
the first record, then using a Do until loop, move to each successive record
until the id matches the desired id.