Show or Hide image on a report

[WineSnob]

I have a simple music db. I would like to place a rating (stars) in a report based on the field value [rating] in the db. If the rating of the song is 5 then I would like to show 5 stars on the report next to the record data. I have an image called stars.jpg. What type of code do I need to show or hide the images on the report based on the rating?
Thanks.

 

[Trevor G]

I have a simple music db. I would like to place a rating (stars) in a report based on the field value [rating] in the db. If the rating of the song is 5 then I would like to show 5 stars on the report next to the record data. I have an image called stars.jpg. What type of code do I need to show or hide the images on the report based on the rating?
Thanks.

Place the image in the report look at the properties and make the visible part false.

Then you need an if statement in the report in the detail selction you would need to open the properties and select the event tab and use the on print and have something like this:

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
If Me.FieldName.Value > 5Then
Me.PictureField.visible=true
Else
Me.PictureField.visible = false
End If

End Sub

 

[boblarson]

1. You put all 5 star images on the report.

2. In the tag property of each image put the number it represents. So, if on the second image it would represent the second star, then put a 2, etc.

3. Then in the On Format event of the report's detail section you can put:

Dim ctl As Control
Dim int As Integer
 
int = Me!YourFieldWithRatingNumber
 
For Each ctl In Me.Section("Detail").Controls
   If ctl.Tag In(1,2,3,4,5) Then
      If CInt(ctl.Tag) < = int Then
         ctl.Visible = True
      Else
         ctl.Visible = False
      End If
   End If
Next ctl

that will iterate through the controls on the details section and set the visible property for each star that you have set.

 

[WineSnob]

Thanks Bob, but I get a compile error on
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
Dim int As Integer

int = Me!rating Compile error here - expected identifier (rating is formatted as number

For Each ctl In Me.Section("Detail").Controls
If ctl.Tag In(1,2,3,4,5) Then
If CInt(ctl.Tag) < = int Then
ctl.Visible = True
Else
ctl.Visible = False
End If
End If
Next ctl
End Sub

 

[boblarson]

1. is RATING the actual field name? Make sure that there is no control named that on your report as well. If there is rename the control - txtRating.

2. I forgot to include code to handle nulls in case there were - so change to this:

Int = Nz(Me!Rating, 0)

 

[WineSnob]

rating is the field name. There is no other field named that on the report. I changed it to txtrating anyway. I am still getting the compile error. Expected:Identifier.
The report and table are simple. Artist, Song, Album, rating.

 

[boblarson]

Well, let's try this:

int = Nz(Me!rating.Value,0)

But if that doesn't work, then can you maybe post a copy of the database here so we can take a look at it? All of my tests on my own reports have this working fine.

Is Rating a number field?

 

[WineSnob]

Here is the db. Thanks for your help. I am sure it is something very simple. I understand the code.

 

[boblarson]

Well, here is the code I came up with to work. But it only works in Print Preview. Not with the newer 2007 Report View. I need to research that one.

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Dim ctl As Control
Dim int1 As Integer
 
int1 = Nz(Me!rating, 0)
 
For Each ctl In Me.Section("Detail").Controls
  
   Select Case ctl.Tag
   Case 1 To 5
      If CInt(ctl.Tag) <= int1 Then
         ctl.Visible = True
      Else
         ctl.Visible = False
      End If
    End Select
   
Next ctl
End Sub

 

[boblarson]

Well, according to Access MVP Allen Browne's website, that is a known "bad thing" on his list of the good/bad.

So, just make sure to view in Print Preview if you want the stars to appear correctly.

 

[WineSnob]

Thanks. I guess I'll have to live with that. If (in the future) you run across a solution, I'd like to know. What if I did it in a form rather than a report? Would the code be similiar?

 

[boblarson]

Thanks. I guess I'll have to live with that. If (in the future) you run across a solution, I'd like to know. What if I did it in a form rather than a report? Would the code be similiar?

Forms don't have the same format event. And, if you are using a continuous form it is problematic because the control is a single control and referring to it in code changes it for all records.