Add a new record via a popup form

[HeislerKurt]

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.

 

[Marshall Barton]

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.

 

[HeislerKurt]

Ahh ... very nice. Almost there:

1. For the Add New Referral button on fsubReferrals, I put:

Dim lngPatientID As Long
lngPatientID = Me.PatientID

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

This works great.

2. For the Save Referral button on frmNewReferral I put:

DoCmd.Close
Forms![frmPatients]![fsubReferrals].Requery

(If I instead follow the earlier DoCmd.openForm line with Me!
fsubReferrals.Form.Requery I get a "can't find the field
'fsubReferrals' " error.)

However, I need to take a different approach with saving the referral.
When the new referral is saved, I'd like the form to bookmark and
requery fsubReferral to the record that was just added. I have this
working perfectly in another database that doesn't use a popup form to
add & save the new referral. In that database, the Save Referral code
looks like this:

Dim KeyCurrent As Integer
KeyCurrent = ReferralID

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

' Requeries the form (probably not needed since the bookmark does
this)
Me.Requery

' Bookmarks the record just saved, and returns to it (otherwise,
the requery defaults to first record)
Me.RecordsetClone.FindFirst "[ReferralID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark

How can I use similar code in the Save Referral button on my popup
form? I'm having trouble figuring out how to refer the subform since
I'm not on it (e.g., KeyCurrent = Forms!frmPatients!fsubReferal.Form!
ReferralID ???)

Thanks again.

Kurt

 

[HeislerKurt]

As for the edit referral button on fsubReferrals, I first tried this
code:

DoCmd.openForm "frmNewReferral", , , "PatientID = " & Me.PatientID,
acFormEdit, acDialog

However, I get an "Invalid use of Null" error when I click on it.
Debugging points to this line in the frmNewReferrals:

Private Sub Form_Open(Cancel As Integer)
Me.txtPatientID.DefaultValue = Me.OpenArgs
End Sub

I guess this happens because the code for the Edit Referral button
doesn't use the OpenArgs argument that I put in the code for the New
Referral button. Plus, the above code doesn't even reference the
Referral ID (a patient can have many referrals). After some trial &
error, I ended up with this, which seems to work:

DoCmd.openForm "frmNewReferral", , , "ReferralID = " & Forms!
frmPatients!fsubReferrals.Form!ReferralID, acFormEdit, acDialog,
Me.PatientID

 

[Marshall Barton]

Comments inline below:
--
Marsh
MVP [MS Access]

Ahh ... very nice. Almost there:

1. For the Add New Referral button on fsubReferrals, I put:

Dim lngPatientID As Long
lngPatientID = Me.PatientID

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

This works great.

Great! Any progress is a good thing ;-)

2. For the Save Referral button on frmNewReferral I put:

DoCmd.Close
Forms![frmPatients]![fsubReferrals].Requery

I am not sure what that requery is doing because that line
should be:
Forms!frmPatients!fsubReferrals.FORM.Requery
BUT, that's not a good idea because you should not have to
make the referrals form dependent on the patients form.

(If I instead follow the earlier DoCmd.openForm line with Me!
fsubReferrals.Form.Requery I get a "can't find the field
'fsubReferrals' " error.)

I thought the add new referral button was on the referrals
subform. If that were the case then line of code after the
OpenForm line would be:
Me.Requery
Or, use what you tried if the button is on the main form,
but I can see no reason for that error in this case.

However, I need to take a different approach with saving the referral.
When the new referral is saved, I'd like the form to bookmark and
requery fsubReferral to the record that was just added. I have this
working perfectly in another database that doesn't use a popup form to
add & save the new referral. In that database, the Save Referral code
looks like this:

Dim KeyCurrent As Integer
KeyCurrent = ReferralID

DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, ,
acMenuVer70

The DoMenuItem method is an archaic leftover from Access 2.
The recommended way now is to set Dirty to False.

' Requeries the form (probably not needed since the bookmark does
this)
Me.Requery

' Bookmarks the record just saved, and returns to it (otherwise,
the requery defaults to first record)
Me.RecordsetClone.FindFirst "[ReferralID] = " & KeyCurrent
Me.Bookmark = Me.RecordsetClone.Bookmark

How can I use similar code in the Save Referral button on my popup
form? I'm having trouble figuring out how to refer the subform since
I'm not on it (e.g., KeyCurrent = Forms!frmPatients!fsubReferal.Form!
ReferralID ???)

Since the ReferralID is still available in the referrals
form, you can still reference it without going back to the
subform.

To position the subform from within the referrals form, the
code would be the same, but with a different form object
(instead of Me)

Dim KeyCurrent As Integer
KeyCurrent = Me.ReferralID
Me.Dirty = False
With Forms![frmPatients]![fsubReferrals].Form
.Requery
.RecordsetClone.FindFirst "[ReferralID] = " & KeyCurrent
.Bookmark = .RecordsetClone.Bookmark
End With
DoCmd.Close

 

[Marshall Barton]

As for the edit referral button on fsubReferrals, I first tried this
code:

DoCmd.openForm "frmNewReferral", , , "PatientID = " & Me.PatientID,
acFormEdit, acDialog

However, I get an "Invalid use of Null" error when I click on it.
Debugging points to this line in the frmNewReferrals:

Private Sub Form_Open(Cancel As Integer)
Me.txtPatientID.DefaultValue = Me.OpenArgs
End Sub

I guess this happens because the code for the Edit Referral button
doesn't use the OpenArgs argument that I put in the code for the New
Referral button. Plus, the above code doesn't even reference the
Referral ID (a patient can have many referrals). After some trial &
error, I ended up with this, which seems to work:

DoCmd.openForm "frmNewReferral", , , "ReferralID = " & Forms!
frmPatients!fsubReferrals.Form!ReferralID, acFormEdit, acDialog,
Me.PatientID

Yes, that will get around this problem.

I would have modified the referrals form's Open event to
test for nothing in OpenArgs:

Private Sub Form_Open(Cancel As Integer)
If Not IsNull(Me.OpenArgs) Then
Me.txtPatientID.DefaultValue = Me.OpenArgs
End If
End Sub

just to eliminate an unnecessary coding requirement.

 

[HeislerKurt]

To position the subform from within the referrals form, the

code would be the same, but with a different form object
(instead of Me)

Dim KeyCurrent As Integer
KeyCurrent = Me.ReferralID
Me.Dirty = False
With Forms![frmPatients]![fsubReferrals].Form
.Requery
.RecordsetClone.FindFirst "[ReferralID] = " & KeyCurrent
.Bookmark = .RecordsetClone.Bookmark
End With
DoCmd.Close

Hmm ... for some reason, by adding the KeyCurrent code, DoCmd.Close
closes *everything* (frmPatients plus fsubReferral) *except* for
frmNewReferral (which is the only form left open).

 

[HeislerKurt]

To position the subform from within the referrals form, the
code would be the same, but with a different form object
(instead of Me)

Dim KeyCurrent As Integer
KeyCurrent = Me.ReferralID
Me.Dirty = False
With Forms![frmPatients]![fsubReferrals].Form
.Requery
.RecordsetClone.FindFirst "[ReferralID] = " & KeyCurrent
.Bookmark = .RecordsetClone.Bookmark
End With
DoCmd.Close

Hmm ... for some reason, by adding the KeyCurrent code, DoCmd.Close
closes *everything* (frmPatients plus fsubReferral) *except* for
frmNewReferral (which is the only form left open).

Yeah! I finally got everything working.

As for the close form problem, I fully specified the form to close, as
in:

DoCmd.Close acForm, "frmNewReferral"

Also, I needed to add a .Form_Load statement in the Save referral code
to help refresh some other controls on fsubReferral which are tied to
the OnLoad event.

Thanks again for all your help. (And you can disregard my PM as I
think I got everything working now.)

 

[Marshall Barton]

To position the subform from within the referrals form, the
code would be the same, but with a different form object
(instead of Me)

Dim KeyCurrent As Integer
KeyCurrent = Me.ReferralID
Me.Dirty = False
With Forms![frmPatients]![fsubReferrals].Form
.Requery
.RecordsetClone.FindFirst "[ReferralID] = " & KeyCurrent
.Bookmark = .RecordsetClone.Bookmark
End With
DoCmd.Close

Hmm ... for some reason, by adding the KeyCurrent code, DoCmd.Close
closes *everything* (frmPatients plus fsubReferral) *except* for
frmNewReferral (which is the only form left open).

I might have confused you, that code needs to be behind
frmNewReferral's save button? As much as I dislike making
frmNewReferral dependent on the patients form, it's easier
than trying to pass the value of the new record's ReferralID
back to the opening form (which is better, but more
complex).

Regardless of which main form contains the close statement,
we should be more explicit so close doesn't have to guess
which form to close:
DoCmd.Close acForm, Me.Name, acSaveNo

 

[HeislerKurt]

I might have confused you, that code needs to be behind

frmNewReferral's save button?

That's where it is.

Regardless of which main form contains the close statement,
we should be more explicit so close doesn't have to guess
which form to close:
DoCmd.Close acForm, Me.Name, acSaveNo

The syntax I ended up with is:

DoCmd.Close acForm, "frmNewReferral"

It seems to work without the acSaveNo (perhaps it's not necessary,
since the record was just saved using the Me.Dirty = False?)

 

[Douglas J. Steele]

The syntax I ended up with is:

DoCmd.Close acForm, "frmNewReferral"

It seems to work without the acSaveNo (perhaps it's not necessary,
since the record was just saved using the Me.Dirty = False?)

The acSaveNo (or acSaveYes or acSavePrompt) refers to saving design changes
in the form, not data changes made using the form.