Page 1 of 2 12 LastLast
Results 1 to 15 of 18
  1. #1
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557

    Unanswered: Listbox Selections to another listbox or form

    I have a form as you see it below. I select parts>both>series&class

    That produces a part list. I need my users (salesmen) to be able to multi select the parts they want.

    I need to create a Sales Order Form that takes the products they selected and generates that part.

    I first need the selected items to populate another listbox in case they need to do several searches for products.

    At this point in time I dont need this datastored. (Maybe in the future). Or maybe I should store it now, but I need the data to go from that list box to another and then allow them to press a button that shows the sales order form.

    I need to know how to get the selections from one listbox to the next one, and then from there to the Sales Order Form.

    Thanks for all the help that has gotten me to this point.
    JS
    Attached Thumbnails Attached Thumbnails findformnew.jpg  
    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
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Hi,

    I use list boxes quite extensively for a variety of reasons, some of which Microsoft probably didn't intend!

    There are 3 basic methods of getting a list of data in the listbox through code:

    1) Craft SQL statements in code to apply to the listbox and requery the listbox
    2) Use a temp table
    3) Use a "Value List" string

    #1 is the simplist to use and work with as a developer, but offers the least utility. This is prefered becasue it doesn't do anything fancy in code (making it more reliable).
    #2 is more complicated to setup but easier to use for you, the developer. It allows the most flexibility - you control the temp table, data in the temp table and the properties of the list box through code.
    #3 requires no advance setup, but is harder to use. In a practical sense, this is prefered if you have a small list (partly due to the 255 text string limit in the rowsource parameter, partly due to how hard it is to set it up just right).

    Based on your example, it look like using afterupdates and #3 would work well for the top lists, and #1 (since I assume your parts list in in tables already) for the big one.

    Are you familiar with the listbox's properties and how to navigate the data in a listbox when multiselect is enabled?

  3. #3
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    Yea my first 2 listboxes are driven from Afterupdate()'s The rest is just based on those two.

    The Big one is based on my parts table. Though I will be changing the record source to one of my cost queries so I can obtain costing and dimensions, but that I can do later. I need now to be able to populate another listbox, and a 'Sales Order' form.

    My salesmen are gettting really excited about this, I am too. I cant wait to make this work.

    I have never used temp tables. I know I could manipulate the data as soon as I got it in there but I would need to know how that would work. And also since it is data that may at somepoint need to be saved do you think I should have the 'Sales Order' saved in a table for later look up if needed? I think it would be easier to set that up now and then if I need it later I would have it right?

    I dont know very much about getting the data from a listbox to anything else. I have just been using the afterupdate to set the next listbox's properties such as rowsource, rowsourcetype, etc.

    As much as you can help I would be forever thankful.

    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
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I would like a small example on all three methods you mentioned above. (Just to know)

    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

  5. #5
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by JSThePatriot
    Yea my first 2 listboxes are driven from Afterupdate()'s The rest is just based on those two.

    The Big one is based on my parts table. Though I will be changing the record source to one of my cost queries so I can obtain costing and dimensions, but that I can do later. I need now to be able to populate another listbox, and a 'Sales Order' form.

    My salesmen are gettting really excited about this, I am too. I cant wait to make this work.

    I have never used temp tables. I know I could manipulate the data as soon as I got it in there but I would need to know how that would work. And also since it is data that may at somepoint need to be saved do you think I should have the 'Sales Order' saved in a table for later look up if needed? I think it would be easier to set that up now and then if I need it later I would have it right?

    I dont know very much about getting the data from a listbox to anything else. I have just been using the afterupdate to set the next listbox's properties such as rowsource, rowsourcetype, etc.

    As much as you can help I would be forever thankful.

    JS
    Generally speaking you need to accomodate for the worst case scenario ... Is there the potential for a long parts list? There are a couple of methods for doing this with temp tables:

    1) Load the parts for just the selection criteria -or-
    2) Load all parts and query for only those that match

    I prefer #2 to cut down on the # of db hits ... Basically you have all the necessary filter columns in this table WITH an extra column (or 2) for whether the particular part is to be hidden or not ...

  6. #6
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    I have decided that I want to create a table that tracks all of my sales orders. I want the data in that table generated by the form shown above. I want the user to be able to do several different searches get all the parts they need then click one button to generate the sales order then enter the quanity they want blah blah blah. Print/Save it.


    Hope that narrows down my options
    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
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Ok, there's a lot to cover. I learned all the basics of using list boxes from the help files, and then experimenting.

    The list box is essentially a 2 dimensional array (a table) and using properties like "Index" (which translates to "Row") and "Column" (which translates to "Field") you can get all the information you need from the listbox after the Sales guys pick the items they want.

    Create 2 new tables:
    tblSalesOrder
    tblSalesOrderDetail

    thbSalesOrder stores Transaction ID (can be an autonumber), Salesman ID, and other misc. data like the date.
    tblSalesOrderDetail stores the TransactionID (from above) and Item ID (which is the key from the table holding the products list).

    Have a query that shows ALL the prodcuts in the big list box. Use the afterupdate on the previous listboxes to create an SQL to filter the big list box (product list). Make sure you requery the listbox after changing it's rowsource.

    After the salesguy has selected his item(s), he presses a button. There is an "ItemsSelected" collection that contains each item the user selected in the listbox. Open a recordset to the first table I mentioned above to create a new Sales Order. Add the Salesman ID, the date, etc. and get the transaction ID. Then, open the second table in a recordset and use a Do...Loop to rifle through the listbox's selected items, recording the Transaction ID and the Item ID in each record.

    Now you have a "One to Many" showing each "Sales Order" and the list of items under it.

    The rest is in the bucket! To print, simply attach a report to a query that puts the "One to Many" together open the report with a filter "[Transcation ID] = " & TheID

    To EDIT the sales order once it is created can be accomplished best by using a seperate form, with the first table as the recordsource, and a subform with the second table as the recordsource.

    Good luck - and check out those help files on Listboxes - they're pretty helpful!

    Have fun!

  8. #8
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    That is a good Idea that I use the main box to hold all the parts from the beginning and when they click the other options it just narrows it down. That way they can look at everything if they really want to.

    The part I am confused about is the .ItemsSelected option. I looked it up in help and it said it could only be used in design mode or something weird like that. Could you show me a piece of sample code that has .ItemsSelected in it?

    Thanks for all the help,
    JS

    I think I am going to do this...

    Two tables as you said (I already have many other things that are connected like that such as my caulk inventory tracking.)

    User selects parts clicks button adds to another listbox.
    That listbox then populates an unbound form. The form has a Save/Print button to save and print the Sales Order or a Cancel to keep from having too much data in the database.

    Does all of that sound logical to you?

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

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

  9. #9
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by JSThePatriot
    That is a good Idea that I use the main box to hold all the parts from the beginning and when they click the other options it just narrows it down. That way they can look at everything if they really want to.

    The part I am confused about is the .ItemsSelected option. I looked it up in help and it said it could only be used in design mode or something weird like that. Could you show me a piece of sample code that has .ItemsSelected in it?

    Thanks for all the help,
    JS

    I think I am going to do this...

    Two tables as you said (I already have many other things that are connected like that such as my caulk inventory tracking.)

    User selects parts clicks button adds to another listbox.
    That listbox then populates an unbound form. The form has a Save/Print button to save and print the Sales Order or a Cancel to keep from having too much data in the database.

    Does all of that sound logical to you?

    Thanks again!,
    JS
    Sure ...

    Code:
    Private Sub RemoveWOButton_Click()
        If AllocList.ItemsSelected.Count < 1 Then Exit Sub
        
        Dim i As Long, ItemCount As Integer, BDRow As Variant, SelArray() As Integer
        
        i = 1
        ItemCount = AllocList.ItemsSelected.Count
        ReDim SelArray(AllocList.ItemsSelected.Count)
        For Each BDRow In AllocList.ItemsSelected
            AllocList.Selected(BDRow) = False
            AvailableQty = AvailableQty + CDbl(AllocList.Column(1, BDRow))
            SQLString = "UPDATE " & WO_Tbl & " SET IsVisible=True WHERE (WorkOrderNumber='" & AllocList.Column(0, BDRow) & "');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQLString, False
            SelArray(i) = BDRow
            i = i + 1
        Next BDRow
        AvailQtyLbl.Caption = AvailableQty
        
    '    For i = AllocList.ItemsSelected.Count To 1 Step -1
        For i = ItemCount To 1 Step -1
            SQLString = "DELETE FROM " & AllocListTbl & " WHERE (WorkOrderNumber='" & AllocList.Column(0, SelArray(i)) & "');"
            DoCmd.SetWarnings False
            DoCmd.RunSQL SQLString, False
        Next
        
        AssignToQtyTxt.Value = ""
        WO_ComboBox.Value = ""
        
        WO_ComboBox.Requery
        AllocList.Requery
        AssignToQtyTxt.SetFocus
        EditPerformed = True
    End Sub

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

    Okay

    So if I were to use the .ItemsSelected I have to use another .somthing after it?

    I basically need to take items from one listbox to the other.

    Code:
    Private Sub cmdAddtoList()
        lstSelectedParts.RowSource = lstParts.ItemsSelected
    End Sub
    That didnt work for me. Why?

    JS
    Have you ever thought about thinking on purpose?

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

  11. #11
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    hmmmm, to quote you: "keep from having too much data in the database"

    don't worry too much about that - Access and Jet can handle slim tables with many, MANY records - especially if you use indexes properly.

    In simplist form, have the "Here's your options" list box (the big one in your original post at the bottom of the screen shot).

    Then, have a "Here's what you selected" list box on the right. The new list box should be 2 columns wide, the first column to hold the item key and the second to hold the item description. Make the column width 0 for the first column and set it's rowsource to Value List

    Change the big listbox to No Multiselect and use the afterupdate to:

    Dim strSQL as string
    Dim strNew as string

    If Len(Me.lstProducts & "") = 0 then Exit Sub
    strSQL = Me.lstSelected.RowSource
    if Len(strSQL) > 0 then strSQL = strSQL & ";"
    strNew = Me.lstProducts & ";" 'this gets the index (key)
    strNew = strNew & Me.lstProducts.Column(Me.lstProducts.ListIndex,1)
    strSQL = strSQL & strNew
    Me.lstSelected.RowSource = strSQL
    Me.lstSelected.Requery

    Note: the 1 in the column statement will give you the value in the second column - the index for this property starts at 0

    This will add every item selected to the new list box. You can use command buttons to check remove items (using a loop to rebuild the RowSource while omitting the selected item) and add a procedure to sort and check for duplicates.

    Personally, though, using a temp table in the second list box makes it really easy to 1) Add new items, 2) Check for duplicates, 3) Sort, 4) Remove items and 5) The data is already in table format when you're ready to move on

    Have fun!

  12. #12
    Join Date
    Mar 2004
    Location
    Nashville, TN
    Posts
    557
    So you would use a temp table instead of another listbox?

    I have never created a table using VBA so I would need help or some sample code on that.

    Thanks for your patience,
    JS

    P.S. Also if it is in a temp table it will keep me from having an unbound form right? Because I could base my form off of the temp table and when I click save it just saves to the real table?
    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
    I am going to keep giving it a shot until I can post some more exact question up here. I still would need to know how to get the items from the lstbox to a temp table until I save or close the sales order.

    Thanks for everyones 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

  14. #14
    Join Date
    Sep 2003
    Location
    MI
    Posts
    3,713
    Originally posted by JSThePatriot
    I am going to keep giving it a shot until I can post some more exact question up here. I still would need to know how to get the items from the lstbox to a temp table until I save or close the sales order.

    Thanks for everyones help,
    JS
    My code example uses a temp table ... Moving from 1 listbox to another is as simple as:

    1) Set hidden flag for item in list1
    2) requery list1
    3) Add item to list2
    4) requery list2

  15. #15
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    ummm, you have several important thoughts blended together.

    "So you would use a temp table instead of another listbox?"
    The temp table will be the recordsouce for the list box - you'll want the listbox becasue it's a familiar on screen representation of a collection of data.

    "I have never created a table using VBA"
    Creating and then deleting tables creates a phenomenon many people call "ballooning" - in which the space alloted to the new table is not recovered until the next time the file is compacted. A general rule of software development is to "develop yourself out of the program" so it's good to be able to find a way to do it that does not require compacitng.

    Search this forum for temp tables and temp databases. There are a number of good ideas, including one thread with a link to the person's website with an actual "temp database manager" available for download. I'm sorry I don't have the specific thread - it was 1:00 in the morning last week when I came across it.

    I still don't see why you don't want to store the data as the user creates it. Worst case scenerio, after you have accululated hundreds of thousands of records, you'll have to archive the old data and remove it from this file.

    "if it is in a temp table it will keep me from having an unbound form"
    I'm not clear on this one - the listbox will be unbound, with your temp table as the recordsource. this is independant of the form's recordsource (bound or not). It seems, based on the screen shot you supplied, that your form is probably unbound.

    "I could base my form off of the temp table and when I click save it just saves to the real table"
    Not exactly. Either your temp table is the actual table, in which case the data is already there, or it really is a temp table, in which case you'll have to copy the data from the temp table to the real table (there are a couple of methods - I like to use Recordsets becasue I am a control freak - I don't trust Access to do it right).

    It's too bad Access doesn't let you use Unbound Recordsets - that's the perfect solution to using temp tables!

    Good luck!

Posting Permissions

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