Form Error with GoToRecord , , acNewRec

[RxExec]

I have simple form (2 fields and a combobox) bound to a single table. The form needs to open with the Patient_ID taken from the current main form and the ProtPat_ID at autonumber. It works now with one hiccup, the form opens with ProtPat_ID at autonumber but with an empty Patient_ID field. If I manually enter The Patient_ID, everything saves to the table as expected.

The form opens from a command button with the following code:

Private Sub btnAddPatientToProtocol_Click()
On Error GoTo Err_btnAddPatientToProtocol_Click

Dim stDocName As String
Dim sWhere As String
Dim stLinkCriteria As String

stDocName = "form8"
sWhere = " Patient_ID = " & Me!Patient_ID
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.OpenForm "form8", acNormal, , sWhere
DoCmd.GoToRecord , , acNewRec

Exit_btnAddPatientToProtocol_Click:
Exit Sub

Err_btnAddPatientToProtocol_Click:
MsgBox Err.Description
Resume Exit_btnAddPatientToProtocol_Click

End Sub



How do I open the form with the Patient_ID populated with the ID from the current main form?

Thanks

 

[Galaxiom]

Use the Before Insert Event of the opened form to write the Patient_ID to the field or control.

 

[RxExec]

Thanks Galaxiom,
I used the code below and the Patient_ID field populates with the correct ID and the ProtPat_ID is at autonumber, but when I pick a choice from the combobox The autonunber reverts to the last record for that patient and overwrites the fields for that record...what am I missing? Thanks


Private Sub Form_BeforeInsert(Cancel As Integer)

If Me.NewRecord Then
Forms!form8.Requery

End If


End Sub

 

[vbaInet]

In the absence of Galaxiom, here's a replacement -->

Private Sub btnAddPatientToProtocol_Click()
On Error GoTo Err_btnAddPatientToProtocol_Click

    DoCmd.OpenForm "form8", acNormal, , "Patient_ID = " & Me!Patient_ID
    DoCmd.RunCommand acCmdRecordsGoToNew

Exit_btnAddPatientToProtocol_Click:
    Exit Sub

Err_btnAddPatientToProtocol_Click:
    MsgBox Err.Description
    Resume Exit_btnAddPatientToProtocol_Click

End Sub

If the data type of PatientID is Text, then replace the first line with this:

DoCmd.OpenForm "form8", acNormal, , "Patient_ID = '" & Me!Patient_ID & "'"

 

[RxExec]

Thanks for stepping in. The Patient_ID field still does not populate with the ID from the current form. Maybe a BeforeInsert? If I enter the Patient_ID manually works perfect. Here's how it behaves:

Thanks Again

 

[vbaInet]

Upload a stripped down version of your db (mentioning steps to reproduce the problem) and I'll have a quick look.

 

[RxExec]

I will, Thank You

 

[Galaxiom]

Although including a Where argument in the OpenForm command will limit the records to those selected by the Where it does not add that value to the new record automatically.

You need to maintain some record of the ID and write that to the appropriate field or control with the BeforeInsert Event procedure.

The value can be stored in a Global variable but the best place is in the OpenArgs property of the form.

Open the form like this.

DoCmd.OpenForm "form8", acNormal, , "Patient_ID = " & Me!Patient_ID, , , Me!Patient_ID

Then use this to write the value to the control or field called Patient_ID in the opened form's BeforeInsert Event (fired when a new record is saved)

Me!Patient_ID = Me.OpenArgs

 

[RxExec]

Thank You vbaInet for willing to take a look.
Thank You Galaxiom for the answer I was looking for, that works perfectly! Adding the concept behind the code helped me even more! Thanks Again