Insert records into ADP Access 2003 ListView

  • 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

  • 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.

  • I have removed the .Value, but I still receive the same error.

  • 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

  • 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

  • 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

  • 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