Trying to get this to work. DELETE FROM tblItemsInStories WHERE StoryID = [Forms]![FrmTblStoriesInfo]![txtStoryID] AND ItemID = varItem varItem is a long number. That one or more #’s selected in a ListBox by user.

Question

closest I got is this

 

DELETE *

FROM tblItemsInStories

WHERE StoryID = [Forms]![FrmTblStoriesInfo]![txtStoryID] AND ItemID = [item# to delete];

It does work.

---

Mark J

Answer 1

You need VBA for this. For example, in the On Click event procedure of a command button cmdDelete on the FrmTblStoriesInfo form. In the following, lstItems is the multi-select list box.

Private Sub cmdDelete_Click()
    Dim strSQL As String
    Dim strIn As String
    Dim varItm As Variant
    strSQL = "DELETE * FROM tblItemsInStories " & _
        "WHERE StoryID=" & Me.txtStoryID
    For Each varItm In Me.lstItems.ItemsSelected
        strIn = strIn & "," & Me.lstItems.ItemData(varItm)
    Next varItm
    If strIn <> "" Then
        strSQL = strSQL & " AND ItemID In (" & Mid(strIn, 2) & ")"
    End If
    CurrentDb.Execute strSQL, dbFailOnError
End Sub

---

Regards, Hans Vogelaar (http://www.eileenslounge.com)

Answer 2

I would recommend Hans's solution, but if you do want to create a query you can make use of the InParam and GetToken functions published by Microsoft.  You'll find an example of the use of these functions in DatabaseBasics.zip in my public databases folder at:

https://onedrive.live.com/?cid=44CC60D7FEA42912&id=44CC60D7FEA42912!169

Note that if you are using an earlier version of Access you might find that the colour of some form objects such as buttons shows incorrectly and you will need to  amend the form design accordingly.

In this little demo file the section on 'retrieving data from the database. includes a form in which a multi-select list box allows one or more contacts to be selected.  The code behind the button to open a report loops through the control's ItemsSelected collection, as in Hans's solution, and writes a comma separated list of values to a hidden text box control in the form.  This is then referenced by the InParam function in the report's query.  The GetToken function is called by the InParam function.

---

Ken Sheridan, Stafford, England

Answer 3

Hans,

thank you.

I remember something about using 'execute'   but di did not know about using CurrentDb.

And I could use dbFailOnError .

This is the first chance I got to try to see your answer.

thanks,

Mark J

---

Mark J

Answer 4

Ken,

Thanks. thought I was going to have a problem opening the file because it was zipped.

I decided to try with the default, that was Explorer, and I got the file open.

I have never used or heard of the InParam function, and I have had problems with using Parameters in Access SQL and in Queries.  

So I will need to spend time studying and practicing how to get your examples to work.

I have found time to time I have changed settings that prevent some code from working the way I expected-until I fournd my error in setting.

I looks like I will be able to learn much from that sample file.  So thanks again.

Mark J

---

Mark J

Answer 5

Hans,

It works great!

For some reason I haven't figured out yet my PC complained about the comma.

When I removed     "," &      from the code it works great.

Now I will be busy removing all unneeded or wanted code, for a while.  then next bit for this code is to make it a template so it will be very in the several uses for this code.

thanks again,

Mark J

---

Mark J