multiselect listbox

Ok... perhaps my last thread was confusing. I am new to access... well I know my way around. I am definitley new to coding with access and vb. I have a form with a multiselect listbox. My listbox is linked to a table I have created. I want to be able to select multiple items from my listbox and when I save the form, I want the items I selected to be stored in a field in the table I created. Each time I have tried it will not work. Can someone please help.

Hi denels2,

I can probably help you with your multiselect list box, but typically you shouldn't store the results in a table field. Assuming that you want to do something with the items that are selected, can you post what the end results you are wanting to achieve rather than just storing the items in a table field?

lwells

Well here's what I want it all to do. I have a form... has stuff like name, date, you know basic stuff. Then there is a multiselect listbox. I want to be able to select multiple criteria to describe that person. For example (No this is not the real criteria) ... If the person can sing and dance. I would select sing, and I would select dance. I want this to be stored somewhere so that I can create a query and be able to query for anyone who can sing (not neseccarily dance though). I hope this is making sense. I am fairly new to Access... have taken a couple classes on it while I've been learning the process. So vb is new to me as well... actually I have never used vb... nor know where to begin... Any help is appreciated.

Denels2,

Take a look at the sample database, which has a sample multiselect listbox that adds the criteria to a table so that you can search later by. In this sample database, the same list box can also be used as your search criteria as well. The code should be fairly easy to understand, but if you still have additional questions post back.

lwells

Thanks so much for the sample database. However when I tried to add the skills like sing dance and so forth I get a compile error saying that the variable isn't defined or something similar and it highlights DAO.Database and I'm sure it will do the same for DAO.Recordset. Any thoughts

Make sure you have the microsoft DAO 3.6 object library selected under references. To do this make sure you are in the VBA editor. Once you are in the code window click Tools > References on the top tool bar. Once here scroll down untill you find Microsoft DAO 3.6 Object Library . Once you find it click the empty check box next to it. Once checked click OK. DAO.Recordset and DAO.Database are types that are stored in this library and Access needs to know where to reference them. By selecting the library it should work. Hope this solved your problem.

theguz

theguz...Thanks, I keep forgetting A2K defaults to ADO....just a habit of declaring the DAO library in my applications.

lwells

I really appreciate all the help. Here is my new dilemma. I typed up all the VB and such got the errors to work except a new one has occured. When I try to add I get a compile error says method or member type is not recognized or something similiar. Then it highlights "Me.PersonID" (about half way down in the cmdAddSkill module. I had to do some altering to the code and such to make it more wha tI need and didn't need. So here is a more better explanation of what I would like to do... I would like to take what was in the sample program and have that form (Well part of it) on the form I already have. I have a table set up with a lot of fields. I changed one to match PersonID. and did another table for skill. Any thought suggestions?

Me refers to the current active form. The PersonID after Me refers to the control name on the form that was used to display the primary key ID. So on your form check the name of the control that holds the PersonID from your table and change the Me.PersonID to the name of the control....Me.ControlName using the exact name of the control.

See if that is the problem and post back.

lwells

Ok.. I got it all error free but it just pops up with a '0' you click OK and nothing happens... Let me try to explain what I'm trying to do in a better way.

I have a form... Let's call it "Add New"
On this form there is Name, Date, What is to be added, what category does this fall under, approved...etc

Now The "What Category..." is my multiselect listbox. What the sample program has is very good... but it is a seperate form. I tried to copy the code to my form but it got all screwed up becuase it (along with myself) did not know what names to change where and for what... Does any of this make sense. I'm very appreciative of your help.

Hi Denels2

Very seldom can you copy code from a sample database and make it work without some changes. It's hard at this point to visualize your specific application to advise what changes to make in your code in order for it to work like you want. To speed things up a little, is it possible to make a copy of your application, and put it into a zip file and send to me? You can remove any data that would be confidential from the tables and let me correct the code for you. That way you can copy the code over into the master application without any changes. Plus you will get a chance to see what changes are made and I will add comments to the code as to what/why the code changes.

Use this email address lwells1433@aol.com

lwells

OK I just want to say many thanks to lwells.... So much work and effort you are awsome! Now I just have a couple of questions (By the way... I got it all to work!) I have gotten it to query on the multiselect listbox, but when it queries it brings up a query form view... I would like it to bring up a report... How can I go about doing this... If it helps any... I pretty much kept the code the same... I just changed the names of some stuff. Also I'll throw this one out there too... How can I, when I select (On the add form) a skill, such as singing, make a pop up form come up which will ask me what type of music does he/she sing (like rock, opera, blues)... or how can I do something that will give me the similar effect. Thanks for all the massive help!

Hi Denels2,

You asked, so here is another sample database where you can select additional skills to the main skills selected...subskills if you like. The technique is pretty much the same, however two additional tables had to be added to index the skills and sub skills together. Also I made a simple report to display rather than the query.

I didn't have time to go through and comment the code, so if you run into problems let me know.

Cheers,
lwells

Ok I'm working with my project with the information you gave me. One question... With the query... when I open it in design view it is code... not the wonderful view of the tables and such. How do I get to this code view?

Right click on the title bar and select Query Design. What you are viewing is the SQL of the query.

lwells