none
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. RRS feed

  • Question

  • closest I got is this

     

    DELETE *

    FROM tblItemsInStories

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

    It does work.



    Mark J

    Monday, April 8, 2019 9:38 AM

Answers

  • 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)

    • Marked as answer by PuzzledByWord Wednesday, April 10, 2019 2:48 AM
    Monday, April 8, 2019 12:22 PM

All replies

  • 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)

    • Marked as answer by PuzzledByWord Wednesday, April 10, 2019 2:48 AM
    Monday, April 8, 2019 12:22 PM
  • 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

    Monday, April 8, 2019 12:43 PM
  • 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

    Wednesday, April 10, 2019 2:38 AM
  • 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

    Wednesday, April 10, 2019 2:47 AM
  • 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

    Wednesday, April 10, 2019 4:41 AM