Results 1 to 7 of 7
  1. #1
    Join Date
    Sep 2009
    Posts
    6

    Unanswered: Help Getting Data from Multi-Select Listbox to Table in Access 2007

    Hi,

    I am VERY new to access and additionally have limited experience with any kind of programming which may make this rather difficult. I have searched extensively on the internet for my issue but haven't been able to figure out what I am doing wrong. Basically I have created a database that is used to house economic data. I have a form with a listbox which contains years (1980-2008) that is set to extended multiselect and allows the user to make multiple selections. I need the user to select what years they would like to see the data for and then save those selected years in a separate table (to be used later to filter the database). Being not proficient in programming, I have tried to adjust various codes I have seen on this forum, microsoft support pages, and other support forums but have been unable to make my codes work. Please help!

    Not sure if this is helpful, but here is some information:
    form name: Select Time Period
    listbox name: yearlist (pulling information from another table)
    command button1 : clrlist (this clears the selections in the listbox)
    command button2 : yearsselected

    If it is not too complicated, I would like to have the users selections displayed in a textbox on the form. I have tried doing this using the microsoft support page code from How to retrieve the selected items in a multiple selection List Box as a comma-delimited string in Microsoft Access, but was unable to get it to work after I followed step by step their code. I am not sure even if I am putting the code in the right section. I even renamed all my objects to match those used in the example and it still wasn't working so I am not sure what I am doing wrong.

    Once I get this to work, I hope to repeat the above code for the user to select what countries they would like data for as well as which indicators and filter the data according before downloading to excel. I am not sure if this makes any difference at all, but the database is going to be split and the back end will be housed on the company server and will need to be accessed by multiple users at one time.

    Thanks in advance for your help!!

  2. #2
    Join Date
    May 2009
    Posts
    258
    The link that you posted is actually the solution to the problem that you have. Read through the instructions completely and follow them. The only difference is that you have the form already set up, so you'll just want to go into the design view for the form, instead of doing step 4. You'll also want to compare the field names you have set up to those in the example and make any changes in the logic as necessary.

    Regards,

    Ax

  3. #3
    Join Date
    Sep 2009
    Posts
    6
    Thanks for the reply. I actually just got it to work. The issue apparently was some default setting in my access that did not enable all content or something which wasn't able to run all the code. So, after enabling it, it is now able to recognize the users selections and put them into a text box with commas in between (i.e. "2008, 2007, 2004, 1999). Hooray! But, now I am stuck on how to transfer the data from the text box to a table. Any tips or ideas?

  4. #4
    Join Date
    May 2009
    Posts
    258
    Do you already have the table set up? If so, you'll have to make sure you know which user is on the form and, since you'll be using filters for more than one field, will need to have a table setup similar to the following:
    Code:
    User	Field	Criteria
    It seems clumsy to store these values in a table, why not just use form variables? Will the filter be done from the form itself?

    Ax

  5. #5
    Join Date
    Sep 2009
    Posts
    6
    Thanks Ax. I don't have a table already set up to input the users selected values. What I do have is have a large database table with the fields: country, year, and then a column for each indicator for example (gdp, debt, literacy rate, etc.). I would like to use the users selections to filter this database table for the years selected. I will then filter the table two more times when the user selects country and indicator they would like on two separate forms.

    What do you suggest is the best way to do this. Use a form variable? I am actually not sure what that is but will look more into that.

    Sorry, I may not be explaining as clear as possible, but please let me know if you need additional info. Thanks for your help!

  6. #6
    Join Date
    May 2009
    Posts
    258
    If you will be using the value across multiple forms, I'd suggest using a global variable in a Module:
    Code:
    Global gStrYear as string
    You can then set this variable in the logic you already have, so instead of:
    Code:
    Me!mySelections.Value = sTemp
    It would be:
    Code:
    gStrYear = sTemp
    I'd recommend you go through an Access VBA tutorial, such as the following:
    Access Tutorial: VBA Basics in Access 2003/XP/2000/97

    Ax

  7. #7
    Join Date
    Sep 2009
    Posts
    6
    Thanks Ax238, for all your help with this issue! I tried to create global variables as you suggested, but was unable to get it to work for some reason. A little more searching online and I found the answer which I want to share with who else runs into this issue. Check out the demo by Jack Cowley posted on this link (#5) http://www.access-programmers.co.uk/...ad.php?t=25867. Used it and worked like a charm! Basically using a InParam function.

Posting Permissions

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