listbox problem

[pandey_om]

hi experts
i am working in VBA ,excel2003,sql-server 2000
in my project there is a form has a listbox
listbox contain the various user-name form the sql-table
when i execute the delete query the user name must be remove from the list box() at run time ,How it is possible? the code is following PLZ help

Private Sub cmdDelete_Click()
    Dim ADOConn As New ADODB.Connection
    Dim ADORset As New ADODB.Recordset
    Dim strQuery As String
    Dim id_user As String
    Dim Response As String
    Dim ID As Integer
 
    If Me.txtUserID.Text = "" Then
        MsgBox "Select a user to delete. ", vbInformation + vbOKOnly, "Delete Status"
        Exit Sub
    End If
 
    If Me.txtUserID.Text = "Tutorial" Or Me.txtUserID.Text = "Admin" Then
        MsgBox "The record for UserID " + Chr$(34) + "Tutorial" + Chr$(34) + " and " + Chr$(34) + "Administrator" + Chr$(34) + " cannot be deleted. Select a new record and try again.", vbInformation + vbOKOnly, "Delete Status"
        Exit Sub
    End If
    id_user = Me.txtUserID.Text
    Set ADOConn = New ADODB.Connection
    ADOConn.Open "Provider=sqloledb.1;Initial Catalog=Arvicon;uid=sa;pwd=; Data Source= localhost ;"
    Set ADORset = New ADODB.Recordset
Response = MsgBox("Are you sure you want to delete this record.", vbInformation + vbYesNo, "Delete Status")
    If Response = 6 Then
        strQuery = "Delete From tblUserData Where UserID='" & id_user & "'"
        Set ADORset = ADOConn.Execute(strQuery)
        MsgBox "Your record has been deleted successfully.", vbInformation + vbOKOnly, "Delete Status"
ElseIf Response = 7 Then
        cmdCancel_Click 'DoCmd.CancelEvent
    End If
 
    ADOConn.Close
    Set ADOConn = Nothing
Exit Sub
End Sub

[KGComputers]

Hi,

Please put vbcode tags in your code to make it readable for the experts...Thanks....

[pandey_om]

thanx for reply
i have put vbcode tags in my code

[RobDog888]

Option Explicit
 
Private Declare Function SendMessage Lib "user32.dll" Alias "SendMessageA" (ByVal hwnd As Long, ByVal wMsg As Long, _
ByVal wParam As Long, ByRef lParam As Any) As Long
 
Private Const LB_FINDSTRINGEXACT As Long = &H1A2
 
Private Sub Form_Load()
    Dim intIndex As Integer
    List1.AddItem "Text1"
    Me.Text1.Text = "Text1"
    intIndex = SendMessage(Me.List1.hwnd, LB_FINDSTRINGEXACT, 0&, ByVal Me.Text1.Text)
    If intIndex > -1 Then
        List1.RemoveItem intIndex
    End If
End Sub

[pandey_om]

thanx for reply i m using the Itemremove method & it is working for me ,any correction can make u in this code

Private Sub cmdDelete_Click()
    Dim ADOConn As New ADODB.Connection
    Dim ADORset As New ADODB.Recordset
    Dim strQuery As String
    Dim id_user As String
    Dim Response As String
    Dim ID As Integer
 
    If Me.txtUserID.Text = "" Then
        MsgBox "Select a user to delete. ", vbInformation + vbOKOnly, "Delete Status"
        Exit Sub
    End If
 
    If Me.txtUserID.Text = "Tutorial" Or Me.txtUserID.Text = "Admin" Then
        MsgBox "The record for UserID " + Chr$(34) + "Tutorial" + Chr$(34) + " and " + Chr$(34) + "Administrator" + Chr$(34) + " cannot be deleted. Select a new record and try again.", vbInformation + vbOKOnly, "Delete Status"
        Exit Sub
    End If
    id_user = Me.txtUserID.Text
    Set ADOConn = New ADODB.Connection
    ADOConn.Open "Provider=sqloledb.1;Initial Catalog=Arvicon;uid=sa;pwd=; Data Source= localhost ;"
    Set ADORset = New ADODB.Recordset
    'rst.FindFirst "UserId= " + Chr$(34) + id_user + Chr$(34)
    'If Not rst.Bookmarkable = ID Then
    'ID = rst.Fields("ID")
    'End If
    Response = MsgBox("Are you sure you want to delete this record.", vbInformation + vbYesNo, "Delete Status")
    If Response = 6 Then
        strQuery = "Delete From tblUserData Where UserID='" & id_user & "'"
        Set ADORset = ADOConn.Execute(strQuery)
       [B] ListBox1.RemoveItem ListBox1.ListIndex[/B] :eek2: 
        MsgBox "Your record has been deleted successfully.", vbInformation + vbOKOnly, "Delete Status"
        
    'If Not rst.BOF Then
    'Previous_Record_Click
    ' Else
    'End If
    ElseIf Response = 7 Then
        cmdCancel_Click 'DoCmd.CancelEvent
    End If
 
    ADOConn.Close
    Set ADOConn = Nothing
Exit Sub
End Sub

[pandey_om]

can u show me what code i have to write if Response=7 it means user do not wana deletion
how i cancele the deletion

[RobDog888]

All this (ListBox1.RemoveItem ListBox1.ListIndex ) does is remove the item that is currently selected and not the one being referred to. You need to use my code in post #4 in order to find the matching text so you can retrieve the index number of it for the .RemoveItem method.

[pandey_om]

hi Robdog888
i am using your code but there is an error "Compile error-->Method or data member not found"

Private Sub cmdDelete_Click()
    Dim ADOConn As New ADODB.Connection
    Dim ADORset As New ADODB.Recordset
    Dim strQuery As String
    Dim id_user As String
    Dim Response As String
    Dim ID As Integer
 
    If Me.txtUserID.Text = "" Then
        MsgBox "Select a user to delete. ", vbInformation + vbOKOnly, "Delete Status"
        Exit Sub
    End If
 
    If Me.txtUserID.Text = "Tutorial" Or Me.txtUserID.Text = "Admin" Then
        MsgBox "The record for UserID " + Chr$(34) + "Tutorial" + Chr$(34) + " and " + Chr$(34) + "Administrator" + Chr$(34) + " cannot be deleted. Select a new record and try again.", vbInformation + vbOKOnly, "Delete Status"
        Exit Sub
    End If
    id_user = Me.txtUserID.Text
    Set ADOConn = New ADODB.Connection
    ADOConn.Open "Provider=sqloledb.1;Initial Catalog=Arvicon;uid=sa;pwd=; Data Source= localhost ;"
    Set ADORset = New ADODB.Recordset
    Response = MsgBox("Are you sure you want to delete this record.", vbInformation + vbYesNo, "Delete Status")
    If Response = 6 Then
        strQuery = "Delete From tblUserData Where UserID='" & id_user & "'"
        Set ADORset = ADOConn.Execute(strQuery)
        intIndex = SendMessage[COLOR=DarkRed]([B]Me.ListBox1.hwnd,[/B]  :eek2:[/COLOR] 'error here  LB_FINDSTRINGEXACT, 0&, ByVal Me.txtUserID.Text)
        If intIndex > -1 Then
            ListBox1.RemoveItem intIndex
        End If
 
        'ListBox1.RemoveItem ListBox1.ListIndex
        MsgBox "Your record has been deleted successfully.", vbInformation + vbOKOnly, "Delete Status"
    ElseIf Response = 7 Then
        cmdCancel_Click 'DoCmd.CancelEvent
    End If
    ADOConn.Close
    Set ADOConn = Nothing
Exit Sub
End Sub

[pandey_om]

error in the Me.listbox1.hwnd
hwnd is reason of error

[RobDog888]

Me.ListBox1.hwnd with the name of YOUR listbox.

[pandey_om]

name of MY list box is listbox1
but error is present

[pandey_om]

i think there is no reference of dll
how i can referenced ,there is no dll reference in Tool-->reference
which dll i frefer on browing, there are various dll file i saw

[pandey_om]

when i reference user32.dll
error msg "u can't refer this file " i m using excel 2003
help plz

[RobDog888]

You are mising the rest of the code from post #4. The API declaration and Constant declaration too.

Is this behind a UserForm then since its Excel VBA?