Using VBA to delete specific records from a table.

[scroce]

Q - I have list box called lstChosenOnes on a form called frmDeleteChosenOnes. Examples of choices in the List Box are like, Sally, Bob, Fred, and Joe. A User can pick one or more items selected in this list box. Upon selection, the user ought to be able to click a command button that deletes records from a table called tblChosen. In other words, if the user selects Bob and Fred, and then clicks the Command button, then the code should open the tblChosen, find Bob and Fred and Delete them. I've been trying something like this, but it's probably wrong - feel free to totally disregard it:

Set frm = Forms!frmDeleteChosenOnes
Set ctl = frm!lstChosenOnes
Set dbCurrent = CurrentDb
strSQL = "SELECT ChosenOnes FROM tblChosenOnes;"
Set rsChosenOnes = dbCurrent.OpenRecordset(strSQL)
strCriteria = "[ChosenOnes] = varItm

MsgBox "are you sure you want to delete these?", vbOKCancel

For Each varItm In ctl.ItemsSelected
'rsChosenOnes.FindFirst "ChosenOnes" = strCriteria
rsChosenOnes.FindFirst strCriteria
Debug.Print ctl.ItemData(varItm)
rsChosenOnes.Delete

It sounds like it should be relatively simple, but I am having a heck of a time with it. Can anyone help, or does anyone have some example code of something similar? I don't know if I have explained this adequately enough.

Thanks in advance:

S. Croce
SCroce13@aol.com

 

[TheInsider]

Hi,
I'll send you a sample database.

Rob Marriott
rob@career-connections.net

 

[scroce]

OK - manipulating some of the code you gave to me, I came up with the following : (the text editor messed it up a little bit - not too bad)

I keep getting a an SQL error - the problem is passing strCriteria into strSQL. The strSQL is generating the error, specifically in this line :

strSQL = "DELETE tblChosenOnes.ChosenOnes* FROM tblChosenOnes WHERE (((tblChosenOnes.ChosenOnes)=(" & strCriteria & &quot"

can this be done? - what is the proper syntax? I copied this pretty much directly from your example - ARRGH!! help!

**** see below***


Private Sub cmdDeleteChosenOnes_Click()

Dim strCriteria As String, strSelection

Dim strSQL As String


strSQL = "DELETE tblChosenOnes.ChosenOnes* FROM tblChosenOnes WHERE (((tblChosenOnes.ChosenOnes)=(" & strCriteria & &quot"

If (lstChosenOnes.ItemsSelected.Count > 0) Then
For Each strSelection In lstChosenOnes.ItemsSelected
strCriteria = strCriteria & IIf(strCriteria <> &quot;&quot;, &quot; OR&quot;, &quot;&quot _
& IIf((lstChosenOnes.ItemData(strSelection) = &quot;<Empty>*&quot, &quot;NULL&quot;, &quot;'&quot; _
& lstChosenOnes.ItemData(strSelection)) _
& IIf((lstChosenOnes.ItemData(strSelection) = &quot;<Empty>*&quot, &quot;&quot;, &quot;'&quot & &quot&quot;


Next
DoCmd.RunSQL (strSQL)

End If
End Sub

 

[scroce]

I guess the essence of the question is, in the following:

strSQL = &quot;DELETE tblChosenOnes.ChosenOnes FROM tblChosenOnes WHERE (((tblChosenOnes.ChosenOnes)='fred'));&quot;

How do you replace 'fred' with a variable - or can you?

 

[TheInsider]

Hi,
I posted the answer of the Var question in the other post but just to give you an idea of what my code did... I decided that instead of looping through each item selected in the list box, then deleting the records one at a time (which does work)...I decided to loop through and obtain a list of the selected item, then build a dynamic criteria string... then do one big delete once the criteria string had been formed. This is not necessary, but more efficient.

Rob Marriott
rob@career-connections.net

 

[bab]

I only noticed that in your sql statement below:

strSQL = &quot;DELETE tblChosenOnes.ChosenOnes* FROM tblChosenOnes WHERE (((tblChosenOnes.ChosenOnes)=(&quot; & strCriteria & &quot&quot;

that you forgot the single quotes that have to go around strCriteria, as in:

=('&quot; & strCriteria & &quot;')&quot;

That would probably prevent your code from working.