Results 1 to 7 of 7
  1. #1
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    I have a sub form set as continuous forms. On it there are 4 combo boxes for each record (A,B,C & D), B,C & D are all dependent upon a value in A to drive their queries. When on a particular record they work fine however when I move to another record that has a different value in A, all the previous selections are blanked out. The combo boxes have to be bound contros as the rates associated with them must be stored in each record. I could add fields for each of the descriptions to the underlying table but that seem to be rather wasteful. There has to be a better way but I do not know what it is. Can anyone help please?

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    The 'standard' solution to is to place a text box in front of each combo box that holds the value from the second column of the combo.

    I attach a demo. frmPeople demonstrates the problem. frmPeoplefixed has the extra text box.

    [attachment=87550:comboboxes.zip]

    I have updated the attachment. The fixed form is based on a query that joins the main table with the related table. The joint must show all records from the main table, whether a matching record exists in the related table.
    Attached Files Attached Files
    Regards
    John



  3. #3
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    As an alternative, can you not just use the Current Event to Requery the combo boxes B, C and D
    This should be fine if B,C and D are based upon Queries that reference A
    If their content is driven by the After Update event of A then maybe not

    e.g.

    On the Forms Current Event

    cboB.Requery
    cboC.Requery
    cboD.Requery
    Andrew

  4. #4
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    I don't think requerying is enough Andrew.

    When the combo has two columns with the first column having a zero width, the combo boxes appear blank for any records other than the current one (and others where combo A has the same value as the current one.

    You can see this problem in my demo, which already has requerying in the oncurrent event.
    Regards
    John



  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    AH.


    I didn't look at your demo John.
    Perhaps I ought to have done that first.


    Andrew

  6. #6
    2 Star Lounger
    Join Date
    Jan 2002
    Location
    Delta, Br. Columbia
    Posts
    116
    Thanks
    1
    Thanked 0 Times in 0 Posts
    Many thanks John; that worked perfectly. I also un-enabled and locked the two txt boxes to prevent the users mucking around. That also helped.

  7. #7
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts
    Glad that worked.

    The text box in my sample had code in the On Got Focus event that set the focus to the Combo Box. I think that achieves the same the same result.
    Regards
    John



Posting Permissions

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