Delete from List Box

[irunergoiam]

I have a list box on a form that displays two columns (Path and Mapping) for Shared Folders assigned to a person. These columns come from a query based on three criteria of the form (1. Location; 2. Department; 3. Position) that are not displayed in the query (or for that matter, the list box).

I would like to have the user select a row from the list box and click a button to delete that row from the original table on which the query and list box are based.

I found a pretty good starting point at http://www.databasedev.co.uk/query_using_listbox.html, but am not quite sure how to go about this as I do not wish to use a union query and have an "All" selection option available.

I've made modifications to that code and used tick marks to skip the following:
'Dim flgSelectAll As Boolean
'If "All" was selected in the listbox, don't add the WHERE condition
'If Not flgSelectAll Then
'strSQL = strSQL & strWhere
'End If

I get a compile error "Sub or Function Not Defined" at
Exit_cmdOpenQuery_Click: (just below "Next VarItem")

So, two questions: 1) Is this even the right approach or 2) Is there a better way to select a row from a list box and delete it?

Much thanks to the AWF masters who mentor the barely-initiated like me!

 

[vbaInet]

I have a list box on a form that displays two columns (Path and Mapping) for Shared Folders assigned to a person. These columns come from a query based on three criteria of the form (1. Location; 2. Department; 3. Position) that are not displayed in the query (or for that matter, the list box).

I would like to have the user select a row from the list box and click a button to delete that row from the original table on which the query and list box are based.

I found a pretty good starting point at http://www.databasedev.co.uk/query_using_listbox.html, but am not quite sure how to go about this as I do not wish to use a union query and have an "All" selection option available.

I've made modifications to that code and used tick marks to skip the following:
'Dim flgSelectAll As Boolean
'If "All" was selected in the listbox, don't add the WHERE condition
'If Not flgSelectAll Then
'strSQL = strSQL & strWhere
'End If

I get a compile error "Sub or Function Not Defined" at
Exit_cmdOpenQuery_Click: (just below "Next VarItem")

So, two questions: 1) Is this even the right approach or 2) Is there a better way to select a row from a list box and delete it?

Much thanks to the AWF masters who mentor the barely-initiated like me!

You could do something like this on the On Click event:

If lisBox1.ListIndex > -1 And ListBox.value <> "" Then
    Dim response As Integer
    response = MsgBox("Do you want to delete this record?", vbYesNo, "Delete confirmation")
    If response = vbYes Then
        DoCmd.SetWarnings False
        DoCmd.RunSQL "DELETE FROM [TableName] WHERE FieldNameToDelete = " & listBox1.Column(X) & ";"
        DoCmd.SetWarnings True
        listBox1.Requery
    Else
        MsgBox "Delete cancelled"
    End If
Else
    MsgBox "You didn't make a selection"
End If

replace the TableName, FieldNameToDelete and "x" (in listBox1.Column) with appropriate values. I would imagine that "x" is 0 as column 0 is usually the bound column. i added a few cosmetics (such as the prompt to delete) which you may not need