Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2004
    Posts
    64

    Unanswered: Clear records from a listbox

    Hello to all you database wizards

    I have populated a list box with records using 3 columns. now I want to clear the list box and put in another recordset.

    Me!Listproperty.Value = Null or Me!Listproperty.Value = "" does not clear it

    What code should i use ?

    your help will be appreciated

    peter

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    you can either remove each item in a loop
    eg
    with <mylistbox>
    while .count>0
    .removeitem(1)
    wend
    end with

    I think you can also set the rowsource to ""

    or if you are using a SQL statement as the rowsource 'just' assign the new sql statement to the listbox row source

  3. #3
    Join Date
    Nov 2004
    Posts
    64
    Thanks healdem for your reply

    myListbox.rowsourse = "" works, thank you

    Peter
    Last edited by Peter.t; 10-31-07 at 20:04.

  4. #4
    Join Date
    Nov 2004
    Posts
    64
    Healdem Im interest to know how your code below works.

    the list box is populated with a recordset from a SQL Statement

    when it gets to .count I get a error message "method or data not found"


    With myListbox
    While .Count > 0
    .RemoveItem (1)
    Wend
    End With

    And how I assign the new sql statement to the listbox row source,
    because if I re run the current SQL/recordset it just adds onto what is already there

    peter
    Last edited by Peter.t; 10-31-07 at 20:07.

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    it depends on how your list box is populated.
    if its manually populated then you can add or delete items on the fly
    if its populated from a SQL statement you can't.
    that's controlled by the row source type

    in all cases setting the rowsource to "" (ie a null SQL statement or empty list) clears the list box.

    if your list box is populated using a SQL statement then you could 'just' assign a new SQL statement which should clear the current contents and replace with values from the new source.

    However Im not to sure how this works if the list box is using a bound column. it would not be a smart idea unless you programticaly made sure the row value was present list box.

    By using user provided values in a semicolon separated list you can add or remove whatever item(s) you wish. Its that design stylistic that allows a double linked list box to work (for example when you use the report generator wizard it presents various fields and allows you to move them from "available" to "used" listboxes)

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Code:
    With myListbox 
      While .Count > 0  'stay in this loop until the number of items in the listbox is 0
        .RemoveItem (1) 'delete the item at position 1 in the list box's collection / row source
      Wend 'keep in while loop
    End With
    the "with mylistbox" merely saves having to expressly state which object you are iterating through, its not rearly useful here but its force of habit for me. if we dropped the with / end with construct the same effective code becomes...

    Code:
    While myListbox.Count > 0  'stay in this loop until the number of items in the listbox is 0
      myListbox.RemoveItem (1) 'delete the item at position 1 in the collection 
    Wend 'keep in while loop
    if you got an error I'd suspect that either a listbox with a SQL row source doesn't support the "count" method.. which I doubt, or possibly you are not referring to a valid listbox control. I use <mylistbox> to indicate that you should replace <mylistbox> with your listbox name

  7. #7
    Join Date
    Nov 2004
    Posts
    64
    Thanks Healdem
    its taken a while to get my head around all this, I have saved it in my code database for future reference

    appreciated thanks

    Peter

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I'm not quite sure if the .additem and .removeitem work in Access 2000 though. I think this is XP and above.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Quote Originally Posted by pkstormy
    I'm not quite sure if the .additem and .removeitem work in Access 2000 though. I think this is XP and above.

    Paul...
    Im sure you are righht on that, however if I remember correctly there are other methods that do the same thing called somethign a bit different. Its one of the banes of my life having to support VBA, VB, VB.NET apps that there isn't a consistent naming convention across all apps within the Mircrosoft stable.. its a bit better as .NET evolves. Not forgetting of course when I have to jump ship and use PHP and or Javascript..

Posting Permissions

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