Based on the code you posted I assumed you were trying to work with the record set via vba. Are you trying to open a form at this new record?
If that's the case then yes we were going about this in the wrong way.
Step 1) From your code in the prev form that launches the new form, do a dlookup to check if the ECN exists in the completion table. If it does then open the next form filtered by that ecn value. If it does not then open the next form in acFormAdd mode and provide the ecn value to the OpenArgs. Something like this:
Code:
Public Sub OpenCompletionForm()
If DLookup("ECN", "Completion", "ECN=" & ECN_VALUE_HERE) Then
'dlookup found an existing record in the completion table with a matching ECN value
DoCmd.OpenForm "FORM_NAME_HERE", , , "ECN = " & ECN_VALUE_HERE, acFormEdit
Else
'dlookup didn't find a matching record so create a new one
DoCmd.OpenForm "FORM_NAME_HERE", , , , acFormAdd, , CStr(ECN_VALUE_HERE)
End If
End Sub
Step 2) In the last form, on the load event, check if openargs are present. If they are then set the default value for the ECN control to the value of the open args. Something like this:
Code:
Private Sub Form_Load()
If Len(Me.OpenArgs) > 0 Then
Me.ECN.DefaultValue = Me.OpenArgs
Else
Me.ECN.DefaultValue = ""
End If
End Sub
I'm making lots of assumptions here, like ECN being a long data type for example. Inspect the code and make any necessary adjustments for field names, datatypes, etc. If this isn't enough to point you in the right direction you can upload a zipped copy of your db and I can take a look at it.