Results 1 to 7 of 7
  1. #1
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Adding fields "on the fly"

    Using Access97:

    Is there any way I can have the results from a multi-select listbox add fields to a table once the user makes their selection and exits the listbox? I have a group of about 35 items to select from (of which the user might choose from 1 to 8 or so), and would rather not have to include fields for all 35 choices in my table.

    Any help is greatly appreciated!

  2. #2
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding fields

    Do you mean "add a _record_ to the table for each entry in the multi-select listbox", or do you really want to add a field?

    If you want to add a field, can you let us know your data structure and what you are trying to achieve - it sounds like you might want to have two tables here.

  3. #3
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Brisbane, Queensland, Australia
    Posts
    352
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding fields "on the fly"

    I expect it can be done but I haven't so I will leave that for someone else.

    What you could do is to put all the fields in you table but hide them in forms and reports depending on what was ticked.
    David Grugeon
    Brisbane Australia

  4. #4
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding fields

    Here's the info:

    I'm creating a database to track (insurance company) client submissions. One of my tables, tblCoverages, requires the user to enter the types of exposures this particular policy should cover, of which there are a total of about 35. I've created a separate tblExposures to handle those records, but would prefer not to clutter up my Coverages table with a field for all of them when only a few would be applicable.

    Hope this makes sense. If not, I'll be happy to provide more detail.

  5. #5
    Star Lounger
    Join Date
    Feb 2001
    Location
    Melbourne, Victoria, Australia
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding fields

    One way to do this is as follows:
    Tables:
    tblClientSubmission:
    SubmissionID; ClientName; Whatever

    tblCoverages:
    SubmissionID; ExposureID

    tblExposures:
    ExposureID; ExposureDescription

    Relationships:
    one: tblClientSubmissions.SubmissionID many: tblCoverages.SubmissionID

    one: tblExposures.ExposureID many: tblCoverages.ExposureID

    Then for every submission, the user selects the required exposures in the multiselect listbox. The listbox is populated by tblExposures. On some event, execute some VBA: For every exposure selected in the listbox, append a record to tblCoverages. Specify the Current Submission ID and the Current Exposure ID. (Your initial post asks how to do this bit).

    This means that each record in tblCoverages has lots of records but only two fields: A pointer to the Client Submission and a pointer to the Exposure type. To find the Exposures for a particular Client, you query tblCoverages based on a SubmissionID.

    There are ways to refine this solution further, but it's a starting point. Please advise if this makes sense in your application.

  6. #6
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Adding fields

    Normally, you do this kind of thing using subclasses, which are implemented in Access/VB by building a one-to-one relationship between your main policy table and another table that has the same primary key as the main table but contains fields that only apply to a particular subset of records in the main table. So, for instance, if you had both life insurance and homeowners insurance policies, the type of information you would store would be different and you would use two subclasses of the main policy table to hold the different kinds of information peculiar to the different kinds of coverages. You would then have a separate Exposures table that would have a single record for each exposure for that particular policy. Include fields for the PolicyID, the type of exposure (that is, this field holds the name of the exposure type, rather than having a bunch of fields for different exposure types), and any other information that relates to this particular exposure type for this particular coverage. Some policies/ coverages might have one or two exposure types and some could have a large number of them. By using this approach, you never have to go back and add a field to your table for yet another type of exposure. You simply add another record instead and give it the new exposure type.
    Charlotte

  7. #7
    Lounger
    Join Date
    Feb 2001
    Location
    Upstate, New York, USA
    Posts
    47
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Adding fields

    Charlotte and Marty -

    That's why I love this forum - you guys take something that seems enormously complicated and break it down to its simplest possible solution. Thanks much for the prompt responses and the sound advice.

    Kyle

Posting Permissions

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