Questions Regarding Listview?

[Salvatore]

I have a couple of questions for those who have successfully utilized the Listview control on their projects...

1. I would like to center the data within the Listview so that it is centered under it's appropriate ListItem..( not a major one for the completion of my project...)

2. I need to round up the values that come from my SQL, which fill the ListView...( this is important)...I know there are Round functions, but I have not been successful with it...

I have 3 fields that I need to round up to the 2nd decimal place...i.e: 35.66666677 to 35.67

I tried to create 3 double variables then set the variables to the value coming from the recordsets...

But I keep running into NULL values, which there very well maybe NUll coming from the SQL statement...

This is the code I have...now it is long so please be forgiving

Dim pitmQuality As ListItem
    Dim prstQuality As New ADODB.Recordset
    Dim qPhone As Double, qEmail As Double, qMail As Double
    
    Qssql = "Select AVG (Phone)  as PHONE , AVG(Mail) as MAIL, AVG (Email) as EMAIL , AVG (ACW) as ACW, AVG (AUX) as AUX, AVG (ACD) as ACD, AVG (Talk) as Talk, " _
            & "RepName from ( Select [Phone Score 1] as Phone ,[Mail Score 1] as Mail , [Email Score 1] as Email , [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union All Select [Phone Score 2] as Phone ,[Mail Score 2] as Mail , " _
            & "[Email Score 2] as Email , [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union All Select [Phone Score 3] as Phone ,[Mail Score 3] as Mail ,[Email Score 3] as Email ,[ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 4] as Phone, [Mail Score 4] as Mail, " _
            & "[Email Score 4] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 5] as Phone, [Mail Score 5] as Mail, [Email Score 5] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 6] as Phone, [Mail Score 6] as Mail, " _
            & "[Email Score 6] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 7] as Phone, [Mail Score 7] as Mail, [Email Score 7] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 8] as Phone, [Mail Score 8] as Mail, " _
            & "[Email Score 8] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 9] as Phone, [Mail Score 9] as Mail, [Email Score 9] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 10] as Phone, [Mail Score 10] as Mail, " _
            & "[Email Score 10] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 11] as Phone, [Mail Score 11] as Mail, [Email Score 11] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 12] as Phone, [Mail Score 12] as Mail, " _
            & "[Email Score 12] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 13] as Phone, [Mail Score 13] as Mail, [Email Score 13] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 14] as Phone, [Mail Score 14] as Mail, " _
            & "[Email Score 14] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 15] as Phone, [Mail Score 15] as Mail, [Email Score 15] as Email, [ACW] as ACW, [AUX] as AUX, [ACD] as ACD, [TalkTime] as Talk, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "') as MyTemp group by RepName"
 
    Set prstQuality = New ADODB.Recordset
    prstQuality.Open Qssql, CN, adOpenDynamic, adLockOptimistic
    
    qPhone = prstQuality("Phone")  ' here is where the error Invalid use of NULL occurs
    qEmail = prstQuality("Email")
    qMail = prstQuality("Mail")
    
    'lvwAgents.Icons = ilsLargeIcons
    'lvwAgents.SmallIcons = ilsSmallIcons
    lvwAgents.View = lvwReport
    lvwAgents.ListItems.Clear
    'create headers
    lvwAgents.ColumnHeaders.Clear
    lvwAgents.ColumnHeaders.Add , , "Rep Name", 1500
    lvwAgents.ColumnHeaders.Add , , "ACW", 1000
    lvwAgents.ColumnHeaders.Add , , "AUX", 1000
    lvwAgents.ColumnHeaders.Add , , "ACD Calls", 1000
    lvwAgents.ColumnHeaders.Add , , "AVG Talk Time", 1400
    lvwAgents.ColumnHeaders.Add , , "AVG Phone Scores", 1400
    lvwAgents.ColumnHeaders.Add , , "AVG Email Scores", 1400
    lvwAgents.ColumnHeaders.Add , , "AVG Mail Scores", 1400
    
       'add list items
       
    Do Until prstQuality.EOF
        Set pitmQuality = _
            lvwAgents.ListItems.Add()
        pitmQuality.Text = prstQuality("RepName")
        pitmQuality.ListSubItems.Add , "ACW", _
                prstQuality("ACW") & ""
            pitmQuality.ListSubItems.Add , "AUX", _
                prstQuality("AUX") & ""
            pitmQuality.ListSubItems.Add , "ACD", _
                prstQuality("ACD") & ""
            pitmQuality.ListSubItems.Add , "Talk Time", _
                Format(prstQuality("Talk"), "hh:mm") & ""
            pitmQuality.ListSubItems.Add , "Phone", _
                Round(qPhone,2) & ""    ' I can not even get to this part
            pitmQuality.ListSubItems.Add , "Email", _
                qEmail & ""
            pitmQuality.ListSubItems.Add , "Mail", _
                qMail & ""
 
        
        prstQuality.MoveNext
    Loop

So this is where I am at...thanks in advance!

[leinad31]

To handle nulls...

for strings
result = rs.Field("X").Value & ""

for numerics
result = IIF(IsNull(rs.Field("X")), 0, rs.Field("X)) 'set null to zero, return value as is is not null

[ahara]

Hey guys;

Question: will code crash if you try to Format a null value...I ask this as Salvatore IS concatenating a zero length string to the fields just in case, but the line where he indicates it is crashing uses the format function...I am wondering if this is causing the crash....may need to use isNull and pass to a variable before adding to the listview....as for the alignment:

lvwMain.ColumnHeaders(i).Alignment = 0, 1, or 2

0 = left
1 = right
2 = center

One warning however....the first column that holds the itmes must always be left aligned (just a quirck with the control)

[leinad31]

Welll ... if he's using IIf() then in the condition wherein the value is not Null he can do the formatting there.

IIf(IsNull(rs.Field("X")), "12:00", Format("Hh:Mm"))

and for strinbgs, append "" first

Format(rs.Field("X") & "", "@@@@@")

[Salvatore]

While I posted this...I tried a very basic Round(expression, 2) to the 3 fiedls in question and it worked...

Thanx AHARA for the alignment issue....

There is still a kink I have to wrinkle out regarding printing all this data...while I had it in my head to use a Datareport, I am not sure now that will work...

Maybe you guys can tell me if I am looking at this wrong...Since the Datareport will be somewhat Statis, I do not know how to code it so that the Datareport will show the appropriate data from the SQL statement you saw in the my thread...

What I mean is that I can set the controls of the datareport to the prstQuality("Phone") but it will only give me the first record....

Now if i place a loop in here with the data being sent to the DataReport, I do not know if that will work..because the Datareport would need to know which control to fill...and there is no way (at least for me) to code that????

So I went back to Ahara's snippet for printing the ListView..and it looked pretty plain...if I have to use it then I will, but maybe you know how I can create this look:

-----center----> CPMS Monthly Report <-----------center---------
[space]
--------center---->Averages<----------center----------
[space]
Rep Name_ ACW_ AUX_ ACD Calls_ Talk Time_ Phone Score etc...
[space]
1st record
[space]
2nd record

I am able to accomplish the column headings :

Printer.FontName = "arial"
    Printer.FontUnderline = True
    Printer.FontSize = 7
    Printer.Orientation = vbPRORLandscape
    Printer.Print "Rep Name"; _
                  Tab(20); "Average ACW"; _
                  Tab(40); "Average AUX"; _
                  Tab(60); "Average ACD Calls"; _
                  Tab(90); "Average Talk Time"; _
                  Tab(120); "Average Phone Score"; _
                  Tab(150); "Average Email Score"; _
                  Tab(180); "Average Mail Score"
                  
    Printer.FontUnderline = False
    
    For intIndex = 1 To lvwAgents.ListItems.Count
        Printer.Print lvwAgents.ListItems(intIndex); _
                      Tab(20); lvwAgents.ListItems(intIndex).ListSubItems(1); _
                      Tab(40); lvwAgents.ListItems(intIndex).ListSubItems(2); _
                      Tab(60); lvwAgents.ListItems(intIndex).ListSubItems(3); _
                      Tab(90); lvwAgents.ListItems(intIndex).ListSubItems(4); _
                      Tab(120); lvwAgents.ListItems(intIndex).ListSubItems(5); _
                      Tab(150); lvwAgents.ListItems(intIndex).ListSubItems(6); _
                      Tab(180); lvwAgents.ListItems(intIndex).ListSubItems(7)
    Next
    
    Printer.EndDoc

Thanks for all the help!

[Salvatore]

and....Ahara...the alignment code that you provided, can I use that for the data as well as the column headings??

[ahara]

the alignment of the text in the cells is set according to the columnheaders alignment - so you should see the text align the same way as the header by coding this way...wish I could help you with the data report - that's an area where I lack expertise

I am making an effor to learn though....

I am checking this link out later tonight after work:

http://www.vbcity.com/forums/faq.asp...port&#TID20722

It shows how to use data report with and without data environment. Cheers

[Salvatore]

Ok I will try some alternatives to this printing thing...

But a major concern about the SQL has just appeared!!!

I guess I will need to pull data for the listview from 2 tables instead of just 1...

As I am confident that it can be accomplished...I am not as confident of my skills...

Maybe you know off hand, I can I just make an adjustment to the SQL that is in the beginning of this thread...by changing the table to the fields ACW, AUX, ACD, and TalkTime to tblProductivity rather than tblHBUS....

If it is that easy...then WHOOPEEE...but if not then DOU!

[ahara]

for the SQL, as long as the fields match in both tables and the 'Union' clause is combining select statements that have the same number and name of fields, it should work.....of course you will have to make sure each field is referencing the right table name so you will have to change your FROM clause....

from tblHBus a, tblProductivity b


and then the field names will have to be a.phonescore1, b.ACW, etc. to reference the appropriate table

[Salvatore]

Ahara, what do you think of this....I found out that I do not need to use the AVG for the 4 fields in question that are found in the other table....

So I was think of creating 2 recordsets...which may work, but I am having a problem with the code to place the appropriate values under their respective Listview column headings...

This is what I have:

Dim pitmQuality As ListItem
    Dim prstQuality As New ADODB.Recordset
    Dim prstProductivity As New ADODB.Recordset
    Dim Qsql As String, Psql As String
        
    Qsql = "Select AVG (Phone)  as PHONE , AVG(Mail) as MAIL, AVG (Email) as EMAIL , " _
            & "RepName from ( Select [Phone Score 1] as Phone ,[Mail Score 1] as Mail , [Email Score 1] as Email , " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union All Select [Phone Score 2] as Phone ,[Mail Score 2] as Mail , " _
            & "[Email Score 2] as Email , RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union All Select [Phone Score 3] as Phone ,[Mail Score 3] as Mail ,[Email Score 3] as Email , " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 4] as Phone, [Mail Score 4] as Mail, " _
            & "[Email Score 4] as Email, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 5] as Phone, [Mail Score 5] as Mail, [Email Score 5] as Email, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 6] as Phone, [Mail Score 6] as Mail, " _
            & "[Email Score 6] as Email, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 7] as Phone, [Mail Score 7] as Mail, [Email Score 7] as Email, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 8] as Phone, [Mail Score 8] as Mail, " _
            & "[Email Score 8] as Email, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 9] as Phone, [Mail Score 9] as Mail, [Email Score 9] as Email, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 10] as Phone, [Mail Score 10] as Mail, " _
            & "[Email Score 10] as Email, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 11] as Phone, [Mail Score 11] as Mail, [Email Score 11] as Email, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 12] as Phone, [Mail Score 12] as Mail, " _
            & "[Email Score 12] as Email, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 13] as Phone, [Mail Score 13] as Mail, [Email Score 13] as Email, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' Union ALL Select [Phone Score 14] as Phone, [Mail Score 14] as Mail, " _
            & "[Email Score 14] as Email, RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "' " _
            & "Union ALL Select [Phone Score 15] as Phone, [Mail Score 15] as Mail, [Email Score 15] as Email, " _
            & "RepName from tblHBUS WHERE MonthYear = 'Mar 2004' AND [Unit Manager] = '" & mgrName & "') as MyTemp group by RepName"
 
    
    Psql = "SELECT * FROM tblProductivity WHERE [Unit Manager] = '" & mgrName & "' ORDER BY RepName"
    
    Set prstQuality = New ADODB.Recordset
    Set prstProductivity = New ADODB.Recordset
    prstQuality.Open Qsql, CN, adOpenDynamic, adLockOptimistic
    prstProductivity.Open Psql, CN, adOpenDynamic, adLockOptimistic
    
    'lvwAgents.Icons = ilsLargeIcons
    'lvwAgents.SmallIcons = ilsSmallIcons
    lvwAgents.View = lvwReport
    lvwAgents.ListItems.Clear
    'create headers
    lvwAgents.ColumnHeaders.Clear
    lvwAgents.ColumnHeaders.Add , , "Rep Name", 1500
    lvwAgents.ColumnHeaders.Add , , "ACW", 1000
    lvwAgents.ColumnHeaders.Add , , "AUX", 1000
    lvwAgents.ColumnHeaders.Add , , "ACD Calls", 1000
    lvwAgents.ColumnHeaders.Add , , "AVG Talk Time", 1400
    lvwAgents.ColumnHeaders.Add , , "AVG Phone Scores", 1600
    lvwAgents.ColumnHeaders.Add , , "AVG Email Scores", 1500
    lvwAgents.ColumnHeaders.Add , , "AVG Mail Scores", 1450
    
       'add list items
       ' below here is where I am stumped...???
       
    Do Until prstQuality.EOF
        Set pitmQuality = _
            lvwAgents.ListItems.Add()
        pitmQuality.Text = prstQuality("RepName")
        pitmQuality.ListSubItems.Add , "ACW", _
                prstQuality("ACW") & ""
            pitmQuality.ListSubItems.Add , "AUX", _
                prstQuality("AUX") & ""
            pitmQuality.ListSubItems.Add , "ACD", _
                prstQuality("ACD") & ""
            pitmQuality.ListSubItems.Add , "Talk Time", _
                Format(prstQuality("Talk"), "hh:mm") & ""
            pitmQuality.ListSubItems.Add , "Phone", _
                Round(prstQuality("Phone"), 2) & ""
            pitmQuality.ListSubItems.Add , "Email", _
                Round(prstQuality("Email"), 2) & ""
            pitmQuality.ListSubItems.Add , "Mail", _
                Round(prstQuality("Mail"), 2) & ""
 
        
        prstQuality.MoveNext
    Loop

I'll hang up and listen....

I should just take a ride up to Toronto from Buffalo and see if you could just look at it and say move this here move that there...and WHOOOPEE...I am done..

[ahara]

I guess my immediate question would be - how are the two tables related (if they are at all?)....how are you going to make sure that the two recordsets are perfectly synched? Even if you could, I don't think it would be practical as soon as your DB is thrown some kind of curve, and the tables are not synched, ye be screwed!! If I understand the situation correctly, there is basically one row in the tblProductivity table for each employee? Is there an ID field in each table you can join on? Ideally, I think you might want to go this way

If possible, join the tables and use 1 sql statement to generate an initial record set that will loop the way you have it looping at present. So the join would include some sort of ID to identify the employee. Then as you loop through the initial recordset, at each iteration you query the other table with the employee ID and get the particulars, then fire them into the listview. Do you see what I am getting at? (hope its not too confusing )

For each iteration of the outer loop, there is an employee. This employee is associated with fields such as ACW, AUX, etc in the tblProductivity table....so you basically need to access that info using the info from your initial rec set - I mean you do have 'RepName', but what if two reps have the same name? ....

Do Until prstQuality.EOF
        Set pitmQuality = _
            lvwAgents.ListItems.Add()
        pitmQuality.Text = prstQuality("RepName")

        'maybe add code here
        'take value from prstQuality recset and query tblProductivity
        'get recset back and add the field values for ACW, AUX, ACD, etc.

        pitmQuality.ListSubItems.Add , "ACW", _
                prstQuality("ACW") & ""
            pitmQuality.ListSubItems.Add , "AUX", _
                prstQuality("AUX") & ""
            pitmQuality.ListSubItems.Add , "ACD", _
                prstQuality("ACD") & ""
            pitmQuality.ListSubItems.Add , "Talk Time", _
                Format(prstQuality("Talk"), "hh:mm") & ""
            pitmQuality.ListSubItems.Add , "Phone", _
                Round(prstQuality("Phone"), 2) & ""
            pitmQuality.ListSubItems.Add , "Email", _
                Round(prstQuality("Email"), 2) & ""
            pitmQuality.ListSubItems.Add , "Mail", _
                Round(prstQuality("Mail"), 2) & ""

        
        prstQuality.MoveNext
    Loop

Again, I am not entirely sure of your data, but if I read you correctly, the above should work. Hope that helps

[ahara]

gotta get home - just realized - big game on tonight!! - oh and I forgot in my last post, you are certainly more than welcome to come to T.O. anytime!! Maybe your boss can spring for expenses? Take it easy.

[Salvatore]

Ahara, that is what I was thinking as well...but I do not need to AVG the ACW, ACD, AUX, and TalkTime fields like I first thought..

And I am well aware of the problem with synching up the 2 tables...I just met with my Management team to see if there is another way we can view the data...

They were fine with seperating the data in the listview...Would it be too difficult to have (2) Listviews on my form, and populate the 1st Listview with the Quality data (Phone Score, Email Score..etc..)

And then populate the 2nd Listview with the Productivity data (ACW, AUX..etc..)

If you think I have a shot with this new thought, please let me know how I would need to adust my current layout of the Listview data...

But as I loosen the ties a little I can see that they will be tightened again by the fact that I need to create a better printing system for these listviews...

Any and all thoughts about this are welcome!!!

Thank you!

[ahara]

gotta run so I'll be quick - yeah I think you can use two listviews if you need to - I have done this before and made it work. I just positioned them accordingly - printing may be tricky though...to be honest I do not have much experience with printing and reports in general. What we need right now is for another guru to jump in!! (hint hint)

[leinad31]

Here's a sample of manually creating and filling the recordset. He user one public recordset in a module (rsPreview) for most of the simple reports. If what you need for the data report is a heirarchical recordset then you CANT do it this way. Below is a single level recordset populated from a vsflexgrid control (third party list control).

There's room for improvement here. Such as, he must have been a C++ programmer before cause he used + for concatenation... he used value from a textbox for the total instead of using the function control (summation) in the data report, etc.

Private Sub FillPreview()
 
    Set rsPreview = New ADODB.Recordset
    
    With rsPreview
        .Fields.Append "Quantity", adBSTR
        .Fields.Append "Unit", adBSTR
        .Fields.Append "Particulars", adBSTR
        .Fields.Append "Unit Price", adBSTR
        .Fields.Append "Amount", adBSTR
        .Open
    End With
    
    ' fill rsPreview
    If grdOrder.Rows > 1 Then
    
        'On Error Resume Next
        For j = 1 To grdOrder.Rows - 1
          
            'ReOrd|Test Material|Package|Unit Price|Qty|Subtotal|Class|Serial|Price Plain|Reorder Price Plain|Subtest ID
            rsPreview.AddNew
            
            If grdOrder.Cell(flexcpText, j, FindColumn("Subtest ID")) = "X111Z101" Then
                rsPreview!Quantity = ""
                rsPreview!Unit = ""
                rsPreview![Unit Price] = ""
            Else
                rsPreview!Quantity = grdOrder.Cell(flexcpText, j, FindColumn("Qty"))
                rsPreview!Unit = grdOrder.Cell(flexcpText, j, FindColumn("Package"))
                rsPreview![Unit Price] = Format(grdOrder.Cell(flexcpText, j, FindColumn("Unit Price")), "#,##0.00")
            End If
            rsPreview!Particulars = grdOrder.Cell(flexcpText, j, FindColumn("Test Material")) + " " + grdOrder.Cell(flexcpText, j, FindColumn("Serial"))
            rsPreview!Amount = Format(grdOrder.Cell(flexcpText, j, FindColumn("Subtotal")), "#,##0.00")
            
        Next j
        rsPreview.AddNew
        rsPreview!Particulars = "*** Nothing Follows ***"
        
        'On Error GoTo 0
    End If
        
    ' Header section:
    With rptInvoice.Sections("Header")
        .Controls("lblCompany").Caption = datClient.Recordset.Fields("Client Name")
        .Controls("lblAdd1").Caption = Add1
        .Controls("lblAdd2").Caption = Add2
        .Controls("lblPsych").Caption = cboPsych.List(datPsych.Recordset.AbsolutePosition)
        .Controls("lblDate").Caption = Format(DTPicker1.Value, "dd MMM yyyy")
        .Controls("lblTerms").Caption = cboTerms.text
    End With
        
    'footer
    rptInvoice.Sections("Footer").Controls("lblTotal").Caption = Format(txtTotal.Tag, "P #,##0.00")
 
'etc................

[Salvatore]

Leinad31,

Thank you for your feedback...I am still concerned with how this would work..I mean that the values from the recordset can and will be eratic...

So how can I inform the DataReport of which controls will be populated with data????

Do you know what I mean?

The listView can change from UnitManager to UNitManager...that means that each unitmanager has so many reps underneath them..so when they click on a Manager from the Treeview their reps show in the Listview...then I need to send that data that was queryed from the database into a Datareport...

Maybe I am the one confused...let me know if I am crazy or not

[Salvatore]

Ahara,

I attempted the alignment piece of the ListView and received an error - " Element not found "

This is the code:

lvwQuality.ColumnHeaders("AVG Phone Scores").Alignment = 2
    lvwQuality.ColumnHeaders("AVG Email Scores").Alignment = 2
    lvwQuality.ColumnHeaders("AVG Mail Scores").Alignment = 2
    lvwQuality.ColumnHeaders("ACW").Alignment = 2
    lvwQuality.ColumnHeaders("AUX").Alignment = 2
    lvwQuality.ColumnHeaders("ACD Calls").Alignment = 2
    lvwQuality.ColumnHeaders("AVG Talk Time").Alignment = 2

What am I doing wrong?

By the way I was able to get the SQL to work, thanx to your previous suggestion...I used the current loop but made some adjustments to the values going into the Listview...

Thank you once again!

[ahara]

this error refers to an element in the columnheaders collection that could not be found. Re-check the spelling of the column names in the alignment code against those of the .Add code - there's probably a spelling mistake.

[leinad31]

You can either create one report template for each set of columns or... you can use lots of data report textboxes and progmatically set the data report.

First case is easier to design since you'll use the GUI. The number of columns are predefined for each report type/template. You'll end up creating lots of templates though.

Second case is very difficult since you'll have to set the visible property and probably other properties too such as left, width etc. For the excess unused textboxes, so they wont generate an error, simply set them to invisible then bind them to any existing field (its invisible so it doesnt matter which as long as its bound).

eg.
Text1.DataSource = "Field1"
Text2.DataSource = "Field1"
Text2.Visible = False

[Salvatore]

Once again I am thankful for both of your assistance (Ahara & Leinad31)!!

I have been succesfull with the alignment piece and and semi successful with the printing...

Due to time constraints I have not been able to explore the DataReport possibilities, which I am sure will take a ton of code...

I am stuck with utilizing the Print Object...I have just 2 pieces to adjust with regards to the formatting....if you know anything about this please respond, otherwise I want to extend my gratitude for your self-sacrificing spirit in giving me a helping hand, while also teaching me a thing or 2...

The 2 areas in the printing is:

1. center align the data under the headings
2. a return carriage between each row of the Sublist items

This is the code I have:

Private Sub cmdPrint_Click()
    Dim intIndex As Integer
    
    Printer.FontName = "arial"
    Printer.FontUnderline = True
    Printer.FontSize = 7
    Printer.Orientation = vbPRORLandscape
    
    Printer.Print
    Printer.Print "CPMS Monthly Performance by Unit Manager"
    Printer.Print
    Printer.Print "Salvatore"
    Printer.Print String(76, "=")
    Printer.Print
 
    Printer.Print "Rep Name"; _
                  Tab(30); "Average Phone Score"; _
                  Tab(60); "Average Email Score"; _
                  Tab(90); "Average Mail Score"; _
                  Tab(120); "Average ACW"; _
                  Tab(140); "Average AUX"; _
                  Tab(160); "Average ACD Calls"; _
                  Tab(190); "Average Talk Time"
                  
    Printer.FontUnderline = False
    
    Printer.Print
    
' below is the code that sends the individual rows  for print, but they are all by default left justified.
 
' I could not sucessfully get a space between each row..
 
    For intIndex = 1 To lvwQuality.ListItems.Count
        Printer.Print lvwQuality.ListItems(intIndex); _
                      Tab(30); lvwQuality.ListItems(intIndex).ListSubItems(1); _
                      Tab(60); lvwQuality.ListItems(intIndex).ListSubItems(2); _
                      Tab(90); lvwQuality.ListItems(intIndex).ListSubItems(3); _
                      Tab(120); lvwQuality.ListItems(intIndex).ListSubItems(4); _
                      Tab(140); lvwQuality.ListItems(intIndex).ListSubItems(5); _
                      Tab(160); lvwQuality.ListItems(intIndex).ListSubItems(6); _
                      Tab(190); lvwQuality.ListItems(intIndex).ListSubItems(7)
    Next
    
    Printer.EndDoc
    
End Sub

Thank you...

[ahara]

Hey Salvatore;

For line returns, VB has a constant you can use: vbCrLf - so just concatenate this to your string where you want the line to return. As for centering, let me ask you this: Are you by chance using a mono-spaced font like courrier? If so, you can probably do it in code - otherwise you will have a hard time.

But is the listview not printing center aligned? I have never tried printing it before - only seen the code for it....maybe try to realign before the print statement again? Maybe leinad31 knows. If you were to use code, I suppose you could use the Spc function which adds as many spaces as you need like:

Printer.Print Spc((30 - len(lvwQuality.ListItems(intIndex)))/2) & lvwQuality.ListItems(intIndex);

it's a bit convoluted, so I would really investigate why the listview is not printing center aligned.

[leinad31]

Your using the priter object not ListView.Print. I dont think ListView.Print is available and if it is it would probably be a graphical (not text based) printout. Anyway, even if the item was centered in the listview, you extracted the text

Printer.Print lvwQuality.ListItems(intIndex); _
                      Tab(30); lvwQuality.ListItems(intIndex).ListSubItems(1); _
                      Tab(60); lvwQuality.ListItems(intIndex).ListSubItems(2); _
                      Tab(90); lvwQuality.ListItems(intIndex).ListSubItems(3); _
                      Tab(120); lvwQuality.ListItems(intIndex).ListSubItems(4); _
                      Tab(140); lvwQuality.ListItems(intIndex).ListSubItems(5); _
                      Tab(160); lvwQuality.ListItems(intIndex).ListSubItems(6); _
                      Tab(190); lvwQuality.ListItems(intIndex).ListSubItems(7)

so its gonna be left aligned in the printer object printout.

If your gonna be concerned with alignment then you'll have to use "Courier" or "Courier New", whichever is in the printer objects font list. You can pad spaces with the Format() function... centering would be difficult since you have to pad both left and right sides so the rest of the text for that line is aligned in their respective 'columns'