Discuss "Chooser Form" in the Microsoft Access forum.

Well, we've all seen it. You see it all the time when you are choosing fields in a wizard. You have a box on the left showing what you can choose, and a box on the right showing what you chose. You can toggle your choices back and forth from being chosen or not.

I know what you're thinking, "Can I do this in a database?" The answer is "Yes". The key is to set it all up properly. Some things to know first:

1. You need a general knowledge of vba, as this will entail using "Insert" and "Delete" SQL from VBA.

2. You have to have a good knowledge of how to set up queries to show what has and has not been chosen. Yes, this sounds simple, and on the chosen part it is; on the what's left to be chosen, well, we'll just say it takes a little bit of magic (in other words a dcount() function)

In this example I have 3 tables:
tblEmployees
tblEmployeeSkills
tblSkills

tblSkills will just be used as a lookup table for available skills.

First of all, you need to setup you queries to limit to chosen and unchosen.

Secondly, start a form based on tblEmployees. Make 2 listboxes based on the chosen and unchosen queries.

Next, make 2 command buttons that will use vba to insert and delete. The left side (Unchosen skills) will use the insert, and the right side (Chosen skills) will use the delete. In my example, I also gave the choice of double clicking to insert or delete.

Lastly, I made a subform based on the chosen skills. With every update, both listboxes will need to be requeried.

I didn't go into great detail; that's up to you to look at the queries and the vba. This is just an example of how to make a chooser form to update your tables.

The download is now in the next post with updates being added.

Ok, changes made. Added record navigation error checking; fixed grouping for skills not chosen.

Here is the updated DB.

Edit: Thanks AOG for your help

Attached Files

chooser.zip (39.9 KB, 23 views)

VERY nice, benji. I included a sort of chooser (I didn't think to call it that) in the demo database that I just posted here, but you've carried it through to assigning the skills to the employees, which is a step further than I took it. Nice work!

I down loaded the chooser thing and it seems really cool so thanks but when I tried to use it I got an error msg. I personally am not really ready yet for something like that nor do I think I have enough knowledge of how to impliment it in a DB. Like I said though it seems to have a bug. Thank you though for it. It seems great.

Have you extracted it from the zip file?

__________________
If i helped you, make me famous by clicking the

Yeah, if you run any database from the zip, you will get errors because it cannot update the database. Make sure you extract the file and run the extracted file.

Oh Ok Ill try that but like I said cool stuff cant wait to see it work and more than that cant wait to be able to know how to do it in my stuff