Code looping back to itself unexpectedly

[magicdds-]

I have the following code on the ACCEPT command button on the form
PATIENTINFOENTRY:

Private Sub Accept_Click()
On Error GoTo Err_Accept_Click

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenForm "Main", acNormal, "",
"[PatientID]=[Forms]![PatientInfoEntry]!_[PatientID]", acEdit, acNormal

DoCmd.Close acForm, "PatientInfoEntry"
Forms!Titlebar!Patient.Requery

Exit_Accept_Click:
Exit Sub

Err_Accept_Click:
MsgBox Err.Description
Resume Exit_Accept_Click

End Sub


This code should open the form MAIN showing the record for the patient whose
information was just entered in the form PATIENTINFOENTRY, Close the form
PATIENTINFOENTRY, add the patient's name to a combobox in the form TITLEBAR.
This seemed to work fine until I open a form to add a charge to the patient
record. When I close the form CHARGE, a box pops up asking me for
[Forms]![PatientInfoEntry]![PatientID]. Remember, the form PatientInfoEntry
has already been closed.

If I delete the line
DoCmd.OpenForm "Main", acNormal, "",
"[PatientID]=[Forms]![PatientInfoEntry]!_[PatientID]", acEdit, acNormal
and open the form MAIN from the form TITLEBAR (in the combobox, select the
patient's name & the form Main opens to that patient's record), everything
works smoothly.

The question is, why would the code try to open the form a second time after
the form PATIENTINFOENTRY has been closed for some time already?

Any ideas would be appreciated.
Thanks
Mark

 

[Graham Mandeno]

Hi Mark

The way you are opening your form "Main", you are presetting a filter,
namely:
"[PatientID]=[Forms]![PatientInfoEntry]!_[PatientID]"
(By the way, are you sure that underscore should be there???)

If you ever requery that form later, it will try to reapply the same filter.
If the form "PatiientInfoEntry" is closed at that time, then the filter will
fail.

The solution is to insert the *value* of the PatientID into the filter
string, so that any future requery of the form does not rely on the original
form being open (and on the same record).

Try this:
DoCmd.OpenForm "Main", , , "[PatientID]=" & Me![PatientID]

Note that I've removed the arguments for which you were supplying default
values. You can put them back in if you like, but they are unnecessary.

If PatientID is a text field then of course you will need to enclose its
value in quotes.

 

[magicdds-]

That was exactly the problem. Thanks so much for your help!

By the way, for my future knowledge, why do these 2 lines produce different
result

"[PatientID]=" & Me![PatientID]

versus

"[PatientID]= Me![PatientID]"


Mark

Hi Mark

The way you are opening your form "Main", you are presetting a filter,
namely:
"[PatientID]=[Forms]![PatientInfoEntry]!_[PatientID]"
(By the way, are you sure that underscore should be there???)

If you ever requery that form later, it will try to reapply the same filter.
If the form "PatiientInfoEntry" is closed at that time, then the filter will
fail.

The solution is to insert the *value* of the PatientID into the filter
string, so that any future requery of the form does not rely on the original
form being open (and on the same record).

Try this:
DoCmd.OpenForm "Main", , , "[PatientID]=" & Me![PatientID]

Note that I've removed the arguments for which you were supplying default
values. You can put them back in if you like, but they are unnecessary.

If PatientID is a text field then of course you will need to enclose its
value in quotes.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have the following code on the ACCEPT command button on the form
PATIENTINFOENTRY:

Private Sub Accept_Click()
On Error GoTo Err_Accept_Click

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenForm "Main", acNormal, "",
"[PatientID]=[Forms]![PatientInfoEntry]!_[PatientID]", acEdit, acNormal

DoCmd.Close acForm, "PatientInfoEntry"
Forms!Titlebar!Patient.Requery

Exit_Accept_Click:
Exit Sub

Err_Accept_Click:
MsgBox Err.Description
Resume Exit_Accept_Click

End Sub


This code should open the form MAIN showing the record for the patient
whose
information was just entered in the form PATIENTINFOENTRY, Close the form
PATIENTINFOENTRY, add the patient's name to a combobox in the form
TITLEBAR.
This seemed to work fine until I open a form to add a charge to the
patient
record. When I close the form CHARGE, a box pops up asking me for
[Forms]![PatientInfoEntry]![PatientID]. Remember, the form
PatientInfoEntry
has already been closed.

If I delete the line
DoCmd.OpenForm "Main", acNormal, "",
"[PatientID]=[Forms]![PatientInfoEntry]!_[PatientID]", acEdit, acNormal
and open the form MAIN from the form TITLEBAR (in the combobox, select the
patient's name & the form Main opens to that patient's record), everything
works smoothly.

The question is, why would the code try to open the form a second time
after
the form PATIENTINFOENTRY has been closed for some time already?

Any ideas would be appreciated.
Thanks
Mark

 

[Graham Mandeno]

Hi Mark

Glad it worked for you

Why do these 2 lines produce different results?

"[PatientID]=" & Me![PatientID]

This takes the *value* of PatientID at the time the code is executed and
places it into the string. Let's say PatientID is 999, then the string
value would be:
"[PatientID]=999"

"[PatientID]= Me![PatientID]"

This does not substitute the value, but leaves the *reference* to the value.
If the object being referenced disappears (for example, because a form is
closed) then the reference becomes invalid.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

That was exactly the problem. Thanks so much for your help!

By the way, for my future knowledge, why do these 2 lines produce
different
result

"[PatientID]=" & Me![PatientID]

versus

"[PatientID]= Me![PatientID]"


Mark

Hi Mark

The way you are opening your form "Main", you are presetting a filter,
namely:
"[PatientID]=[Forms]![PatientInfoEntry]!_[PatientID]"
(By the way, are you sure that underscore should be there???)

If you ever requery that form later, it will try to reapply the same
filter.
If the form "PatiientInfoEntry" is closed at that time, then the filter
will
fail.

The solution is to insert the *value* of the PatientID into the filter
string, so that any future requery of the form does not rely on the
original
form being open (and on the same record).

Try this:
DoCmd.OpenForm "Main", , , "[PatientID]=" & Me![PatientID]

Note that I've removed the arguments for which you were supplying default
values. You can put them back in if you like, but they are unnecessary.

If PatientID is a text field then of course you will need to enclose its
value in quotes.
--
Good Luck

Graham Mandeno [Access MVP]
Auckland, New Zealand

I have the following code on the ACCEPT command button on the form
PATIENTINFOENTRY:

Private Sub Accept_Click()
On Error GoTo Err_Accept_Click

DoCmd.RunCommand acCmdSaveRecord

DoCmd.OpenForm "Main", acNormal, "",
"[PatientID]=[Forms]![PatientInfoEntry]!_[PatientID]", acEdit, acNormal

DoCmd.Close acForm, "PatientInfoEntry"
Forms!Titlebar!Patient.Requery

Exit_Accept_Click:
Exit Sub

Err_Accept_Click:
MsgBox Err.Description
Resume Exit_Accept_Click

End Sub


This code should open the form MAIN showing the record for the patient
whose
information was just entered in the form PATIENTINFOENTRY, Close the
form
PATIENTINFOENTRY, add the patient's name to a combobox in the form
TITLEBAR.
This seemed to work fine until I open a form to add a charge to the
patient
record. When I close the form CHARGE, a box pops up asking me for
[Forms]![PatientInfoEntry]![PatientID]. Remember, the form
PatientInfoEntry
has already been closed.

If I delete the line
DoCmd.OpenForm "Main", acNormal, "",
"[PatientID]=[Forms]![PatientInfoEntry]!_[PatientID]", acEdit, acNormal
and open the form MAIN from the form TITLEBAR (in the combobox, select
the
patient's name & the form Main opens to that patient's record),
everything
works smoothly.

The question is, why would the code try to open the form a second time
after
the form PATIENTINFOENTRY has been closed for some time already?

Any ideas would be appreciated.
Thanks
Mark