1

I have a form with a list box in it. The list box displays the results of a SELECT query and I want the user to be able to push a button to delete the item that is selected in the list box. Here is the query I want to run when the user clicks the button.

DELETE *
FROM Client
WHERE
(([Client].[ClientID])=[Forms]![UpdateDeleteClientInformationForm]![ClientFullNameListBox]);

The problem is that I'm unsure how to access the current ClientID from the selection in the list box. How do you access the data in each of the columns in the list box for the selected item in an SQL query?

I'm not particularly comfortable with Access at the moment.

Cromulent
  • 3,788
  • 4
  • 31
  • 41

1 Answers1

1

You can build your query in VBA. The list of items to be deleted can be got from a multi-select listbox like so:

For Each itm In AListBox.ItemsSelected
    srem = srem & "," & itm
Next

srem = Mid(srem, 2)

sSQL ="DELETE FROM ATable WHERE ID IN (" & srem & ")"
CurrentDB.Execute sSQL, dbFailOnError

Note that you would need quoted items for a text data type.

Fionnuala
  • 90,370
  • 7
  • 114
  • 152
  • Thank you for that. I was trying to avoid using VBA but I guess that it is the best option in this case. – Cromulent Feb 14 '13 at 12:09