Solved Open Form from a Command Button (1 Viewer)

[Weekleyba]

I have a form with some project data on it called F_Project. I want a command button on that form, to open a new form call F_PANumRequest. I only want one record on this new form and the ability to open it again and see what the request is. The F_Project has a record source of T_Project with a primary key of ProjectID. The F_PANumRequest has a record source of T_PANumRequest with primary key of PANumRequestID and a field for ProjectID.

How do I create a button to open a new record (only one) if there are no records or open the existing related record to the project if a record has already been created?
I believe I need an IF statement to check if the record related to the ProjectID exist prior to calling for a new record to be added, but I'm not sure how to do that.

Private Sub cmdButtonPA_Click()
   DoCmd.OpenForm "F_PANumRequest", , , , acFormAdd
   Forms![F_PANumRequest].Form.ProjectID = Me.ProjectID
End Sub

 

[theDBguy]

Are you saying T_PANumRequest can only have record per ProjectID?

 

[Weekleyba]

That is correct. The engineer we submit a PA Request that is generated from the F_PANumRequest form only once.

 

[ebs17]

Like this:

Private Sub cmdButtonPA_Click()
    DoCmd.OpenForm "F_PANumRequest", , , "ProjectID = " & Me.ProjectID, , , Me.ProjectID
End Sub

' in form F_PANumRequest
Private Sub Form_BeforeUpdate(Cancel As Integer)
    If Not IsNull(Me.OpenArgs) Then
       If Me.NewRecord Then Me.txtProjectID = CLng(Me.OpenArgs)
    End If
End Sub

The control that there will be only one record must be done differently, e.g. via a unique index on ProjectID, which would in fact create a 1:1 relationship.

 

[bastanu]

I think you need this in the F_PANumRequest:

Private Sub Form_Current()
If Me.NewRecord Then
        Me.[ProjectID] = Me.OpenArgs
End If
End Sub

Cheers,

 

[Weekleyba]

That worked ebs17. Thank you.