Results 1 to 3 of 3
  1. #1
    Join Date
    Apr 2002
    Posts
    4

    Post Unanswered: Permanently deleting records from a listbox

    Is there a way that data that is output to a list box in access 97 can be permanently deleted. I have used the following code, but it just clears the data out of the list box and not the tabe


    Private Sub cmdDelete_Click()

    Dim stDelStartSQL As String
    Dim stDelSQL As String
    Dim blnAND As Boolean

    stDelStartSQL = " DELETE DISTINCT * FROM Report WHERE"

    blnAND = False

    '------ Building the executed SQL statement ---------'

    If Not IsNull(Forms![frmSearch]![cboRuleBase]) Then
    stDelSQL = stDelStartSQL & " base = '" & Me!cboBase & "'"
    blnAND = True
    End If

    If Not IsNull(Forms![frmSearch]![txtNum]) Then
    If blnAND Then
    stDelSQL = stDelSQL & " AND Number = '" & Me!txtNum & "'"
    Else
    blnAND = True
    stDelSQL = stDelStartSQL & " Number = '" & Me!txtNum & "'"
    End If

    End If

    If Not IsNull(Forms![frmSearch]![txtComment]) Then
    If blnAND Then
    stDelSQL = stDelSQL & " AND Comment = '" & Me!txtComment & "'"
    Else
    blnAND = True
    stDelSQL = stDelStartSQL & " Comment = '" & Me!txtComment & "'"
    End If

    End If

    If Not IsNull(Forms![frmSearch]![txtSource]) Then
    If blnAND Then
    stDelSQL = stDelSQL & " AND Source = '" & Me!txtSource & "'"
    Else
    blnAND = True
    stDelSQL = stDelStartSQL & " Source = '" & Me!txtSource & "'"
    End If

    End If

    If Not IsNull(Forms![frmSearch]![txtDest]) Then
    If blnAND Then
    stDelSQL = stDelSQL & " AND Dest = '" & Me!txtDest & "'"
    Else
    blnAND = True
    stDelSQL = stDelStartSQL & " Dest = '" & Me!txtDest & "'"
    End If

    End If

    '------ End of SQL statement build ---------'


    Me.lstData.RowSource = stDelSQL


    End Sub

  2. #2
    Join Date
    Nov 2001
    Posts
    336
    Try "DELETE * FROM Report WHERE ... ;" without Distinct.

    In order to actually delete records you may issue:
    CurrentDB.Execute stDelSQL

    Igor

  3. #3
    Join Date
    Oct 2001
    Location
    Bay Area, CA
    Posts
    117
    It looks like you are trying to build a custom delete query and then you try to apply it to a listbox with Me.lstData.RowSource = stDelSQL

    Without understanding what is in your listbox, or what is supposed to be in your list box, If you indeed want to delete records from a table (called Report???) then do the following

    Dim db As Database
    Set db = CurrentDb
    db.Execute(stDelSQL)

    If you want the listbox to reflect the changes and the rowsource is based on this table, then refresh the listbox.

    Hope this helps....

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •