Page 1 of 3 123 LastLast
Results 1 to 15 of 36
  1. #1
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Unanswered: Listbox Help! No Duplicates.

    Okay I have a listbox I populate by a user clicking a part they want then the add to list button. I need to keep them from creating duplicates.

    How would that be done.

    Here is the code that puts the items in the last listbox.

    Code:
    Private Sub cmdAddtolst_Click()
        lstSelectedParts.RowSourceType = "Value List"
        lstSelectedParts.AddItem (lstParts)
    End Sub
    Please let me know how to keep duplicates from showing up.

    Thanks,
    JS
    Attached Thumbnails Attached Thumbnails findform.gif  
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  2. #2
    Join Date
    Nov 2003
    Posts
    23
    Look like another case for Dcount.

    Use dcount to count the number of occurance of item in the list.

    If there are 0 occurances then
    add item to list
    else
    exit sub

  3. #3
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Could you give me an example? of Dcount().

    Thanks
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  4. #4
    Join Date
    Nov 2003
    Posts
    23
    Sure have teddy's example

    If DCount("yourField", "yourTable", "yourField = " & yourKeyControl) Then
    sql = "UPDATE yourTable SET ..." etc.
    Else
    sql = "INSERT INTO yourTable (fields) ..." etc
    End If


    also check help files from code editor

    sorry teddy

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713

    Re: Listbox Help! No Duplicates.

    Originally posted by JSThePatriot
    Okay I have a listbox I populate by a user clicking a part they want then the add to list button. I need to keep them from creating duplicates.

    How would that be done.

    Here is the code that puts the items in the last listbox.

    Code:
    Private Sub cmdAddtolst_Click()
        lstSelectedParts.RowSourceType = "Value List"
        lstSelectedParts.AddItem (lstParts)
    End Sub
    Please let me know how to keep duplicates from showing up.

    Thanks,
    JS
    JS,

    That goes back to a previous comment of mine where you have "hidden" value column where you set your query to select ONLY items that are not hidden ... Once selected you set the item to hidden. If it's unselected you set the item to visible ...

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Okay that doesnt quite help me on the listbox senario. That would help if I was using a table and a field from it, but I am not.

    Please if you could give me another example or if someone else has an answer. (sorry to be abrupt not trying to be, I just have to get this done today or I will have to work this weekend).

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  7. #7
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by JSThePatriot
    Okay that doesnt quite help me on the listbox senario. That would help if I was using a table and a field from it, but I am not.

    Please if you could give me another example or if someone else has an answer. (sorry to be abrupt not trying to be, I just have to get this done today or I will have to work this weekend).

    JS
    Pardon my french, but why the h**l not? When the listbox is based on a table and the rowsource is "SELECT * FROM MyTable WHERE (IsVisible=True);" THAT won't work? I do that exact thing myself ... I use a table because I can't rely upon the RowSource NOT exceeding it's 2K limit ... You want to see my code again?????

  8. #8
    Join Date
    Nov 2003
    Posts
    23
    Isn't the contents of the parts list box derived from a table?

  9. #9
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    No sorry. That bottom list is just a value list. It comes from the user clicking the Part in the one second from the bottom. and then clicking the button.

    Thanks,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  10. #10
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Well if you don't want to go the DCount route, you'll have to iterate through the selected items and do a string comparison:

    Code:
    Dim existing As boolean
    For i = 0 to (listbox.RecordCount - 1)
       If listbox.Column(textcolumnhere, i) = otherList.column(text column, otherList.ListIndex - 1) Then
          existing = True
       End If
    Next i
    
    If existing Then
       MsgBox "This item already selected."
    Else
       append stuff here
    End if
    Of course you should tweek this to meet your individual needs/style.
    Last edited by Teddy; 04-16-04 at 11:55.

  11. #11
    Join Date
    Nov 2003
    Posts
    23
    By parts list I am refering to the second from bottom one.

  12. #12
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Thanks Teddy I will see what I can do with that.

    Not sure I quite understand all of it, but I will give it a shot and let you know the outcome.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  13. #13
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Originally posted by trager
    By parts list I am refering to the second from bottom one.
    Yes that is from a table.

    Not sure how that would help though because the last one is the one I dont want to have duplicates.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  14. #14
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Hey Teddy

    Here is the code I am using.

    Code:
    Private Sub cmdAddtolst_Click()
    Dim existing As Boolean
    
    For i = 0 To (lstSelectedParts.ListCount - 1)
        If lstParts.Column(PartID, i) = lstSelectedParts.Column(PartID, lstSelectedParts.ListCount - 1) Then
            existing = True
        End If
    Next i
    
    If existing Then
        MsgBox "This part is already in the list."
    Else
        lstSelectedParts.RowSourceType = "Value List"
        lstSelectedParts.AddItem (lstParts)
    End If
    
    End Sub
    After the first time I selected a duplicate, it tells me from then on that everything is a duplicate.

    I am guessing I need to set existing default to false or something like that.

    Please help,
    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

  15. #15
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Also it allows for one duplicate and then, it tells me I cant add because of duplicate. And also wont let me add anything else.

    JS
    Have you ever thought about thinking on purpose?

    Jarvis Stubblefield
    Patriot Designs
    Web/Database Development and Consulting
    The-Patriot.net by Patriot Designs

Posting Permissions

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