Chooser Form

**sbenj69** · sbenj69, August 1st, 2008 02:58 AM

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.

**sbenj69** · sbenj69, August 1st, 2008 05:02 PM

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

**don94403** · don94403, August 3rd, 2008 12:47 AM

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!

**nboscaino** · nboscaino, August 12th, 2008 08:30 AM

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.

**AOG123** · AOG123, August 12th, 2008 08:31 AM

Have you extracted it from the zip file?

**sbenj69** · sbenj69, August 12th, 2008 10:41 AM

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.

**nboscaino** · nboscaino, August 14th, 2008 09:53 AM

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

**rivergum23** · rivergum23, February 19th, 2009 06:39 AM

Originally Posted by sbenj69

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.

G'Day sben69,
I downloaded this program for something I was working on and it gave me some really good ideas and how too's. If it's possible at can you suggest how to do the following.

Basically to give you an overview of what I'm doing is that I'm creating a MS Access program to allow me to make sure that when I despatch a group of parts to make a kit I have a way of checking that I have packed all required parts to make up the required kit.

So relating it back to your program when an item is packed into the box I select or double click the item and it transfers it to one column to the other therefore making it easy for me to see if any parts have been missed. I want to go to the next step by installing a barcode scanner or keyboard strokes and as soon as I scan the item it goes automatically goes from one column to another.

Any help would be greatly appreciated.

Brett

**sbenj69** · sbenj69, February 19th, 2009 01:06 PM

I've never used a scanner before, but from what I know, it's like entering in a group of characters and hitting enter, right? So instead of the button to transfer, you could put an unbound textbox, that does the same thing after update. It's been awhile since I've made or even looked at this program, and I can't really right now, as I'm at work, and tonight I have to fix a computer for a friend. So, I'm going by memory, but you should be able to do this with an unbound textbox with changing very little.

**rivergum23** · rivergum23, February 19th, 2009 06:12 PM

Firstly thanks heaps for your response.

Yes you are correct in saying that connecting a scaner is exactly like what you said, entering in keystrokes and pressing enter.

I will have a go at doing this tonight myself, but I'm not 100% sure I understand what you are asking so if you have a spare 5mins to show me that would be great.

Thanks Again

Thread: Chooser Form

LinkBack

Thread Tools

Display

Chooser Form

RE: Chooser

Chooser Form

Similar Threads

Time Entry Form

Simple way to email form

Generic Form Handler

filters on a form

Bookmarks

Bookmarks

Posting Permissions