Open MS Access subform to edit specific record

Question

I have an Access form "DailyItemsTracked" with a subform "ActivitiesSubform" in datasheet view. When I double click on a record in "ActivitiesSubform" it should be opening a form called "ActivityEntry" to edit the record.

When I double click, I have the correct id of the record I want to edit. However, the new form consistently opens to add a new record rather than edit the existing record. (All existing records are filtered out). I have Googled the issue for more than an hour, but without a resolution.

The name of the id field and the id control source on the popup form are both "id".

Here is how I have tried to open the form:

MsgBox (Me![id]) 'It is getting the correct id for the selected record

DoCmd.OpenForm "ActivityEntry", , , "ID=" & Me.id

DoCmd.OpenForm "ActivityEntry", , , "[id]=" & Me![id]

DoCmd.OpenForm "ActivityEntry", , , "[id]=" & Form_ActivitiesSubform.id

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ActivityEntry"
stLinkCriteria = "[id]=" & Me![id]

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ActivityEntry"
stLinkCriteria = "ActivityEntryEdit.[id]= " & Me![id]

Even when I spell out the id explicitly it is still opening to a new record:

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ActivityEntry"
stLinkCriteria = "ActivityEntry.[id]= " & 69

Nor can I select the record after opening

Dim stDocName As String
Dim stLinkCriteria As String
stDocName = "ActivityEntry"
stLinkCriteria = "ActivityEntryEdit.[id]= " & Me![id]
DoCmd.OpenForm stDocName, , , stLinkCriteria
Forms(stDocName).Recordset.FindFirst "id = " & Me!id

I appreciate any help you can offer!

Dale M Dale M · Accepted Answer · 2013-02-15T04:00:03

You need to understand the syntax of the DoCmd.OpenForm statement. You can read about it here. Short answer, you need to set the DataMode parameter to acFormEdit.

Open MS Access subform to edit specific record

1 Answers