Results 1 to 7 of 7
  1. #1
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filtering Selections in ComboBox (97)

    I'm trying to get my head around this one, so bear with me.

    From what I read you have 4 tables, they are:

    tblClient
    ClientID
    etc...

    tblIndicators
    ClientID
    IndicatorID
    etc...

    tblIndicatorDetails
    ClientID
    IndicatorID
    RatingID
    etc...

    tblRatings
    RatingID
    Description
    etc...

    You should be able to make up a main form (tblClient) with a dependant subform (tblIndicators) and a dependant subform (tblIndicatorDetails) under that.
    This should be fairly straight forward.

    Have I got this right?

    Pat

  2. #2
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Selections in ComboBox (97)

    You're on the right track.

    tblIndicators does NOT have a client ID in it - it has only InidicatorID (autonumber) and InidicatorName (I.e. smoking, weight).
    tblClients is joined to tblIndicatorDetails by ClientID. No other table is joined to tblClients
    tblIndciators is joined to tblIndicatorDetails by IndicatorID
    tblRatings is joined to tblIndicatorDetails by RatingID
    And I have a main form for client and a subform for InidcatorDetails, linked by ClientID. There can be only one rating selected for a client for each indicator, so I have only one subform. Is there something I'm missing that having two will allow me to do?

    The problem that comes up is that the combobox for the ratings shows ALL the ratings (which means that to get to the indicators for smoking you have to wade through the ratings for every other indicator - once the user has entered the indicator "smoking" we don't want the rating 1 - 50 or more pounds overweight". Once the user has started to create a record in the subform for "smoking", we want them to be able to choose only from: 1-more than two packs per day, 2-one pack per day, 3-less than one pack per day - I think you get the idea. (When entering data, the indicator does get entered before the rating)

    I thought I could do it by having the underlying query for the rating combobox on the subform have four columns - one for each field in the tblRatings (ID, IndicatorID, Rating, Explanation). The criteria for the field IndicatorID would be [forms]![frmIndicatorDetailsSub]![IndicatorID]. Then, I thought, the only fields that would be visible in the combobox would be Rating and Explanation, and the choices would be filtered based on the entry that had just been made in IndicatorID on the subform. I can get it all to work except the filtering. Either ALL the choices show, or no choices show.

    Does that make it more clear?
    Thanks,
    -cynthia

  3. #3
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Filtering Selections in ComboBox (97)

    I am helping a friend who is working on a database to track client health indicators. For each client there are up to twelve indicators (smoking, weight, etc), and for each one, the client gets a rating. The complicated part is that each indicator has different ratings

  4. #4
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Selections in ComboBox (97)

    Oops! I was just reminded that the ratings and indicators need to be entered for each client once in each of four quarters. I had not yet taken that into consideration in our design. I'm not worried about that part, but am including it here only in case it has bearing on how anyone might advise us to solve the filtering problem. I've amended my original post to include this situation.

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

    Re: Filtering Selections in ComboBox (97)

    It looks to me that all that is wrong is the reference to the control on the subform (in the query criteria).
    You have used: <pre>[forms]![frmIndicatorDetailsSub]![IndicatorID] </pre>

    When you refer to a control on a subform you need a different syntax.

    forms![frmClients]![frmIndicatorDetailsSub]![IndicatorID]

    Remember also that you need to requery the combo box in the oncurrent event, and in the afterupdate event of IndicatorID
    Regards
    John



  6. #6
    Platinum Lounger
    Join Date
    Dec 2001
    Location
    Melbourne, Australia
    Posts
    4,594
    Thanks
    0
    Thanked 27 Times in 27 Posts

    Re: Filtering Selections in ComboBox (97)

    You wrote:
    >>I thought I could do it by having the underlying query for the rating combobox on the subform have four columns - one for each field in the tblRatings (ID, IndicatorID, Rating, Explanation). The criteria for the field IndicatorID would be [forms]![frmIndicatorDetailsSub]![IndicatorID]. <<
    When you are in the Ratings combobox are you referring to the IndicatorID's combobox that is on the subform as well? If so, you should be able to use Me![IndicatorID] to indicate a field on the same form (being the subform).

    As John rightly points out you will need to Requery the Ratings combobox in the AfterUpdate of the IndicatorID's combobox.

    Pat

  7. #7
    3 Star Lounger
    Join Date
    Dec 2001
    Location
    Maine, USA
    Posts
    258
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Filtering Selections in ComboBox (97)

    Thank you, John, for pointing out where I strayed!
    I'm not very strong on code or syntax, and it didn't connect in my mind that I had always before used that syntax on a main form or saved query. It works now (I also set up the requerying - I knew I would have to do it on IndicatorID, but I hadn't got as far as thinking about the oncurrent. So thanks again).

    And thanks Pat for the shortcut suggestion. I used that on the events to requery, used John's longer syntax in the combobox query.
    -cynthia

Posting Permissions

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