Results 1 to 4 of 4
  1. #1
    Join Date
    Mar 2004
    Posts
    11

    Question Unanswered: Add selections from multi-select list box to a table

    Hi guys, what I want to do is provide the user with a list of work order numbers, and the person selects whichever ones he/she wants and pushes a button to mark them as done. The act of marking them done actually writes the number to a table with a datestamp (date completed essentially). Here's my code so far



    Dim db As Database
    Dim rstTemp As Recordset
    Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Dim WOselected As String
    Set ctl = Me("rinaxlist")
    For Each varItm In ctl.ItemsSelected
    WOselected = ctl.ItemData(varItem)
    Set db = CurrentDb
    Set rstTemp = db.OpenRecordset("tblRinaxRecords")
    rstTemp.AddNew
    rstTemp!workOrder = WOselected
    rstTemp!Date = Date
    rstTemp.Update
    rstTemp.Close
    Set rstTemp = Nothing
    Next varItm



    I was expecting that for each selection it would change the value of varItem, but it stays as the first selected item on the list. Can anyone tell me how to tweak this to work properly?

    Thanks in advance!

    Best Regards

    Jarrett

  2. #2
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445

    Re: Add selections from multi-select list box to a table

    Originally posted by OEMReman
    Hi guys, what I want to do is provide the user with a list of work order numbers, and the person selects whichever ones he/she wants and pushes a button to mark them as done. The act of marking them done actually writes the number to a table with a datestamp (date completed essentially). Here's my code so far



    Dim db As Database
    Dim rstTemp As Recordset
    Dim frm As Form, ctl As Control
    Dim varItm As Variant
    Dim WOselected As String
    Set ctl = Me("rinaxlist")
    For Each varItm In ctl.ItemsSelected
    WOselected = ctl.ItemData(varItem)
    Set db = CurrentDb
    Set rstTemp = db.OpenRecordset("tblRinaxRecords")
    rstTemp.AddNew
    rstTemp!workOrder = WOselected
    rstTemp!Date = Date
    rstTemp.Update
    rstTemp.Close
    Set rstTemp = Nothing
    Next varItm



    I was expecting that for each selection it would change the value of varItem, but it stays as the first selected item on the list. Can anyone tell me how to tweak this to work properly?

    Thanks in advance!

    Best Regards

    Jarrett
    This is probably nothing but you are mixing varItem and varItm in the code. I assume that you simply typed this incorrectly here since you would have gotten a variable undefined error if you tried to execute it.

    You might try to see how many are selected by checking ItemsSelected.Count. If you are getting the proper count, then it seems to me that you are looping properly although, I think that I would open the recordset just once instead of each time you loop through the listbox.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

  3. #3
    Join Date
    Mar 2004
    Posts
    11
    BINGO


    The mixed up varItm and varItem was the problem (although it was not giving me a variable not defined error - weird).

    Works now, thanks!


    (and I changed the recordset thing, you were right about that).


    Jarrett

  4. #4
    Join Date
    Oct 2003
    Location
    Roanoke, Va
    Posts
    445
    Originally posted by OEMReman
    BINGO


    The mixed up varItm and varItem was the problem (although it was not giving me a variable not defined error - weird).

    Works now, thanks!


    (and I changed the recordset thing, you were right about that).


    Jarrett
    Great. Glad it was simple.

    I thought about the variable thing. I always use Option Explicit in all my code modules to require variable declaration. I'm sure this is why I got the message and you didn't.
    Gregg


    DAO, ADO, SQL, Automation and anything else I can pick up.

Posting Permissions

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