• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 599
  • Last Modified:

SetVisible Property in Report On Open Event

I have a report based on a query.  The query contains a memo field titled "comments" and this field can contain lengthy information that I do not want to appear on my report.  To avoid the cluster of information, I have created a label (appears as a hyperlink) with an on-click event that opens a form to display the "comments associated with that record.  The label/form combination works nice, but not ALL records have comments, and I do not want the label to be visible for those records.  

I tried adding code in the report's On Open event...

If IsNull(Me.Comments.Value) Then
    Set ProdNotes.IsVisible = False
End If

...but the code is not effective.
0
alliedtech
Asked:
alliedtech
  • 15
  • 14
  • 2
  • +1
2 Solutions
 
MootherCommented:
ProdNotes.Visible = not nz(len(Me.Comments),-1)
0
 
alliedtechAuthor Commented:
Thanks for the suggestion, but I was unable to open the report using that code.
Private Sub Report_Open(Cancel As Integer)
ProdNotes.Visible = Not Nz(Len(Me.Comments), -1)
End Sub

Open in new window

0
 
wiswalldCommented:
If  Me.Comments = False Then
    ProdNotes.Visible = False
End If
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Eric ShermanAccountant/DeveloperCommented:
Put your code in the Report's On Format event.

ET
0
 
Eric ShermanAccountant/DeveloperCommented:
Also try this version in the On Format event of the report.

If IsNull(Me.Comments.Value) Then
    Me.ProdNotes.Visible = False
Else
    Me.ProdNotes.Visible = True    
End If


ET
0
 
alliedtechAuthor Commented:
No Luck.  I tried placing the code in the report's On Open as well as the detail section's On Format.
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, try this in the On Format event of the detail section ....  This will test for Null and empty string at the same time.

If Nz(Me.Comments.Value,"") <> "" Then
    Me.ProdNotes.Visible = False
Else
    Me.ProdNotes.Visible = True    
End If


ET
0
 
alliedtechAuthor Commented:
Still no luck....it seems that you are awfully close.
0
 
Eric ShermanAccountant/DeveloperCommented:
My mistake ...

Try this in the detail section On Format event ...

If Nz(Me.Comments.Value,"") = "" Then
    Me.ProdNotes.Visible = False
Else
    Me.ProdNotes.Visible = True    
End If


ET
0
 
alliedtechAuthor Commented:
same result.  this was obviously more difficult than the 50 points I assigned to it haha.  
0
 
Eric ShermanAccountant/DeveloperCommented:
What result are you getting???

ET
0
 
alliedtechAuthor Commented:
The report opens just fine, but the ProdNotes label is visible for ALL records, even those that do not have any data in the Comments field.
0
 
wiswalldCommented:
Set the product label visible property to false in design view.
0
 
alliedtechAuthor Commented:
Opposite Result...the label did not show up for any records.
Screen.bmp
0
 
Eric ShermanAccountant/DeveloperCommented:
Yep,  turn ProdNotes off by default.  

ProdNotes is a label control located where or what section of the report??

ET
0
 
Eric ShermanAccountant/DeveloperCommented:
What is the name of your report???

ET
0
 
alliedtechAuthor Commented:
Report Name: rptP2Prod

ProdNotes label is located in the detail section of the report.

The Comments textbox is also located in the detail section, and I just verified that the comments are showing up ONLY for the records which they are associated with.
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, I take it that we have the correct control names (Comments, ProdNotes), right???

Try this in the Report Header section On Format event ...

If Nz(Me.Comments.Value,"") = "" Then
    Me.ProdNotes.Visible = False
Else
    Me.ProdNotes.Visible = True    
End If


ET
0
 
alliedtechAuthor Commented:
Control are correct...I have checked and rechecked them a hundred times just to make sure.

I put that code in the report header...same results again.

I also tried the attached code....still no success.
If Len(Me.Comments & "") = 0 Then
    Me.Comments.Visible = False
    Me.ProdNotes.Visible = False
Else
    Me.Comments.Visible = False
    Me.ProdNotes.Visible = True
    
End If

Open in new window

0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, at this point I would need to see the report.  Do you have a sample in a database that can be uploaded to ee-stuff.com??  Preferably, Access 2000 or 2003.

ET
0
 
alliedtechAuthor Commented:
I can create a small sample DB.  I have also increased the points to 500 in case that is important to you.
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, upload it to ee-stuff.com and post the link here.

ET
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok alliedtech ...

I had a chance to review your sample.  I removed your unboud fields from the report to make it easier for this discussion.  Basically, the code below works and should be included in the report's detail on format event.  If you look at the picture I attached you will notice each product id where the comments field is not empty the Notes label is displayed.   I have both ProdNotes label and Comments fields set to Visible for this discussion and you would want to set the Comments field Visible to No for the actual report.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Nz(Me.Comments, "") = "" Then
    Me.ProdNotes.Visible = False
Else
    Me.ProdNotes.Visible = True
End If
End Sub

BUT ....  That will not solve your ultimate goal which is to activate the Notes label such that the on click event of this field will open a form to display the comments.  You cannot do that in a Report Object.  You can only do that on a Form Object.  

Therefore, what I generally do in cases like this is format the Comments field to display under the entire record on the report.  Then prompt the user to run a summary or detail version of the report where the detail version will print the comments field and the summary version will set the height of this field to 0 and make it invisible.

ET

rptP2Prod.jpg
0
 
alliedtechAuthor Commented:
ET, Thanks for taking the time to help with this issue.

I copied your exact code, but it had no effect on my report and I have no idea why.  Is it possible that Access 2007 could be causing some sort of problem?

As far as the on click event, the attached code effectively opens a form displaying the comments associated with the record.

Private Sub ProdNotes_Click()
Dim stLinkCriteria As String
    stLinkCriteria = "[ProdID]=" & Me.ProdID
    DoCmd.OpenForm "frmP2ProdNotes", , , stLinkCriteria
End Sub

Open in new window

0
 
Eric ShermanAccountant/DeveloperCommented:
Humm ...  That's strange then because it works everytime with no problems (the Notes label).

As far as the on click event that's probably an Access 2007 thing and if it's working, ok.

ET
 
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, one last try ...

This is the only code I have in my report.  Using the sample db you uploaded see if you can make your report look like the one in the attached picture.  Comments and ProdNotes are set to Visible = Yes.  Make sure you don't have any other code events in your report relating to the comments/prodnotes controls, ok.

Private Sub ProdNotes_Click()
Dim stLinkCriteria As String
    stLinkCriteria = "[ProdID]=" & Me.ProdID
    DoCmd.OpenForm "frmP2ProdNotes", , , stLinkCriteria
End Sub

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Nz(Me.Comments, "") = "" Then
    Me.ProdNotes.Visible = False
Else
    Me.ProdNotes.Visible = True
End If
End Sub

ET

rptP2Prod3.jpg
0
 
alliedtechAuthor Commented:
Strange.   I opened the database using 2003 and it worked perfectly.  I open the same database in 2007 and it does not work.  
0
 
Eric ShermanAccountant/DeveloperCommented:
Ok, at least we know the code works.  

Sounds like the Detail section On Format Event is not firing the same way in Access 2007.  

I haven't done very much in Access 2007 yet (most of the db's I support ...the clients prefer to remain in 2003 and earlier versions).  

At this point it's probably best to close out this question and open a new one directed at Access 2007 as to why that event does fire the same way as in 2003.  It's probably something simple.

ET
0
 
alliedtechAuthor Commented:
Thanks again for your assistance.  Let me know what needs to be done in regards to assigning points, etc.
0
 
Eric ShermanAccountant/DeveloperCommented:
Just post a question in the General Community asking the Moderator to close & refund the points for this question (copy the link to this question and paste it in there for reference).  Explain that no answer was found here and a new question needs to be opend directed specifically at Access 2007 since you know the code works in Access 2003.

ET
0
 
alliedtechAuthor Commented:
Conclusion...

On Cick events DO NOT fire in Print Preview
OnFormat events ONLY fire in Print Preview
0

Featured Post

Learn to develop an Android App

Want to increase your earning potential in 2018? Pad your resume with app building experience. Learn how with this hands-on course.

  • 15
  • 14
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now