display a field in a report only if it is "Yes"

  • Thread starter Thread starter Lisa
  • Start date Start date
L

Lisa

I have two Yes/No fields in a database. I would like to
display them in the report only if they are set to "yes".
Is there an easy way to accomplish this in Access 97.
 
I have two Yes/No fields in a database. I would like to
display them in the report only if they are set to "yes".
Is there an easy way to accomplish this in Access 97.
Click to expand...

In the "OnFormat" event procedure (in the code window, not the cell beside the
Event name) for the report section containing each field's control, enter code
similar to the following:

Me.YesNo1.Visible = Me.YesNo1.Value

Replace the "YesNo1" with the name of the control bound to the YesNo field. You
would use one such line of code for each control you wanted to hide/display. If
you need help with entering code into a procedure, just holler.
 
I'm hollering. I can not find "OnFormat" in the checkbox
properties.

Please try to keep the explanations as simpe as possible,
I am a novice at best.
Click to expand...

And I was listening. <g>

The "OnFormat" event is for the report *section* that contains the checkbox. You
can select the section by clicking on the background beside or below the
checkbox (if there's no room to click on the background, you will find a bar at
the top of that section that contains the name of the section and you can click
on that) and the "properties sheet" dialog's caption should then read "Section:
" and the name of the section (e.g. "Section: Detail"). If you can't see the
property sheet, you can select "Properties" from the "View" menu or simply
double-click on either the section background or the bar at the top of the
section. Now, click on the "Event" tab in the property sheet and you will find
"On Format" in the list. If you type a "[" in the property field beside "On
Format", the property field should autofill with "[Event Procedure]". Now click
on the "build" button (...) to the right of that property field and the code
window should open. Enter the code in the code window between the two existing
lines so that your procedure will look like this (I've used the "Detail" section
in the following example):

'**************EXAMPLE START
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.YesNo1.Visible = Me.YesNo1.Value

End Sub
'**************EXAMPLE END

Again, you need to replace "YesNo1" with the name of your checkbox and you will
need one such line of code for each checkbox you wish to show/hide in that
section. If you have other checkboxes you wish to show/hide that are located in
other sections, you will need to duplicate these steps for each applicable
section.

Let me know how you make out.
 
I would set the control source of text boxes to
=IIf([YesNoA], "Yes","")
=IIf([YesNoB], "Yes","")
Make sure the names of the text boxes are not the same as the field names.
 
Thanks for the help so far. I was able to get it to work
sort of. I have a one to many relationship for my tables
which consist of patient demographics and patient visit
information. It seems to work if in my report, I ask to
see the first visit info in the many table. If I ask to
see a second visit, it gives me a "run time error 2427
You enterd an expression that has no value. The
expression may refer to an object that has no value such
as a form, a report or a label control."

Besides the 2 check boxes I asked about, I also need to
display 2 textboxes and 2 labels that should only display
if either of the 2 check boxes is selected.

Any more ideas?

Lisa

-----Original Message-----
I'm hollering. I can not find "OnFormat" in the checkbox
properties.

Please try to keep the explanations as simpe as possible,
I am a novice at best.
Click to expand...

And I was listening. <g>

The "OnFormat" event is for the report *section* that contains the checkbox. You
can select the section by clicking on the background beside or below the
checkbox (if there's no room to click on the background, you will find a bar at
the top of that section that contains the name of the section and you can click
" and the name of the section (e.g. "Section: Detail"). If you can't see the
property sheet, you can select "Properties" from the "View" menu or simply
double-click on either the section background or the bar at the top of the
section. Now, click on the "Event" tab in the property sheet and you will find
"On Format" in the list. If you type a "[" in the property field beside "On
Format", the property field should autofill with "[Event Procedure]". Now click
on the "build" button (...) to the right of that property field and the code
window should open. Enter the code in the code window between the two existing
lines so that your procedure will look like this (I've used the "Detail" section
in the following example):

'**************EXAMPLE START
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

Me.YesNo1.Visible = Me.YesNo1.Value

End Sub
'**************EXAMPLE END

Again, you need to replace "YesNo1" with the name of your checkbox and you will
need one such line of code for each checkbox you wish to show/hide in that
section. If you have other checkboxes you wish to show/hide that are located in
other sections, you will need to duplicate these steps for each applicable
section.

Let me know how you make out.

--
Bruce M. Thompson
(e-mail address removed) (See the Access FAQ at http://www.mvps.org/access)within the newsgroups so that all might benefit.<<


.
Click to expand...
Click to expand...
 
Thanks for the help so far. I was able to get it to work
sort of. I have a one to many relationship for my tables
which consist of patient demographics and patient visit
information. It seems to work if in my report, I ask to
see the first visit info in the many table. If I ask to
see a second visit, it gives me a "run time error 2427
You enterd an expression that has no value. The
expression may refer to an object that has no value such
as a form, a report or a label control."
Click to expand...

Okay. Change that line of code to:

Me.YesNo1.Visible = Nz(Me.YesNo1.Value,0)
Besides the 2 check boxes I asked about, I also need to
display 2 textboxes and 2 labels that should only display
if either of the 2 check boxes is selected.
Click to expand...

Make sure that the labels are attached to the textboxes (this is the default)
and then use the value of the checkbox to assign the value to the "Visible"
property of the textbox (make sure that you reference the correct checkbox in
your code <g>):

Me.Text1.Visible = Nz(Me.YesNo1.Value,0)
 
You enterd an expression that has no value. The
Okay. Change that line of code to:

Me.YesNo1.Visible = Nz(Me.YesNo1.Value,0)
Click to expand...

Actually, that's not going to do anything for you either. <stupid answer, wasn't
it?>

What you need, in this case, is to implement some error handling in your code,
if you haven't already, and handle Error 2427 with a "Resume Next".

'*********EXAMPLE START
Private Sub MyProcedure ()
On Error Goto MyProcedure_Err

... Your code here ...

MyProcedure_Exit:
Exit Sub

MyProcedure_Err:
If Err.Number = 2427 Then 'If checkbox field is missing
Resume Next
Else
Msgbox "Error " & Err.Number & ": " & Err.Description
Resume MyProcedure_Exit

End Sub
'*********EXAMPLE END
 
Thanks for your patience and help, but this still does not
take care of the problem of being able to view a second
patient visit. It gives the same error message.

Lisa
 
Thanks for your patience and help, but this still does not
take care of the problem of being able to view a second
patient visit. It gives the same error message.
Click to expand...

Did you try what I suggested in my corrected response to you?
 
Thank you, We have Success!!!

Now, I need to apply that same theory to some controls
that are labels and text boxes and just plain old text.

If neither of the check boxes are selected, I do not want
the 2 checkboxes and it's labels as well as 2 plain labels
to display on the report.

How do I do that?

Thanks,
Lisa
 
Thank you, We have Success!!!

Sweeet!
Now, I need to apply that same theory to some controls
that are labels and text boxes and just plain old text.
Click to expand...

Simply apply the same technique. You already have the basics.
If neither of the check boxes are selected, I do not want
the 2 checkboxes and it's labels as well as 2 plain labels
to display on the report.

How do I do that?
Click to expand...

'***********
If Not Me.Control1.Visible and Not Me.Control2.Visible Then
'Hide other controls as needed
Else
'Unhide as appropriate
End If
'***********
 
We now have total success. Thanks for all your input, I
would have not gotten it accomplished without you.

Lisa
 
Back
Top