Hiding row data from a report

[newbie87]

Hi All,

I have a sub-report that shows data from two tables joined together. As there are multiple results shown on my report, is it possible to filter out the ones i don't want too see?

For example I have 7 rows showing on the sub-report which is what I need, but I want to add a button next to each row of data and an onClick event to allow me to hide the row from view and allow me to still see the other 6 rows? I don't want to delete this row, just hide it, there will be times where i need to hide multiple rows at the same time.

Many thanks

 

[vbaInet]

What version of Access are you using?
Are you sure you want this functionality on a report or rather on a form?

 

[newbie87]

What version of Access are you using?
Are you sure you want this functionality on a report or rather on a form?

Hi,

Yes it has to be a report as it has been requested to be used this way.

I'm using Access 2010, any idea's on how to do this?

Many thanks

 

[vbaInet]

Here's some code that you can put in the Click event of the button:

    With Me
        If Right(.Filter, 1) = ")" Then
            .Filter = Left(.Filter, Len(.Filter) - 1) & "," & .[ID] & ")"
        Else
            .Filter = "[ID] NOT IN (" & .[ID] & ")"
        End If
        
        If Not .FilterOn Then .FilterOn = True
    End With

... where [ID] is the name of the field that uniquely identifies that row.

 

[newbie87]

Hi,

This is now my code, the code is working, but with a few flaws.

  With Me
        If Right(.Filter, 1) = ")" Then
            .Filter = Left(.Filter, Len(.Filter) - 1) & "," & .[Age] & ")"
        Else
            .Filter = "[Age] NOT IN (" & .[Age] & ")"
        End If
        
        If Not .FilterOn Then .FilterOn = True
    End With

I want to show people who are 18, I click on the button where the Age's are NOT equal to 18 and all data disappears from the report (even the ones within the age as 18). Any idea's on how to solve this?

 

[vbaInet]

There's no flaw in the code, but in your description of the problem.

If you had clicked 18, and then clicked other Ages, it will hide BOTH and the others. If you don't want this behaviour you need to adjust the IF block of the code.

 

[newbie87]

Hi Sorry,
i dont think i explained myself very well.

The code works, but i have flaws in what i need to do. I'll explain how my form and report work.

I have a main form where i can search for people by using a textfield. For example if I search for Sarah, the sub-report will populate with all the Sarah's in my table. Then i want to filter the sub-report by Sarah's who are only 18 by using the button next to the row's of data. I click the button on row's next to Sarah who aren't 18 to hide the row, but when i click on the button a parameter value message window appears showing the age of the person i have just clicked, i then have to enter this value again, click ok, and the row disappears which is suppose to happen.

Is there anyway of doing this without the parameter value message window showing?

 

[vbaInet]

If you're getting prompted then you haven't written the correct field or control names in the code. Cross check them and if you're still having no luck, upload your db let me see what you've done wrong.

Also, what should it hide if you click Age 17 followed by Age 19? Should it hide both ages?

 

[newbie87]

vbaInet,

Many thanks for all of your help and patience this is now working, thank you. I was entering the incorrect control names.

 

[vbaInet]

No problemo!