[RESOLVED] ListView Help Needed

[encoduane]

OK, on my form, I have a ListView that is being populated with the AddItems call. When I click on one of the records in the ListView, my code runs a SQL statement and the data retured is stored in a recordset. This data is then used to populate text boxes and labels on the same form.

Now one of the fields that is populated is a comments field and on the form, I have another text box and a button that allows the user to enter in new comments. When the user clicks the button to add in the new comments, the data is written to the database. Once this happens, I want the code to basically click the same record it is on so that the data on the form is updated. The problem is when I try to make the call to the _ItemClick event, I have issues passing the selected item to the event and thus will not work the way I want it to.

Thanks


Here is the code for the _ItemClick event (Note - there is more code than what I am showing - but too many characters to post here).

Private Sub lstClient_ItemClick(ByVal Item As MSComctlLib.ListItem)
    strNCMID = cboNCM.Text
    strWOCLNPRJ = Replace(strVal, "'", "''")
    
    lstClient.HideSelection = False
    
    ............
    
        
End Sub

Here is the code that is run when the user enters comments and click the button to add them to the database. Where I am running into problems is when you see the line of code

lstClient.ListItems.Item(1).Selected = True

While this code highlights the record, it does not force the _ItemClick event and that is what I need to do. Is there a way to do this?

Private Sub cmdEdit_Click()
    strWOCLNPRJ2 = Replace(lstClient.SelectedItem, "'", "''")
    intRecord = 0
    
    If txtAdd.Text & "" <> "" Then
        If chkApply.Value = 0 Then
            Set RS1 = New ADODB.Recordset
            strSQL = "SELECT Count(*) AS Ct FROM ENCONCM_WOComment WHERE NCMID='" & cboNCM.Text & "' "
            strSQL = strSQL & "AND WOCLNPRJ='" & strWOCLNPRJ & "' "
            RS1.CursorLocation = adUseClient
            RS1.Open strSQL, frmMain.ElmConnection, adOpenDynamic, adLockReadOnly
            intRecord = RS1!Ct + 1
            RS1.Close
            Set RS1 = Nothing
            
            intNum = 0
            txtComment.Text = ""
            strComment = ""
            ReDim SQLString(intNum)
            strSQL = "INSERT INTO ENCONCM_WOComment (NCMID, WOCLNPRJ, Type, Name, CDate, Comment, Record, WO) "
            strSQL = strSQL & "VALUES ('" & cboNCM.Text & "', '" & strWOCLNPRJ2 & "', '" & strUserType & "', '"
            strSQL = strSQL & strUser & "', '" & Format(Now, "dd-mmm-yy hh:mm:ss") & "', '" & txtAdd.Text & "', "
            strSQL = strSQL & intRecord & ", '" & Left(strWOCLNPRJ2, 7) & "')"
            SQLString(intNum) = strSQL
            
'            'check status of WOCLNPRJ before updating to Active
'            Set RS1 = New ADODB.Recordset
'            strSQL = "SELECT Status FROM ENCONCM_WO WHERE NCMID='" & cboNCM.Text & "' "
'            strSQL = strSQL & "AND WOCLNPRJ='" & strWOCLNPRJ & "' "
'            RS1.CursorLocation = adUseClient
'            RS1.Open strSQL, frmMain.ElmConnection, adOpenDynamic, adLockReadOnly
'            If RS1!Status = "Open" Then
'                intNum = intNum + 1
'                ReDim Preserve SQLString(intNum)
'                strSQL = "UPDATE ENCONCM_WO SET Status='Active' WHERE NCMID='" & cboNCM.Text
'                strSQL = strSQL & "' AND WOCLNPRJ='" & lstClient.SelectedItem & "'"
'                SQLString(intNum) = strSQL
'            End If
'            RS1.Close
'            Set RS1 = Nothing
            
            'check status of NCMID before updating to Active
            Set RS1 = New ADODB.Recordset
            strSQL = "SELECT Status FROM ENCONCM WHERE NCMID='" & cboNCM.Text & "' "
            RS1.CursorLocation = adUseClient
            RS1.Open strSQL, frmMain.ElmConnection, adOpenDynamic, adLockReadOnly
            If RS1!Status = "Open" Then
                intNum = intNum + 1
                ReDim Preserve SQLString(intNum)
                strSQL = "UPDATE ENCONCM SET Status='Active' WHERE NCMID='" & cboNCM.Text & "'"
                SQLString(intNum) = strSQL
                strStatus = "Active"
            Else
                strStatus = RS1!Status
            End If
            RS1.Close
            Set RS1 = Nothing
            
            tempstring = ExecuteSQL(frmMain.ElmConnection, SQLString)

            If tempstring <> vbNullString Then
                MsgBox "There was an error - please take a screen capture of this error and email to the IT Department." & vbCrLf & vbCrLf & _
                       "To screen capture, hold down the Alt button and then press the Print Screen button - then paste into an email." & vbCrLf & vbCrLf & _
                        tempstring, vbOKOnly, "Error"
            Else
'                Call cboNCM_Click
                lblStatus = "NCM Status: " & strStatus

                lstClient.ListItems.Item(1).Selected = True
                frmReview.Refresh
'                lstClient.ListItems.Item(1).Selected = True
'                lstClient.SelectedItem = strWOCLNPRJ2
'                strV = CVar(strWOCLNPRJ)
                
'                Call WOCLNPRJ(strWOCLNPRJ2, strWOCLNPRJ)
            End If
        Else
            Set RS1 = New ADODB.Recordset
            strSQL = "SELECT WOCLNPRJ FROM ENCONCM_WO WHERE NCMID='" & cboNCM.Text & "'"
            RS1.CursorLocation = adUseClient
            RS1.Open strSQL, frmMain.ElmConnection, adOpenDynamic, adLockReadOnly
            ReDim SQLString(0)
            intNum = 0
            Do While Not RS1.EOF
                strWOCLNPRJ = Replace(RS1!WOCLNPRJ, "'", "''")
                Set RS2 = New ADODB.Recordset
                strSQL = "SELECT Count(*) AS Ct FROM ENCONCM_WOComment WHERE NCMID='" & cboNCM.Text & "' "
                strSQL = strSQL & "AND WOCLNPRJ='" & strWOCLNPRJ & "' "
                RS2.CursorLocation = adUseClient
                RS2.Open strSQL, frmMain.ElmConnection, adOpenDynamic, adLockReadOnly
                intRecord = RS2!Ct + 1
                RS2.Close
                Set RS2 = Nothing
                
                txtComment.Text = ""
                strComment = ""
                
                ReDim Preserve SQLString(intNum)
                strSQL = "INSERT INTO ENCONCM_WOComment (NCMID, WOCLNPRJ, Type, Name, CDate, Comment, Record, WO) "
                strSQL = strSQL & "VALUES ('" & cboNCM.Text & "', '" & strWOCLNPRJ & "', '" & strUserType & "', '"
                strSQL = strSQL & strUser & "', '" & Format(Now, "dd-mmm-yy hh:mm:ss") & "', '" & txtAdd.Text & "', "
                strSQL = strSQL & intRecord & ", '" & Left(strWOCLNPRJ, 7) & "')"
                
                SQLString(intNum) = strSQL
                intNum = intNum + 1
                RS1.MoveNext
            Loop
            RS1.Close
            Set RS1 = Nothing
            
'            'check status of WOCLNPRJ before updating to Active
'            Set RS1 = New ADODB.Recordset
'            strSQL = "SELECT WOCLNPRJ, Status FROM ENCONCM_WO WHERE NCMID='" & cboNCM.Text & "' "
'            RS1.CursorLocation = adUseClient
'            RS1.Open strSQL, frmMain.ElmConnection, adOpenDynamic, adLockReadOnly
'            Do While Not RS1.EOF
'                If RS1!Status = "Open" Then
'                    ReDim Preserve SQLString(intNum)
'                    strSQL = "UPDATE ENCONCM_WO SET Status='Active' WHERE NCMID='" & cboNCM.Text
'                    strSQL = strSQL & "' AND WOCLNPRJ='" & Replace(RS1!WOCLNPRJ, "'", "''") & "'"
'                    SQLString(intNum) = strSQL
'                    intNum = intNum + 1
'                End If
'                RS1.MoveNext
'            Loop
'            RS1.Close
'            Set RS1 = Nothing
            
            'check status of NCMID before updating to Active
            Set RS1 = New ADODB.Recordset
            strSQL = "SELECT Status FROM ENCONCM WHERE NCMID='" & cboNCM.Text & "' "
            RS1.CursorLocation = adUseClient
            RS1.Open strSQL, frmMain.ElmConnection, adOpenDynamic, adLockReadOnly
            If RS1!Status = "Open" Then
                ReDim Preserve SQLString(intNum)
                strSQL = "UPDATE ENCONCM SET Status='Active' WHERE NCMID='" & cboNCM.Text & "'"
                SQLString(intNum) = strSQL
                intNum = intNum + 1
                strStatus = "Active"
            Else
                strStatus = RS1!Status
            End If
            RS1.Close
            Set RS1 = Nothing
            
            tempstring = ExecuteSQL(frmMain.ElmConnection, SQLString)
            If tempstring <> vbNullString Then
                MsgBox "There was an error - please take a screen capture of this error and email to the IT Department." & vbCrLf & vbCrLf & _
                       "To screen capture, hold down the Alt button and then press the Print Screen button - then paste into an email." & vbCrLf & vbCrLf & _
                        tempstring, vbOKOnly, "Error"
            Else
'                Call cboNCM_Click
                lblStatus = "NCM Status: " & strStatus
                lstClient.SelectedItem = strWOCLNPRJ2
                strV = strWOCLNPRJ
                Call WOCLNPRJ_Click(strV)
'                Call WOCLNPRJ(strWOCLNPRJ2, strWOCLNPRJ)
            End If
        End If
    End If
End Sub

[some1uk03]

Try:

lstClient.ListItems.Item(1).Selected = True: lstClient_Click

Or

lstClient.ListItems.Item(1).Selected = True: lstClient_ItemClick lstClient.SelectedItem

I think 2nd one is what you're looking for.

[Ellis Dee]

Move the ItemClick code into its own procedure, and call it from both the ItemClick event and anywhere else you want to duplicate the behavior.

Private Sub lstClient_ItemClick(ByVal Item As MSComctlLib.ListItem)
    ItemClick Item
End Sub
 
Private Sub cmdEdit_Click()
    ' Tons of code here
    ItemClick lstClient.SelectedItem
End Sub
 
Private Sub ItemClick(ByVal Item As MSComctlLib.ListItem)
    strNCMID = cboNCM.Text
    strWOCLNPRJ = Replace(strVal, "'", "''")
    
    lstClient.HideSelection = False
    
    ............
    
        
End Sub

[Hack]

Why not just clear and reload the ListView?

[encoduane]

Thank you Ellis Dee and some1uk03, both of you had the answer that worked. When I was calling the lstClient_ItemCheck, I was putting my variable in parentheses, which was the problem. You solution (lstClient_ItemClick lstClient.SelectedItem) works exactly the way I want it to.

Thanks again for the quick response.