Results 1 to 5 of 5
  1. #1
    VanessaLH is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2016
    Posts
    3

    Query not saving only selected items in list box and how to save the selected back into database?

    Hi,
    I have 3 queries in my user form, 1st level, selects and saves (bounded) major category, the 2nd is based on the 1st level and that is also bounded (both are cbo boxes), the 3rd is based on the 2nd selection and is a list because I want multiple values to be selected. Problem 1) I have this and I can save all the data into a table. But I can't seem to get only the data the user selects, I get all the data available.

    SELECT tblItemParts.ItemParts, tblItemParts.MinorCategory INTO tblItemsBroughtIn
    FROM tblItemParts


    WHERE (((tblItemParts.MinorCategory)=[Forms]![frmLimView].[cboUnit]));

    I also tried this - but it doesn't work, it doesn't crash which is great but doesn't work.
    Private Sub lstItemsIn_AfterUpdate()
    Dim frm As Form, ctl As Control
    Dim varItm As Variant, intI As Integer

    Set frm = Forms!frmLimView
    Set ctl = frm!lstItemsIn
    For Each varItm In ctl.ItemsSelected
    For intI = 0 To ctl.ColumnCount - 1
    Debug.Print ctl.Column(intI, varItm)
    Next intI
    Debug.Print
    Next varItm
    End Sub

    2) Problem 2, I want to concatenate the correct (only user) selection from tblItemsBroughtIn into a field in my tblMainData and save it to the correct RecordID.
    Help would be greatly appreciated.
    Thanks,
    Vanessa

  2. #2
    NTC is offline VIP
    Windows 7 64bit Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Your post is presenting a technical issue but it strikes me that the bigger issue is conceptual. You mention selecting and saving data via cascading combo boxes (data sets dependent on prior combo box value). Without knowing your data structure nor your goal it is difficult to be too precise. But in general no vba is needed for this type task.

  3. #3
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    What is the table tblItemsBroughtIn being used for? You are re-creating it each time, and from what I can see, the data in it is not related to the items selected list box.

    You have a multi-column list box; what is the row source for it? Once you have made all the selections, what do you want to do with them?

    Please give us a few more details on what you are trying to do.

  4. #4
    VanessaLH is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2016
    Posts
    3
    Quote Originally Posted by John_G View Post
    What is the table tblItemsBroughtIn being used for? You are re-creating it each time, and from what I can see, the data in it is not related to the items selected list box.

    You have a multi-column list box; what is the row source for it? Once you have made all the selections, what do you want to do with them?

    Please give us a few more details on what you are trying to do.
    Hi John, sorry I didn't give enough info. So we have customers that bring in repairs and they can be (major category) - small appliance, whiteware, audio, camera etc and then I have a list that says (minor category) e.g. washing maching, dryer, fridge (under whiteware) and stereo, DVD, amplifiers etc (in audio) (my bounded cbo boxes). Then under these categories I have a list of possible parts that people bring in. Washing machine (e.g. main unit, washing power dispenser), for stereo (main unit, FM antenna, AM antenna). I have a main table database which the user accesses using a form (types the details of the customer etc) and selects the 3 levels, so customer has RecordID (1) - brings in Audio, then I can select they brought in Stereo, then I can see my list of Main Unit, FM antenna, AM antenna.

    If they bring in only the Main Unit and the FM antenna I want to select these 2 items or if they have all 3 or just 1 (I can have about 15 items that people can bring in for one unit) and save it back to the main database in a field as a list that belongs to that customer (RecordID 1). At the moment I've got everything saving to another new table - so not the selected items and then I can't work out how to get that data (in a concatenated string list) from that table back into the original main table record for the customer and empty it so that when I run that query again in a new record it is ready to go again.

    Many thanks,
    Vanessa

  5. #5
    VanessaLH is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Jul 2016
    Posts
    3
    I forgot to say I'm using access 2007, thanks.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 1
    Last Post: 03-05-2013, 06:53 PM
  2. Replies: 3
    Last Post: 10-23-2012, 03:32 PM
  3. List Box Items Are De-Selected On A Screen Refresh
    By plengeb in forum Programming
    Replies: 2
    Last Post: 10-03-2012, 11:58 AM
  4. Replies: 6
    Last Post: 07-10-2011, 05:33 PM
  5. Replies: 5
    Last Post: 04-04-2011, 09:57 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums