comparing values using Detail_Format 1

[scroce]

I'm trying to suppress printing out duplicate records in the detail section of my report.

I want to say something like this pseudocode:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

When the report detail formats, hold the value of txtMyText.
Then next time the report detail formats compare then new
value of txtMyText to the old value of txtMyText.

If they are the same, then don't print that line


End Sub

Reading thru some of the other postings, I think that you probably use some combination of this event along with PrintSection, MoveLayout, NextRecord properties/methods, but what is screwing me up is how to do the comparison since everytime there is a new record, the event resets all your variables?



Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[CosmoKramer]

Try setting the value of the HideDuplicates property of the text box in question to Yes.

Let me know if this helps....

 

[scroce]

ah thanks cosmo - that's an answer to part of my problem.

That works, but now I have data on my report that looks like:

Sales Rep
Customer1 Product1
Product2
Product3
Customer2 Product1
Product2


But what I want is:

Sales Rep
Customer1 Product1, Product2, Product3
Customer2 Product1, Product2


Any ideas how I could get around that? I've gotten some code samples, but nothing has proven successful yet. Acess by default seems to want to format everything vertically.


Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[CosmoKramer]

Check out scriverb's responses in the following thread:

thread703-495410

 

[scroce]

This certainly seems to be along the lines of what I'm looking for, although i'm having trouble making it work exactly right.

However this gives me something to wrestle with for a while. Thanks for your input, I'll post back!

SC

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[scroce]

Cosmo,

Still having trouble....

After playing around with this a little more, I've found that I don't think that setting HideDuplicates to "yes" is going to cut it.

If I do that, it does in fact hide the duplicates, however, the page still formats space on the page as if they were there.

In order for it to stop doing that, I believe I have to somehow employ MoveLayout=False.

Furthermore, I think need to somehow manipulate MoveLayout=False based on the record the report is about to format. If the report tries to print a record with the same customer name, I have to prevent this until it comes to a record with a new customer name.

I can't seem to figure out how to do this - or even, can I do this at all?



Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...

 

[CosmoKramer]

OK, try grouping the report by Customer too.

 

[scroce]

Once again, thanks to everyone who responded. I was able to resolve my issue thanks to this post and a few others,

Thread181-341439 and Thread703-495410 were helpful

I was able to successfully create 3 grouping levels as Cosmo suggested, CountyName, RepName and CustomerName. I didn't use the detail grouping at all, and neither did I employ the hide duplicates.

In the customer grouping, I have txtCustomerName which is a bound directly to the underlying recordset. I also have txtProducts, which is UNBOUND. It's problematic if you bind it directly to the product field becuase access wants to format them vertically rather than horizontally. So in txtProducts ControlSource property, I have called a function that wrote by piecing together bits from some of the other postings as well as my own. I called it by typing =ColumnToLine() directly in the ControlSource property of the txt products. In a module, I then wrote the following code:


Public Function ColumnToLine()

Dim dbs As Database
Dim rs As DAO.Recordset
Dim strSQL
Dim intMemID As Integer

intMemID = Reports!rptMembersByCounty!txtMemberID.Value

Set dbs = CurrentDb

strSQL = "SELECT tblMembers.MemberID.....BLAH BLAH BLAH..."

Set rs = dbs.OpenRecordset(strSQL)
If rs.RecordCount > 0 Then
Do Until rs.EOF
ColumnToLine = ColumnToLine & IIf(Len(ColumnToLine) = 0, "", ", &quot & rs("CoverageType&quot
rs.MoveNext
Loop
End If

End Function


This function searches the database independently for each customer and returns their products in a horizontal line separated by commas. It then puts that answer directly into txtProduct every time the Customer Section formats.

Again, thanks everyone for your ideas!

Ah say, there's somethin' a little "eeeeeeee" 'bout a boy who don't like basbawl...