Multiple selection, write to table (2000)

Topic

New Reply

Ok, this is what I have. I have a table that basically has about 55 fields in it. Each field is the yes/no data type. The reason I do this is as follows.
I need to create a form where a person can select the states that he/she are interested in transfering to. Now the reson I did what I did was because it was the only way I could figure out how to have them check the box and have it update into a table. Is there a better way that this might be done. This will work fine, but I am just thinking it might be a little much. Surely there is an easier way. Anyway Thanks in advance.

Reply | Quote

Replies

Personally I would favor the use of combo boxes for this type of selection and store the selections in the table. A series of combo boxes for “first choice”, “second choice”, etc., might be useful here.

Reply | Quote

Well normally that would be a good idea. The only thing is there are about 55 choices that can be chosen from. A person can chose 1 or 55 of the options. Now they wouldn’t click all 55 boxes if they would go anywhere because there is an ALL option. The combo box limits the number of choices a person can make and I am unable to do that since my requirements specify that I must give a person the option to chose as many as they want. If you can do that with combo boxes or in another way that isn’t so long then I’d appreciate the help.

Reply | Quote

… Using a multi select listbox might work here as well.

Reply | Quote

An alternative is to use several tables:

1. A first table with StateID and State.
2. A second table with UserID and User info.
3. Depending on your preferences:
3A. Either a third table with UserID and StateID representing only those states the user is interested in.
3B. Or a third table with UserID, StateID and a boolean field Interested, containing one record for each possible UserID/StateID combination.

In case 3A, you’d create a form with unbound check boxes for all the states. When the user exits or clicks a Save button or something like that, use code to add the appropriate records to the third table.

Or you can have a continuous subform with bound combo boxes, where users can add states selectively.

In case 3B, you can use code to create the records for the user when a new UserID is created. Present the bound check boxes in a continuous subform.

Reply | Quote

As an afterthought:
In case 3A you can also present the choices in a multi-select list box. Again, save only the selected StateIDs together with the UserID in the third table. This must be done in code.

Reply | Quote

… What would be really neat is to create a map with the check boxes in each state. I actually like your checkbox idea because it gives the flexibility to group the data alphabetically, by region, or other preferences. Since there is no limit to the number of choices, you may need too many combo boxes if you go that route.

Reply | Quote

It would be neat to create a map, and not to difficult to do so. But unfortunately I don’t have the time or flexability to do all of that before the dead line. You also hit the nose on having to group the states. They are grouped by specific Zones, each state is in a certain zone.

hansV, – This is the setup I have so far. First I have a table that holds for the most part all the important user data: Name, Current zone, Current State, type of transfer wanted, Date file is recieved, date sent for review, amoungst other things. I also have a table that holds the fields for each of the 55 choices. One field for each choice with the Yes/No data type. One problem I’ve run into when creating my forms is somewhat simple, but difficult. For the purpose of design and looks I put the state selections on a different form so that it doesn’t clutter the other form. Now is there a way I can do this so that the state form will recognize the name that was entered on the original for so that if I need to I can print a report that will list the names of the people that want to go to a specific state. Thanks

Reply | Quote

You can refer to the user on the original form as

Forms![frmOriginal]![UserID]
(adapt names as necessary)

You can do this in code behind the states form and in expressions.

I have included a small demo made in Access 97, based on my earlier suggestion of using three tables.
There are just a few users and states to serve as example.
It was put together using wizards mostly, without attention to aesthetics, so it looks ugly.

From frmUser you can open two different forms – one uses the combo box approach, the other the multiselect list box approach (the latter is Modal, so you must close it before switching to another user).

I didn’t implement the check box approach – creating many unbound check boxes is not much fun.

There are two reports. One presents the data grouped by user and the other by state.

You’ll have to unzip the database and convert it to Access 2000.

Reply | Quote

I thank you for the example. I think I will decide to go with a list box. My supervisor gave me the permission to do so just at the end of the day yesturday your example will help me out a lot in determining what I need to do. Now for a different question. Will the format you used work just as well for me if I want to be able to enter in new data into the tables? Also for the recalling of the record, these will be two different forms. So when ever I hit the new entry button a form comes up with blank fields in all the areas, and if I hit Update Record it runs a search by the persons ID number. After the search the another form is supposed to pop up with all the relevant data. Any suggestions on a different approuch or advice on that?

Reply | Quote

The example I attached is by no means complete or finished.

For instance, when someone clicks the “Alternative” button in the main form, you should check whether UserID is filled. If not, exit the OnClick routine:

If IsNull([UserID]) Then
MsgBox “You must enter some data first!”, vbInformation
Exit Sub
End If

And if the record has been updated, save it before opening the other form:

If Me.Dirty Then
RunCommand acCmdSaveRecord
End If

The form with the list box can be used to modify existing data. In the code behind the OK button, the records for the UserID selected in the main form are thrown away, then created anew based on the selected items in the list box.

Reply | Quote

Hi all,

I’m trying to do a similar task. If I have the multi select property of a list box on my form set to “simple”, can I get the list of mulitple selections to write to one cell in a table?

Thanks for your help.

Reply | Quote

I have been trying to do that for a very long time, that however, just does not seem to work. I looked it up on microsofts website and they said it can be done with a little bit of code, but it is a bad design habit to get into since you won’t be able to query specific data from that field. For example if you have Apples, Oranges, Pears in the field cause you chose those three you won’t be able to look for just apples. At least that is the way I understood it from the article Microsoft putt out.

Reply | Quote

Thanks for the reply! Yeah, I realize that about the inability to query. (In this particular case, I don’t need to be able to query the field.) Do you happen to have a reference to the microsoft article you mentioned?

Reply | Quote

http://support.microsoft.com/search/previe…b;en-us;Q210203

that is the link to the article.

Reply | Quote

There are other problems with doing it that way. For example, you can’t use a listbox to automatically *display* the answers you captured. That being the case, what’s the point?

Reply | Quote

My end aim is to provide a text file export that lists all the selections a user made in one text string. (The text file will match a format that was set in Filemaker.) For example, if from among the 50 states the user chose MI, CO, and CA, the final entry in the field (and the export) will read “Mi;Co;CA” for that given record.

Reply | Quote