Results 1 to 4 of 4
  1. #1
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    dropdown list based on selection of another dropdo (2003)

    Hi all,
    I work in a hospital and am working on a database that will be tracking patients that smoke that are interested in quitting.
    I am having trouble with the form for data entry that asks the

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

    Re: dropdown list based on selection of another dropdo (2003)

    See the attached version. The amount of code is minimal: the AmountID combo box is requeried in the After Update event of the TobaccoTypeID combo box and in the On Current event of the subform:

    Private Sub Form_Current()
    Me.AmountID.Requery
    End Sub

    Private Sub TobaccoTypeID_AfterUpdate()
    Me.AmountID.Requery
    End Sub

    But a combo box on a continuous subform is tricky: if you change its row source, it changes for all records in the subform, not just for the current one. Since the amounts for e.g. cigarettes are different from those for pipe, the combo box will be cleared for records with a different type of tobacco. To get around this, I added tblAmount to the record source of the subform and placed a text box bound to the Amount (text) field over the AmountID combo box. The text box will display the correct values.

    Two other changes:
    - I renamed the subform as a control on the main form to frmTypeAmountSub.
    - I changed the criteria for TobaccoTypeID in the row source of the AmountID combo box to

    Forms!frmEncounter!frmTypeAmountSub!TobaccoTypeID

    This refers to the TobaccoTypeID combo box on the subform on the main form. The subform itself is not part of the Forms collection, only the main form is.

  3. #3
    Star Lounger
    Join Date
    Mar 2004
    Posts
    92
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: dropdown list based on selection of another dropdo (2003)

    Thanks for answering so quickly. It's perfect.

    I would not have figured that out I'm afraid.
    I did have part of it working - if I only opened the subform - but it did what you said. Once I picked a tobacco type, it cleared for the next one.

    Thanks for the explanation as well. I'm sure I will refer back to it in the future.
    Vicky

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

    Re: dropdown list based on selection of another dropdo (2003)

    The use of a text box superimposed on a combo box is - although not really complicated - far from obvious, you really have to know about it, otherwise you wouldn't find it.

Posting Permissions

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