Open Form to specific record but using 2 different fieldnames

[spartansFC]

Hi

I have a form called frmPeople which has a subform called frmPeopleSubPlacementHistory.

frmPeople has the table structure

lngPeopleID (PK)

frmPeopleSubPlacementHistory has the table structure of

lngPlacementID (PK)
lngPeopleID (FK)

there can be more than 1 placement which is where my problem is.

I need to edit the frmPlacement and also be able to add new records to frmPlacement, on frmPeople i have 2 command buttoms AddNew and EditPlacement

I've sorted out the EditPlacement by using the code

Private Sub EditPlacement_cmdbutton_Click()
On Error GoTo Err_EditPlacement_cmdbutton_lest_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmPlacement"
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!frmPeopleSubPlacementHistory.Form!lngPlacementID

Exit_EditPlacement_cmdbutton_lest_Click:
    Exit Sub

Err_EditPlacement_cmdbutton_lest_Click:
    MsgBox Err.Description
    Resume Exit_EditPlacement_cmdbutton_lest_Click
End Sub

and then on frmplacement i have

Private Sub Form_Open(Cancel As Integer)
    If Len(Me.OpenArgs) > 0 Then
        Dim rs As Object
        Set rs = Me.Recordset.Clone
        rs.FindFirst "[lngPlacementID]=" & Me.OpenArgs
        Me.Bookmark = rs.Bookmark
    End If
End Sub

How do i Open frmPlacement to a new record but to the specific peopleID, i could use peopleID instead of lngPlacementID when editing a placement but it won't work if there are more than 1 placement as it will only look at the first placement.

the code i've started with for NewPlacement is:

Private Sub NewPlacement_cmdbutton_Click()
On Error GoTo Err_NewPlacement_cmdbutton_Click

    Dim stDocName As String
    Dim stLinkCriteria As String

    stDocName = "frmPlacement"
    'DoCmd.OpenForm stDocName, , , stLinkCriteria, acNormal
    DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!PeopleID
    DoCmd.GoToRecord , , acNewRec

Exit_NewPlacement_cmdbutton_Click:
    Exit Sub

Err_NewPlacement_cmdbutton_Click:
    MsgBox Err.Description
    Resume Exit_NewPlacement_cmdbutton_Click

End Sub

Anyone have any ideas

Michael

 

[MajP]

frmPlacement to a new record but to the specific peopleID.

If I understand what you are asking you want to open the form to a new record but the peopleID for each you want to set. So open it to a new record and then set the default value of the peopleID. Do not use the goto new record, but open it in addnew mode of the acformDataMode.

so if you have a control on the "frmPlacement" after you open it you can set the default value like

Forms!frmPlacement.txtBxPeopleID.defaultValue = Me.PeopleID.

If you do not have a control on the form since it is a FK you can add one and make it invisible.

 

[spartansFC]

Thanks for your help Majp

Sorted it, i still kept the code the same and it seemed to work

DoCmd.OpenForm stDocName, , , stLinkCriteria, , , Me!PeopleID
DoCmd.GoToRecord , , acNewRec

On frmPlacement in PeopleID, i've added in the default value

=[Forms]![frmPeople]![ID]

A perfectly simple soulution, didn't even realise you could use the default value in this way.

Thanks

Michael