Delete Selected List Box item from Table?

[Jason1971]

(Newbie) Hi,

I'm trying to delete a record from a table based on the item selected in its List Box. The error I'm getting is "Argument Not Optional" (see bold below)

Private Sub btnDelete_Project_Click()

Dim varSelectedItem As AcRecord
Dim strSQL As String

varSelectedItem = Me.List1.ItemsSelected.Item
strSQL = "DELETE varSelectedItem FROM Project_List_Table;"
DoCmd.RunSQL strSQL

End Sub

Any help would be greatly appreciated, thanks,
Jason.

 

[MarkK]

Common practice in this situation is that the list has multiple columns, one of which is the ID or Key field value of the record.
Here are a few properties and sample settings you might use in the list's property sheet while the item is open in design view.

Data Tab->RowSource: SELECT ItemID, Item FROM tItem;
Data Tab->Bound Column: 1
Format Tab->ColumnCount: 2
Format Tab->ColumnWidths: 0";2"
Other Tab->MultiSelect = none

Now when a row is selected the Value property of the list is equal to the value in the bound column of the selected row. Then you can run code like this...

CurrentDb.Execute "DELETE FROM tProjectList WHERE ItemID = " & Me.lstYourList

Also note the difference in the SQL syntax for the deletion. SQL DELETE only deletes rows, so you don't need any text between the DELETE and the FROM, and unless you want to delete all rows, you need to specify a WHERE clause.
Best o' luck

 

[DCrake]

To add further thought to the thread I would strongly recommend you wrap a YesNo MsgBox prompt around the delete. You don't want anyone deleting records by mistake. Such as:

Are you sure you want to delete this item from the table?

David