• Multiple selection, write to table (2000)

    Home » Forums » AskWoody support » Productivity software by function » MS Access and database help » Multiple selection, write to table (2000)

    • This topic has 16 replies, 6 voices, and was last updated 23 years ago.
    Author
    Topic
    WSNight
    AskWoody Lounger
    May 23, 2002 at 3:09 pm #371323

    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
    Viewing 3 reply threads
    Author
    Replies
    • WSPrestonK
      AskWoody Lounger
      May 23, 2002 at 3:17 pm #589904

      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
      • WSNight
        AskWoody Lounger
        May 23, 2002 at 3:23 pm #589909

        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
    • WSHansV
      AskWoody Lounger
      May 23, 2002 at 3:25 pm #589910

      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
      • WSHansV
        AskWoody Lounger
        May 23, 2002 at 3:27 pm #589911

        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
    • WSGARYPSWANSON
      AskWoody Lounger
      May 23, 2002 at 4:13 pm #589937

      … 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
      • WSNight
        AskWoody Lounger
        May 23, 2002 at 4:48 pm #589943

        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
        • WSHansV
          AskWoody Lounger
          May 24, 2002 at 10:35 am #590094

          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
          • WSNight
            AskWoody Lounger
            May 24, 2002 at 12:20 pm #590123

            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
            • WSHansV
              AskWoody Lounger
              May 24, 2002 at 12:49 pm #590136

              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
    • WSjkeyz
      AskWoody Lounger
      May 24, 2002 at 2:46 pm #590174

      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
      • WSNight
        AskWoody Lounger
        May 24, 2002 at 3:55 pm #590192

        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
        • WSjkeyz
          AskWoody Lounger
          May 24, 2002 at 5:03 pm #590201

          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
          • WSNight
            AskWoody Lounger
            May 24, 2002 at 5:13 pm #590203
            Reply | Quote
          • WScharlotte
            AskWoody Lounger
            May 24, 2002 at 11:45 pm #590249

            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
            • WSjkeyz
              AskWoody Lounger
              May 25, 2002 at 4:09 am #590261

              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
    Viewing 3 reply threads
    Reply To: Multiple selection, write to table (2000)

    You can use BBCodes to format your content.
    Your account can't use all available BBCodes, they will be stripped before saving.

    Your information:




DON'T MISS OUT!
Subscribe to the Free Newsletter
We promise not to spam you. Unsubscribe at any time.
Invalid email address

View the Forum

  • Recent Replies
  • My Replies
  • My Active Topics
  • New Posts in the Last day
  • Private Messages
  • Knowledge Base
  • How to use the Forums
  • All Forums

    • Recent Topics

    My Profile

    June 2025
    S M T W T F S
    1234567
    891011121314
    15161718192021
    22232425262728
    2930  

    Remembering Woody

     

    Want to Advertise in the free newsletter? How about a gift subscription in honor of a birthday? Send an email to sb@askwoody.com to ask how.

    Mastodon profile for DefConPatch
    Mastodon profile for AskWoody

     

    Home • About • FAQ • Posts & Privacy • Forums • My Account
    Register • Free Newsletter • Plus Membership • Gift Certificates • MS-DEFCON Alerts

    Copyright ©2004-2025 by AskWoody Tech LLC. All Rights Reserved.