August 21, 2008 at 9:39 am
Hi,
I am trying to use a listview so that I can use conditional formatting on that listview to make text green for certain records. I would use a listbox, but you can't use conditional formatting on a listbox, and I can't seem to get around it.
I found some code on microsoft's support site (http://support.microsoft.com/kb/155178), but this is for an mdb. I have tried to alter the code to work in my adp, but I keep getting an error 380 "Invalid Property Value" on the "NewLine.SubItems(intCount2) = rst(intCount2).Value" line. The error seems to point to the RHS of the equals sign (the rst(intCount2).Value part).
The listview is called lstRiskDescription. In the recordset, the data types for the columns are:
RiskID = integer (PK of the Risk table)
Risk = varchar(25)
RiskDescription = varchar(250)
Opportunity = bit
My code is below:
Public Sub FillRisks()
On Error GoTo ErrorHandler
'Dim rs As DAO.Recordset
'Dim db As Database
Dim strSQL As String
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim intTotCount As Integer
Dim intCount1 As Integer
Dim intCount2 As Integer
Dim NewLine As ListItem
Set conn = Application.CurrentProject.Connection 'Set db = CurrentDb()
strSQL = "SELECT RiskID, Risk, RiskDescription, Opportunity FROM dbo.Risk WHERE RiskEstimateID = " & lngRiskEstimatingID & " ORDER BY Opportunity, Risk"
rst.Open strSQL, conn, adOpenDynamic, adLockOptimistic 'Set rs = db.OpenRecordset(strSQL)
With Me.lstRiskDescription
.View = lvwReport
.FullRowSelect = True
.ListItems.Clear
.ColumnHeaders.Clear
End With
If rst.RecordCount <> 0 Then
rst.MoveLast
intTotCount = rst.RecordCount
rst.MoveFirst
For intCount1 = 1 To intTotCount
If IsNumeric(rst(0).Value) Then
Set NewLine = Me.lstRiskDescription.ListItems.Add(, , Str(rst(0).Value))
Else
Set NewLine = Me.lstRiskDescription.ListItems.Add(, , rst(0).Value)
End If
For intCount2 = 1 To rst.Fields.Count - 1
NewLine.SubItems(intCount2) = rst(intCount2).Value
Next intCount2
rst.MoveNext
Next intCount1
End If
rst.Close
conn.Close
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If err = 3021 Then ' no current record
Resume Next
Else
MsgBox "Error No: " & err.numBer & "; Description: " & err.Description
Resume ErrorHandlerExit
End If
End Sub
Does anyone know where im going wrong?
Many thanks,
Andrew
August 21, 2008 at 9:36 pm
In MS Access you do not have to reference the ".Value" property to get the current value. Specify no property at all, and the data of the field should be returned by default. I.E.: use rst("FieldName") or rst(numeric_ordinal) to retrieve your data.
This is different than VB and VB.NET syntax, where you would normally refer to the "Text" or "Value" properties.
August 22, 2008 at 2:42 am
I have removed the .Value, but I still receive the same error.
August 22, 2008 at 3:04 am
Solved it. You need to have column headers set up, so that you can actually put data into that column, who'd of thought it :hehe:!. There is an option in the properties of the listview that hide column headers as well, which is what I wanted really. You do have to specify this in vba though, as if you simply set the property value to "Yes", when the sub routine runs, it will overwrite the HideColumnHeaders to "No".
Heres what I have now that works:
Public Sub FillRisks()
On Error GoTo ErrorHandler
Dim strSQL As String
Dim conn As New ADODB.Connection
Dim rst As New ADODB.Recordset
Dim intTotCount As Integer
Dim intCount1 As Integer
Dim intCount2 As Integer
Dim NewLine As ListItem
Set conn = Application.CurrentProject.Connection
strSQL = "SELECT RiskID, Risk, RiskDescription, Opportunity FROM dbo.Risk WHERE RiskEstimateID = " & lngRiskEstimatingID & " ORDER BY Opportunity, Risk"
rst.Open strSQL, conn, adOpenDynamic, adLockOptimistic
With Me.lstRiskDescription
.View = lvwReport
.GridLines = True
.FullRowSelect = True
.ListItems.Clear
.ColumnHeaders.Clear
End With
With Me.lstRiskDescription.ColumnHeaders
.Add , , "RiskID", 700, lvwColumnLeft
.Add , , "Risk", 700, lvwColumnLeft
.Add , , "Risk Description", 2000, lvwColumnLeft
.Add , , "Opportunity", 2000, lvwColumnLeft
End With
Me.lstRiskDescription.HideColumnHeaders = True
If rst.RecordCount <> 0 Then
rst.MoveLast
intTotCount = rst.RecordCount
rst.MoveFirst
For intCount1 = 1 To intTotCount
If IsNumeric(rst(0)) Then
Set NewLine = Me.lstRiskDescription.ListItems.Add(, , Str(rst(0)))
Else
Set NewLine = Me.lstRiskDescription.ListItems.Add(, , rst(0))
End If
For intCount2 = 1 To rst.Fields.Count - 1
NewLine.SubItems(intCount2) = Nz(rst(intCount2), "N/A")
Next intCount2
rst.MoveNext
Next intCount1
End If
rst.Close
conn.Close
ErrorHandlerExit:
Exit Sub
ErrorHandler:
If err = 3021 Then ' no current record
Resume Next
Else
MsgBox "Error No: " & err.numBer & "; Description: " & err.Description
Resume ErrorHandlerExit
End If
End Sub
August 22, 2008 at 4:45 am
Actually I do have one more query. I have an unbound text box on the form the listview is on, and when I click an item in the listview, how do I store the RiskID (data in the first column) for that item in the text box?
Many thanks,
Andrew
August 23, 2008 at 11:07 am
You are using the wrong event. Use ListSelection_SelectedIndexChanged event. But, be careful, because this event fires twice on each selection. Once to deselect the old selection and once to select the new item. On the former, if you are only selecting one item, there will be no SelectedItems! So put this code in the event.
If ListSelection.SelectedItems.Count = 0 Then Exit Sub
Now, having passed the check above, access the columns in the ListView by SubItem:
textBox1.Text = ListSelection(ListSelection.SelectedItems(0).SubItems(0).Text ' first column
The SubItem(0).text is the same as ListView.Item(n).Text
textBox1.Text = ListSelection(ListSelection.SelectedItems(0).SubItems(1).Text ' second column
Note, that you must set the value to textBox1.Text, not textBox1 as in VB6.
source: http://www.knowdotnet.com/forums/topic.asp?TOPIC_ID=356
August 26, 2008 at 3:56 am
That points to vb.net, whereas im using vb6 :). I had solved it before, was really simple actually:
Private Sub lstRiskDescription_Click()
Me.RiskID = Me.lstRiskDescription.SelectedItem
End Sub
Ive come to find that when the form opens, when I click the first item, the first field (which is hidden) displays, which I don't want. The first item isn't selected when the form opens (as in it isn't highlighted), but I can see that the focus shifts from the first item to the other item if I click a different item.
If I click on a different item, the first field doesn't display, but it does if I click on that same item again. Does anyone know a way around that first field not displaying?
Thanks,
Andrew
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply