Selecting an entry to edit from subform

[Paterson10987]

I have a form with a bunch of fields that are used to filter the records on a
subform. The subform is displayed as a datasheet. I want the user to be
able to select one of the records in order to edit it, which I would like
done on a separate form so it's easier to see, use, not mess up etc.

I'm not sure how i should start to go about this.. If I can make an event
for double clicking on the record, or a button on the main form. And how
might I send this specific record to the other form.

Anyone with ideas? Thanks.

 

[Paterson10987]

Really I suppose I just have no clue how to set up a nice system to edit the
entries.

 

[dymondjack]

Try setting up a criteria string to use with the DoCmd.OpenForm function in
your DblClick procedure.

Assuming that the name of the form you want to use is frmEdit, and the
control on your subform with the ID is named ctlID, see below. If you do not
have the ID control on your subform, change Me.ctlID to [ID] (assuming that
ID is the field name of the Primary Key). Note that the ID field needs to be
part of the subforms recordsource even if you do not use it as a field on the
form.

Private Sub DblClick()
Dim strWhere As String
strWhere = "[ID] = " & Me.ctlID
DoCmd.OpenForm "frmEdit", , , strWhere
End Sub

Note: if your ID is of the String datatype, strWhere would need to be as
follows (need to include single quotes so VBA knows its a string when it
evaluates the where condition)

strWhere = "[ID] = '" & Me.ctlID & "'"

An alternative way is to pass the ID as an OpenArg (again, using the
DoCmd.OpenForm function), and use the Bookmark property to jump to that
record (via frmEdit's OnOpen event). This is a little more complicated, but
the advantage is that the recordsource for your edit form will contain all
records, rather than just the one if you use the Where condition. Although,
in your case, just a single record might be more desirable.

hth

--
Jack Leach
www.tristatemachine.com

- "A designer knows he has reached perfection not when there is nothing left
to add, but when there is nothing left to take away." - Antoine De Saint
Exupery