Results 1 to 8 of 8
  1. #1
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Combo Boxes in a Continuous Form (Access 2000 or 2003)

    Hi. I am creating a new database where I need to have a field where I can add salespeople to a particular case. I saw on another database (not my own) that they created a subform which displays as a continuous form. The subform contains a combo box and if you select a person from the first box, it automatically gives you another empty combo box to choose a second person, if required. You can choose multiple people and I don't think there is a limit - it just keeps creating an additional blank combo box for you each time (this by far beats my method of creating a maximum number of fields (say, 10) and then separate combo boxes that stores the names in "salesperson1", "salesperson2" etc.)

    I can't figure out how to replicate this very snazzy feature and I was wondering if someone could point me in the right direction.

    Thanks,

    JoeK

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

    Re: Combo Boxes in a Continuous Form (Access 2000 or 2003)

    This is implemented by creating a separate table in which each salesperson is a separate record. The main table does *not* have a field for the salesperson(s).
    The separate table has two fields: one that links to the ID field of the main table, and one that links to the ID field of the table that lists all salespersons.
    The subform is based on the separate table, linked to the main form on the main ID. It contains a combo box listing the salespersons, i.e. its row source is the table of salespersons.

    See <post#=364,203>post 364,203</post#> for a sample database showing how to implement a many-to-many relationship using forms and subforms.

  3. #3
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes in a Continuous Form (Access 2000

    Hans,

    Thanks for your answer and the link to the sample db - it was very helpful. I managed to create the correct relationships etc, but I am having trouble creating the actual subform. The subform displays correctly and gives me the correct data in the dropdown list. However, when I select a name from the dropdown list, I get the error message that "You cannot add or change a record because a related record is required in table "tbl_ref_number_details" " (the "separate" table - the one with two fields - links to "tbl_ salesperson" and "tbl_ref_number_details").

    I am stumped and could do with some help.

    Thanks,

    JoeK

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

    Re: Combo Boxes in a Continuous Form (Access 2000

    Have you linked the subform correctly to the main form by setting the Link Child Fields and Link Master Fields properties of the subform as a control on the main form?

  5. #5
    4 Star Lounger SteveH's Avatar
    Join Date
    Jan 2001
    Location
    Chelsea, Gtr London, United Kingdom
    Posts
    587
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes in a Continuous Form (Access 2000

    I suspect that your combo box might be bound to the wrong "tbl_ref_number_details" field. It must be bound to the field in the linking table not the one in the main table. The wizard often makes this mistake (as my students found out recently). Check that the recordsource for your subform has the correct field in it as well.

    I have attached a handout I created explaining the 3 main types of combo boxes and when/where they are used.
    Attached Files Attached Files
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

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

    Re: Combo Boxes in a Continuous Form (Access 2000

    Thanks, that's a useful handout!

  7. #7
    2 Star Lounger
    Join Date
    May 2002
    Location
    Jerusalem, Israel
    Posts
    187
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Combo Boxes in a Continuous Form (Access 2000

    Thanks to you both for your replies and very useful hints. I got the combo boxes to work properly. One interesting bug, though, and I don't know if it is important or not: whenever I selected a name from the combo box that had an autonumber ID of 1, I got the same error messages as before. But when I deleted that name and then entered the name into the names table again (and therefore deleting record 1), I didn't get the error at all and haven't since. I checked all the relationships etc and they are identical to other similar combo boxes I created, which don't have this bug - so I am mystified!

    I wonder if I have worked around the problem or could the bug reappear in the future somehow? Should I be worried about it?

    Thanks,

    JoeK

    Thanks.

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

    Re: Combo Boxes in a Continuous Form (Access 2000

    That's impossible for us to say, without having seen the database before and after the change.

Posting Permissions

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