Message Box Not Appearing and Field Updates Automatically

[Curtis]

I am having a problem with the coding below that someone was trying to
help me with in another website. I have been to several websites and
hopefully here I can get it resolved. I inherited this database and
issue, and I apologize for a bit lengthy. The coding is located in the
Event Procedure on the "On Open" property of the {rptLetter}:

Sub cmdPrint_Click()
'Check if no results in any field, if all missing
'inform user and exit report
'Check that [frmExam Data] is the name of the
'subform control, not just the name of the form
'contained.
If Me.[frmExam Data].Form.Trichomonal <> True _
And Me.[frmExam Data].Form.Bacterial <> True _
And Me.[frmExam Data].Form.Yeast <> True Then
MsgBox "This individual does not have any Exam Data entered." & _
Chr(13) & "Previewing / Printing is canceled. " & _
Chr(13) & Chr(13) & "Check if Exam Data is Available and Re-run",
vbOKOnly +
vbInformation
Else
DoCmd.OpenReport rptLetter, acViewPreview
End If

End Sub

My result I want is: when I press the Cmd Button on the {frmPatient
Data}, linked to the macro {UpdatePrintNotice}, if there is a matching
subrecord, I want to view the {rptLetter} ready for printing, and
update the [ResultsNotified] field automatically in the subdatasheet
table {ExamData} as it gets printed. If any of the 3 fields
[Trichomonal], [Bacterial], and [Yeast], Yes/No fields located in the
subdatasheet table, are actively checked, display only the contents of
those that are checked, don't display. If there is no matching
subrecord, I want the msg: "This individual does not have any Exam
Data entered. Check if Exam Data is Available and Re-Run." I want
the printing to be cancelled and return back to the {frmPatient Data},
without any further updates.

Currently: 1) After pressing the Cmd Button, for those with a
subrecord, the data, including all 3 fields, regardless if they are
actively checked or not is printed. The active fields should be
displayed only if they are actively checked. Upon closing, the
[ResultsNotified] is automatically updated which should be the case.
2) For those individuals without a subrecord, the {rptLetter} is
displayed for printing, which shouldn't be. The Msg Box does not
appear at all. When closing, the [ResultsNotified] field still gets
automatically updated, creating a new record in the subdatasheet table
{Exam Data},which shouldn't be the case.

Information: The {frmPatient Data} has a subform {frmExam Data}; table
source is {PatientData} and {ExamData} respectfully. The [SSN] is the
link between the 2 tables. The [SSN] is the Primary Key in the 1st
table and [ExamID] in the 2nd table. The 3 fields [Trichomonal],
[Bacterial], and [Yeast] are fields in the subdatasheet {ExamData}
which are Yes/No fields.

The macro [UpdatePrintNotice] is attached to the Cmd Button "Print
Notification", "On Click" property, which has 2 actions:
SetValue: Item: [Forms]![frmPatient Data]![frmExam
Data]![ResultsNotified]; Expression: Date(); OpenReport: Report
Name: rptLetter; View: Print Preview; Filter Name: blank; Where
Condition: [ExamData]![ExamID]=[Forms]![frmPatient Data]![frmExam
Data]![ExamID].

I noticed that there is a filter "not on" in the Property of the
{rptLetter}. The filter is: ([ExamData]![ExamID]=[Forms]![frmPatient
Data]![frmExam Data]![ExamID]) which is duplicated in the Where
Condition of the macro above; and the next property Filter On: is
"No". Does this make sense!

I have a 2nd macro {ClearNotified} which I can not find where it is
being used in the database; it has 1 action:
SetValue: Item: [Forms]![frmExam Data]![ResultsNotified]; Expression:
Null

Further, there is also a filter on the subform {frmExam Data} as:
((Not Lookup_ResultsID.ExamResults1="WNL")); and Order by:
Lookup_Provider.Provider. I don't know if this is affecting the
error. Any suggestions will be greatly appreciated. Thanks.

[Larry Linson]

Sorry, but IMNSHO, subdatasheets are a violation of relational database
design guidelines so I don't use them, or suggest their use, and, thus,
don't answer questions about them. Because they do not support error
handling, I do not use macros, except on rare occasions, the AutoExec and
AutoKeys macros.

Others take a different view on both subjects, and perhaps will be able to
assist you. I posted this just so you'd know that your post was not being
ignored.

Larry Linson
Microsoft Access MVP
"Curtis" <cu************@us.army.mil> wrote in message
news:11*********************@i40g2000cwc.googlegro ups.com...

I am having a problem with the coding below that someone was trying to
help me with in another website. I have been to several websites and
hopefully here I can get it resolved. I inherited this database and
issue, and I apologize for a bit lengthy. The coding is located in the
Event Procedure on the "On Open" property of the {rptLetter}:

Sub cmdPrint_Click()
'Check if no results in any field, if all missing
'inform user and exit report
'Check that [frmExam Data] is the name of the
'subform control, not just the name of the form
'contained.
If Me.[frmExam Data].Form.Trichomonal <> True _
And Me.[frmExam Data].Form.Bacterial <> True _
And Me.[frmExam Data].Form.Yeast <> True Then
MsgBox "This individual does not have any Exam Data entered." & _
Chr(13) & "Previewing / Printing is canceled. " & _
Chr(13) & Chr(13) & "Check if Exam Data is Available and Re-run",
vbOKOnly +
vbInformation
Else
DoCmd.OpenReport rptLetter, acViewPreview
End If

End Sub

My result I want is: when I press the Cmd Button on the {frmPatient
Data}, linked to the macro {UpdatePrintNotice}, if there is a matching
subrecord, I want to view the {rptLetter} ready for printing, and
update the [ResultsNotified] field automatically in the subdatasheet
table {ExamData} as it gets printed. If any of the 3 fields
[Trichomonal], [Bacterial], and [Yeast], Yes/No fields located in the
subdatasheet table, are actively checked, display only the contents of
those that are checked, don't display. If there is no matching
subrecord, I want the msg: "This individual does not have any Exam
Data entered. Check if Exam Data is Available and Re-Run." I want
the printing to be cancelled and return back to the {frmPatient Data},
without any further updates.

Currently: 1) After pressing the Cmd Button, for those with a
subrecord, the data, including all 3 fields, regardless if they are
actively checked or not is printed. The active fields should be
displayed only if they are actively checked. Upon closing, the
[ResultsNotified] is automatically updated which should be the case.
2) For those individuals without a subrecord, the {rptLetter} is
displayed for printing, which shouldn't be. The Msg Box does not
appear at all. When closing, the [ResultsNotified] field still gets
automatically updated, creating a new record in the subdatasheet table
{Exam Data},which shouldn't be the case.

Information: The {frmPatient Data} has a subform {frmExam Data}; table
source is {PatientData} and {ExamData} respectfully. The [SSN] is the
link between the 2 tables. The [SSN] is the Primary Key in the 1st
table and [ExamID] in the 2nd table. The 3 fields [Trichomonal],
[Bacterial], and [Yeast] are fields in the subdatasheet {ExamData}
which are Yes/No fields.

The macro [UpdatePrintNotice] is attached to the Cmd Button "Print
Notification", "On Click" property, which has 2 actions:
SetValue: Item: [Forms]![frmPatient Data]![frmExam
Data]![ResultsNotified]; Expression: Date(); OpenReport: Report
Name: rptLetter; View: Print Preview; Filter Name: blank; Where
Condition: [ExamData]![ExamID]=[Forms]![frmPatient Data]![frmExam
Data]![ExamID].

I noticed that there is a filter "not on" in the Property of the
{rptLetter}. The filter is: ([ExamData]![ExamID]=[Forms]![frmPatient
Data]![frmExam Data]![ExamID]) which is duplicated in the Where
Condition of the macro above; and the next property Filter On: is
"No". Does this make sense!

I have a 2nd macro {ClearNotified} which I can not find where it is
being used in the database; it has 1 action:
SetValue: Item: [Forms]![frmExam Data]![ResultsNotified]; Expression:
Null

Further, there is also a filter on the subform {frmExam Data} as:
((Not Lookup_ResultsID.ExamResults1="WNL")); and Order by:
Lookup_Provider.Provider. I don't know if this is affecting the
error. Any suggestions will be greatly appreciated. Thanks.