Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    Join Date
    Sep 2005
    Posts
    240

    Unanswered: Detect whether user has selected item in list box

    Hello yet again

    OK, I have a form in my database that contains a list box to display a list of events and dates.
    What I would like to do is allow the user to delete an event from the table, using the list box to select which event to delete.
    I looked in the help and used the example code for the Selected property of the list box, but I can't seem to get this to work correctly.

    Code:
    Public Function DeleteEvent()
        Dim db As Database
        Dim delSQL As String
        Dim intCurrentRow As Integer
     
        Me.ListEvents.RowSource = ""
     
        Set db = CurrentDb()
     
        delSQL = " DELETE FROM dbo_tblSchoolEventDetails" & _
               " WHERE SchoolID = " & Me!ListEvents & ""
     
        For intCurrentRow = 0 To ListEvents.ListCount - 1
            If Me.ListEvents.Selected(intCurrentRow) = True Then
                db.Execute delSQL
            Else
                MsgBox "Please select an event to delete!", vbOKOnly, "Schools Liaison Database"
            End If
        Next intCurrentRow
     
    End Function
    The list box is bound to the first column that contains the ID number, which is specified in the delete query. But when I run the form and select something from the list and click the command button it keeps displaying the message box and clears the data in the list box but doesn't delete it from the table.

    Can anyone help?

    Thanks.

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    How I get the selected value (where column 0 is the bound column):
    Code:
    Me.lstAvailableCols.Column(0, Me.lstAvailableCols.ItemsSelected(0))
    You can see it in action here:
    http://www.dbforums.com/showpost.php...80&postcount=3

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    BTW - would you mind if I asked if you worked in the North West of England?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  4. #4
    Join Date
    Sep 2005
    Posts
    240
    Thanks pootle.

    Yes I do work in the North West, why'd you ask?

    Your not going to start stalking me now are you?

  5. #5
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by KevCB226
    Yes I do work in the North West, why'd you ask?
    Just curious. Is your name Kev?

    Quote Originally Posted by KevCB226
    Your not going to start stalking me now are you?
    You'll just have to wait and see.....
    Testimonial:
    pootle flump
    ur codings are working excelent.

  6. #6
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by pootle flump
    Just curious. Is your name Kev?
    Yes, Yes it is.

    You'll just have to wait and see.....
    I thought I was being followed the other night, your not a spook are you, what have I done now, I'm innocent I tell you

    Anyway, I can't seem to get your suggestion working, I get the following error when hitting my command button:

    You referred to a property by a numeric argument that isn't one of the property numbers in the collection
    Code:
        Dim db As Database
        Dim delSQL As String
     
        Me.ListEvents.RowSource = ""
     
        Set db = CurrentDb()
     
        delSQL = " DELETE FROM dbo_tblSchoolEventDetails" & _
               " WHERE SchoolID = " & Me!ListEvents & ""
     
        If Me.ListEvents.Column(0, Me.ListEvents.ItemsSelected(0)) Then
            db.Execute delSQL
        Else
            MsgBox "Please select an event to delete!", vbOKOnly, "Schools Liaison Database"
        End If
    I tried changing the column from 0 to 1 since the ID is hidden, but that didn't work either

  7. #7
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    I work for a college in the NW. One of my developers is working on a schools liaison database. For a surreal moment I thought I was guiding someone who works down the corridor.

    Anyhoo - I only get that message if nothing is selected when the button is pressed.
    Code:
    Dim db As Database
    Dim delSQL As String If Me.ListEvents.ItemsSelected.Count = 1 Then Me.ListEvents.RowSource = "" Set db = CurrentDb() delSQL = " DELETE FROM dbo_tblSchoolEventDetails" & _ " WHERE SchoolID = " & Me.ListEvents.Column(0, Me.ListEvents.ItemsSelected(0)) If Me.ListEvents.Column(0, Me.ListEvents.ItemsSelected(0)) Then db.Execute delSQL Else MsgBox "Please select an event to delete!", vbOKOnly, "Schools Liaison Database" End If End If
    Testimonial:
    pootle flump
    ur codings are working excelent.

  8. #8
    Join Date
    Sep 2005
    Posts
    240
    No I wasn't in work till all hours last night, just so happens I was logged into dbforums.

    Anyway, I just tried your suggestions, and the same message is displayed.
    When debugging it highlights the sql statement.
    But I also noticed that it was picking up 0 as a value for the ItemsSelected.Count

  9. #9
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Are you able to upload as an attachment (just the relevent parts only)?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  10. #10
    Join Date
    Sep 2005
    Posts
    240
    OK, here you go.

    When you hit the delete button to delete an event from the table it clears the listbox but when you move to another record and back again the event is still listed.
    Attached Files Attached Files

  11. #11
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    One quick observation - you might want to increase the timer interval on your main menu - it is running every milli-second (hence the flicker)
    Testimonial:
    pootle flump
    ur codings are working excelent.

  12. #12
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Problem is the clearing the row source before you;ve performed the actions.
    Code:
    If Me.ListEvents.ItemsSelected.Count = 1 Then
     
     
        Set db = CurrentDb()
     
        delSQL = " DELETE FROM dbo_tblSchoolEventDetails" & _
               " WHERE SchoolID = " & Me.ListEvents.Column(0, Me.ListEvents.ItemsSelected(0))
     
        If Me.ListEvents.Column(0, Me.ListEvents.ItemsSelected(0)) Then
            db.Execute delSQL
            Me.ListEvents.RowSource = ""
        Else
            MsgBox "Please select an event to delete!", vbOKOnly, "Schools Liaison Database"
        End If
    End If
    Also - it seems to me you are deleting all events for that school - not just the selected event - which kind of makes the whole process of reading the listbox a bit pointless - do you agree?
    Testimonial:
    pootle flump
    ur codings are working excelent.

  13. #13
    Join Date
    Sep 2005
    Posts
    240
    Quote Originally Posted by pootle flump
    One quick observation - you might want to increase the timer interval on your main menu - it is running every milli-second (hence the flicker)
    Good point, that has been changed.

    Quote Originally Posted by pootle flump
    it seems to me you are deleting all events for that school - not just the selected event
    Crikey, your right.
    Glad you pointed that out I would have totally overlooked it and then deleted all the records they had already put in. Sort of thing I'd do anyway

    Thanks pootle, I have sorted it now.

  14. #14
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Quote Originally Posted by KevCB226
    Crikey, your right.
    Glad you pointed that out I would have totally overlooked it and then deleted all the records they had already put in. Sort of thing I'd do anyway
    Eeek!

    Always a good idea to:
    a) Have lots of backups
    b) Develop (especially when developing a transactional system) on test data
    c) Have lots of backups
    Testimonial:
    pootle flump
    ur codings are working excelent.

  15. #15
    Join Date
    Sep 2005
    Posts
    240
    Woops, I made that sound as if I do that sort of thing all the time, which would be bad.

    But we have inspection all this week and since there is a lot going on producing reports and what not, it's something that slipped my mind.

    Since the data is stored on SQL Server, I usually create another database but import the data, and use it as a local testing db.

    But on this occasion I wanted to get it done quickly so just used the mdb with the linked tables, and if you hadn't pointed it out it would have deleted everything.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •