Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Multiple selection, write to table (2000)

    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.

  2. #2
    3 Star Lounger
    Join Date
    Dec 2000
    Location
    La Verne, California, USA
    Posts
    313
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple selection, write to table (2000)

    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.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple selection, write to table (2000)

    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.

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple selection, write to table (2000)

    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.

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple selection, write to table (2000)

    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.

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Multiple selection, write to table (2000)

    ... Using a multi select listbox might work here as well.
    Regards,

    Gary
    (It's been a while!)

  7. #7
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Multiple selection, write to table (2000)

    ... 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.
    Regards,

    Gary
    (It's been a while!)

  8. #8
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple selection, write to table (2000)

    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

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple selection, write to table (2000)

    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.
    Attached Files Attached Files

  10. #10
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple selection, write to table (2000)

    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?

  11. #11
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Multiple selection, write to table (2000)

    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

    <img src=/w3timages/blueline.gif width=33% height=2>

    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.

  12. #12
    New Lounger
    Join Date
    May 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple selection, write to table (2000)

    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.

  13. #13
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple selection, write to table (2000)

    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.

  14. #14
    New Lounger
    Join Date
    May 2002
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple selection, write to table (2000)

    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?

  15. #15
    2 Star Lounger
    Join Date
    May 2002
    Location
    USA
    Posts
    190
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Multiple selection, write to table (2000)


Page 1 of 2 12 LastLast

Posting Permissions

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