Results 1 to 6 of 6
  1. #1
    Join Date
    May 2008
    Posts
    3

    Unanswered: Getting data from a list box to a table

    I'm in the process of building a database and have created a form with 2 multiselect (simple) list boxes but I can't get the selected data to save from my list boxes to the table, even if only 1 item in the list box is selected. A simple solution to this would be appreciated as I have limited VB and SQL knowledge. I've included a copy of the database (it is password protected so use the password add to access the form).
    Attached Files Attached Files

  2. #2
    Join Date
    Sep 2007
    Location
    Global Village
    Posts
    185
    I couldn't find in which form you put list box, anyway get the number of items in your list box and make a loop from 0 to your list count-1 to put each item in the related table.
    For example if your list box name is List1 then
    counter=me.List1.ListCount
    for i=0 to counter-1
    data of each item can be verified as: me.List1.ItemData(i)
    'Code to put data in the table'
    next i
    In addition your password security is better to be dynamic and let user to change the password from time to time instead of put password in code; you can find good examples in Code Bank for security
    Cheers.

  3. #3
    Join Date
    May 2008
    Posts
    3
    Thanks Aran1. I'm not sure where that loop code is supposed to go.

    The form which contains the 2 list boxes is called frm_RM_Main and the related table (the one that stores the info from the input form) is called tbl_RM_Main. The tables where the data which populates the 2 fields on the form which users can then select from are called tbl_function and tbl_controlled_copy_distribution.

    The database is only a prototype (for a pilot) so the level of security you have recommended is not required for the purpose of this build.

    Forgot to say that the password I provided to access the input form needs to be used in conjunction with the middle selection on the login form (Add / Modify Records).

    Very much appreciate your assistance.

  4. #4
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    You'd put the code in your "Save" button.
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

  5. #5
    Join Date
    May 2008
    Posts
    3
    Thanks Startrekker. Am not a programmer so am still not sure how the code should be phrased for the two fields in question (RM_Function and RM_Controlled_Copy_Distribution). Can you assist?

  6. #6
    Join Date
    Nov 2007
    Location
    Adelaide, South Australia
    Posts
    4,049
    I would, but your design is wrong so I can't.

    The advice Aran1 gave you will only work once your design supports the use of list boxes.

    You have a table tbl_RM_Main (whatever that means), but to store items from a listbox the way you want to do, you would need to have a related table for each listbox. So for example, you have a listbox for RM_Function, but you have no table to store data relating to both tbl_Function and tbl_RM_Main (a junction table). Therefore you have nowhere to store the data.

    Not to mention the fact that there are no relationships defined at all, which is just setting a time-bomb for disaster...

    IMO, you need to get your table design under control before doing anything else.

    And tbh, designing a database such as you are, you should learn more about VBA and SQL while you are at it! It will save you a lot of stress if you understand them well
    Owner and Manager of
    CypherBYTE, Microsoft Access Development Specialists.
    Microsoft Access MCP.
    And all around nice guy!


    "Heck it's something understood by accountants ... so it can't be 'that' difficult..." -- Healdem
    "...teach a man to code and he'll be frustrated for life! " -- georgev

Posting Permissions

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