A patient can have multiple referrals. frmPatients holds the patient
information and fsubReferrals hold the referral information.

On fsubReferrals, instead of using the basic add a new record approach
(DoCmd.GoToRecord , , acNewRec) and editing the form directly, I'd
like the user to click on a "Add New Referral" button and enter the
information in a popup form (frmNewReferral). When done, he hits a
"Save" button on the popup form, the popup form closes, and the
information appears in frmReferrals (where the fields are visible but
not enabled).

(If the user needs to edit the record later, he clicks on an Edit
button which opens frmNewReferral filtered for that record.)

How should I code this? Under the "Add New Referral" button, I was
thinking something like:

   DoCmd.openForm "frmNewReferral", , , , acFormAdd, acDialog

    Forms!frmNewReferral.RecordSource = "SELECT DISTINCTROW
tblReferrals.* " & _
    "FROM tblReferrals " & _
    "WHERE ((tblReferrals.PersonID=Forms!frmPatients!PatientID]));"

And then maybe some INSERT INTO into statement when he hits the save
button? I need to make sure that the PatientID gets passed into the
new referral record otherwise it will fail. Perhaps I'm over thinking
this.

Any suggestions? Thank you.

HeislerKurt@gmail.com wrote:

>A patient can have multiple referrals. frmPatients holds the patient
>information and fsubReferrals hold the referral information.
>
>On fsubReferrals, instead of using the basic add a new record approach
>(DoCmd.GoToRecord , , acNewRec) and editing the form directly, I'd
>like the user to click on a "Add New Referral" button and enter the
>information in a popup form (frmNewReferral). When done, he hits a
>"Save" button on the popup form, the popup form closes, and the
>information appears in frmReferrals (where the fields are visible but
>not enabled).
>
>(If the user needs to edit the record later, he clicks on an Edit
>button which opens frmNewReferral filtered for that record.)
>
>How should I code this? Under the "Add New Referral" button, I was
>thinking something like:
>
>   DoCmd.openForm "frmNewReferral", , , , acFormAdd, acDialog
>
>    Forms!frmNewReferral.RecordSource = "SELECT DISTINCTROW
>tblReferrals.* " & _
>    "FROM tblReferrals " & _
>    "WHERE ((tblReferrals.PersonID=Forms!frmPatients!PatientID]));"
>
>And then maybe some INSERT INTO into statement when he hits the save
>button? I need to make sure that the PatientID gets passed into the
>new referral record otherwise it will fail.

Setting the record source won't work for this purpose.  Use
the OpenForm method's OpenArgs argument to pass the patient
id:

DoCmd.openForm "frmNewReferral", _
					DataMode:= acFormAdd, _
					WindowMode:= acDialog, _
					OpenArgs"= Me.PatientID

Then in the new referral form's Open event:

	Me.txtpatientid.DefaultValue = Me.OpenArgs

If the referral form is bound to tblReferrals, then there is
no reason to do anything extra to save the new referral
data.  Your "Save" button only needs to close the form.  If
you have some other reason to explicitly save the data, use:
	If Me.Dirty Then Me.Dirty = False
The point here is to just bind the form to the referrals
table and saving is either automatic or really simple.

To edit an existing referral just open the form using the
WhereCondition argument:

DoCmd.openForm "frmNewReferral", _
					WhereCondition:="PatientID=" & Me.PatientID, _
					DataMode:= acFormAdd, _
					WindowMode:= acDialog

The trick to updating the subform's data after adding or
editing a record, is to follow the OpenForm line with:
	Me.fsubReferrals.Form.Requery
This works because the other form was opened in dialog mode.

-- 
Marsh
MVP [MS Access]