Changing rows in the detail section of an Access Report

[jbandy98]

Hello everyone, and thanks for taking a look at my thread. Here is the problem I am having. I am trying to take an access report of about 10-20 users and loop through each report to make a report for each person on that report. Their report would only show their name next to their data, and I need it to blank out all other names on the report. So it kind of looks like this - no making fun of the drawing =)

Name Calls Time Productivity
Joe 50 10:25 75%
Sue 75 12:34 56%
Bob 85 14:23 43%

And from this I need it to make 3 reports, one for each person on the list. So for Joe the report should look like:

Name Calls Time Productivity
Joe 50 10:25 75%
***** 75 12:34 56%
***** 85 14:23 43%

I hope this is something someone can help me with. Any help is appreciated. All I have left to figure out on this project is how to get the other names blanked out that I am not on, but it still needs to show the rest of their data. Thanks!

 

[Remou]

Hi
I have just tried conditional formatting on a report. It might help, if blank suits. For example, text colour = white if name <> Joe.

 

[jbandy98]

This would be helpful and work I think, but I have a total of 150 names to go through and we are trying to automate the process using vb. I should have included this in my first posting. So the conditional formatting would have to be implemented through each pass in the code for each name. If I can figure out the code for that end of this then I think it would work. Anyone have any ideas? I will post my code below, what I have so far:

Set rst = CurrentDb.OpenRecordset("OneMonthOneAssociate")
rst.MoveFirst
With rst
Do Until .EOF
rCount = rst.RecordCount
curName = .Fields(0)
'loop through each data record
For i = 0 To rst.RecordCount

If (Reports!AssociateScorecard!NAME = .Fields(0)) Then
Reports!AssociateScorecard!NAME = curName
Else
Reports!AssociateScorecard!NAME = "**********"

End If
Next i
Reports!AssociateScorecard!lblName = curName
DoCmd.OpenReport "AssociateScorecard", acViewPreview
DoCmd.PrintOut
.MoveNext
Loop
.Close
End With
Set rst = Nothing

The if statement in the middle does not work but was my attempt to figure a way to do this, and maybe it kind of explains more of what I am trying to do. Thanks again,

 

[crobg]

If you don't want to set that everytime you run the report you will need to do some programming.

First you will need to call the reprot with a form. From an unbound form add a command button. Add the following code in it's OnClick event procedure. Please note that you will need to change table and field names where needed.

Private Sub Command0_Click()

Dim db As DAO.Database, rs As DAO.Recordset, strSQL As String, x As Long, strOpen As String

strSQL = "SELECT [strName] FROM [table];"

Set db = CurrentDb()
Set rs = db.OpenRecordset(strSQL)

rs.MoveFirst

Do Until rs.EOF = True
    DoCmd.OpenReport "report1", acViewNormal, , , , rs![strName]
    rs.MoveNext
Loop

rs.Close
Set db = Nothing

End Sub

Next make the name of the current person Black and everyone else white on your report. Put the following code in the OnFormat event procedure of the Detail section of your report.

Private Sub Detail_Print(Cancel As Integer, PrintCount As Integer)
Me.strName.ForeColor = 16777215
If Me.strName = Me.OpenArgs Then
    Me.strName.ForeColor = 0
End If
End Sub

Finally make sure that DAO is selected in your references. (From any module screen go to menu Tools->References.)

 

[crobg]

I didn't refresh my screen before I posted. It looks like you have the basic idea of looping through the records. The conditional formating is done in the OnFormat event of your report.