If you want to open the secondary Form to a given Record (if it exists in the secondary Form) but open it to a New Record (if it doesn't exist in the secondary Form) and assign it the ID of the primary Form's Record...here's some boilerplate code I did ages ago for a poster to do just that:
In the Primary Form:
Code:
Private Sub Go2SecondaryForm_Click()
DoCmd.RunCommand acCmdSaveRecord
If Nz(Me.RecordID,"") <> "" Then
DoCmd.OpenForm "Secondary Form", , , , , , Me.RecordID
Else
MsgBox "A RecordID Must Be Entered First!"
End If
End Sub
In the Secondary Form:
Code:
Private Sub Form_Load()
Dim rst As Recordset
If Nz(Me.OpenArgs,"") <> "" Then
Set rst = Me.RecordsetClone
rst.FindFirst "[SecondaryRecordID] = " & Me.OpenArgs
If Not rst.NoMatch Then
Me.Bookmark = rst.Bookmark
Else
DoCmd.GoToRecord , , acNewRec
Me.SecondaryRecordID = Me.OpenArgs
End If
rst.Close
Set rst = Nothing
End If
End Sub
The above code assumes that RecordID is Numeric. If it is actually defined Text, replace the line
rst.FindFirst "[SecondaryRecordID] = " & Me.OpenArgs
with
rst.FindFirst "[SecondaryRecordID] = '" & Me.OpenArgs & "'"
You'll also need to replace Go2SecondaryForm with the actual name of your Command Button and SecondaryForm.
Also note that, as already suggested, the SecondaryRecordID, which acts as a Foreign Key, in this scenario, cannot be an AutoNumber Field, but must be defined as a Datatype that matches the Datatype of the RecordID, from the primary Form.
Linq ;0)>
The problem with making anything foolproof...is that fools are so darn ingenious!
All posts/responses based on Access 2003/2007