Delete Record From A List Box

[oneleaf5]

I'm trying to delete a record from a List Box. When I run the code below, I get this error:

"undefined function '[Forms]![frmFiscalYear]![lstMembers].Column' in expression"

tblFYMembers, where the List Box items are stored, get its values from the FY field on the Form, and the ID field in cboMember. So I have two criteria in my SQL statement. Perhaps my syntax is clumsy?

Thanks in advance,

oneleaf

************
Private Sub lblDeleteMember_Click()

DoCmd.RunSQL "DELETE * FROM tblFYMembers WHERE ((tblFYMembers.FY)=[Forms]![frmFiscalYear]![FY] AND (tblFYMembers.Member)=[Forms]![frmFiscalYear]![lstMembers].Column(0));"

lstMembers.Requery

End Sub
*************

 

[Remou]

You cannot use column in a query, but you can use the value:

[tt]DoCmd.RunSQL "DELETE * FROM tblFYMembers WHERE tblFYMembers.FY)=" _
& [Forms]![frmFiscalYear]![FY] _
& " AND tblFYMembers.Member=" _
& [Forms]![frmFiscalYear]![lstMembers].Column(0)[/tt]

If the first column, that is column 0, is the bound column, and this is not a multiselect listbox, there is no need to use 'column'. If this is a multiselect listbox, you will also need to reference the row. The example above assumes that both Member and FY are numeric fields, you will need single quotes if they are not. It can be more convenient to use Me, rather than [Forms]![frmFiscalYear], if the SQL is layed out as shown above and is running on frmFiscalYear.

 

[oneleaf5]

Remou,

Wow, it worked. Thanks so much!

oneleaf