Results 1 to 10 of 10
  1. #1
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    2 Combo Box Filter in a Sub-form (2000 - SR2)

    I am having problems with using two combo boxes within a sub-form, where the selection in combo box 1 defines the auto-fill criteria for combo box 2. If I open the sub-form up independently of the main form (in a testing situation) the filtering mechanism works great. However, as you can see by my error message, within the main form, after selecting the category (defined as "legal" in the attachment) I get the stated error message. This filtering combo box example, including coding and macro definition, came from the Northwind database, however in that situation it was not being used as a sub-form, only as a stand-alone form. I believe I do not have the "focus" properly defined to the sub-form, but cannot seem to identify the missing piece of the puzzle. As always, I appreciate the support.
    Attached Images Attached Images

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

    Re: 2 Combo Box Filter in a Sub-form (2000 - SR2)

    The row source of the second combo box presumably has a Where-condition that refers to the first combo box as something like

    [Forms]![Form_Name]![Combo_Box_Name]

    The Forms collection contains all open forms. A subform on a main form is not considered to be 'open', so it is not part of the Forms collection. If you want to refer to a combo box on a subform, you have to include the main form:

    [Forms]![Main_Form_Name]![SubForm_Name]![Combo_Box_Name]

    Notes:
    1. Of course, you must replace the generic names in these examples by the actual names you are using.
    2. The name of the subform must be the name it has as a control on the main form; this is not necessarily the same as the name it has in the database window.

    See Forms: Refer to Form and Subform properties and controls on the Access web for a comprehensive overview.

  3. #3
    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: 2 Combo Box Filter in a Sub-form (2000 - SR2)

    You cannot set a filter on a sub-form. you need to change its recordsource to achieve the same effect. See this on The Access Web for an example. This might give you a pointer or two. <img src=/S/shrug.gif border=0 alt=shrug width=39 height=15>
    Steve H
    IT Lecturer/Access Developer
    O2K SR3/O2010; Win7Pro

  4. #4
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: 2 Combo Box Filter in a Sub-form (2000 - SR2)

    Hans,

    The condition you mentioned, [Forms]![Form_Name]![Combo_Box_Name], was what I used under the criteria for the second combo box - this is defined in the query for the second combo box, under the "criteria" field. When I changed to the method you suggested, by adding the reference to the "Main Form", I still ended up with the same error. Am I doing this correct, in defing the source for the 2nd combo box in query??? You mentioned the "Where-Condition" - is this the same thing I have done in the query, or should this be defined soley in the combo box source property field??

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

    Re: 2 Combo Box Filter in a Sub-form (2000 - SR2)

    Can you post

    1) the row source of both combo boxes; if it is the name of a query, please post the SQL of that query (if you open a query in design view and select View | SQL, you will see the SQL text, ready to be copied)
    2) the VBA code used to update the combo box and/or the subform

    That will make it easier to communicate. Thanks in advance.

  6. #6
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: 2 Combo Box Filter in a Sub-form (2000 - SR2)

    Combo Box 1 (cmbReferralCategory): Source: SELECT tblReferralCategories.ReferralCategories FROM tblReferralCategories ORDER BY tblReferralCategories.ReferralCategories;

    Combo Box 2 (cmbReferralName): Source: SELECT DISTINCTROW qrySyncReferralName.ReferralName FROM qrySyncReferralName ORDER BY qrySyncReferralName.ReferralName;
    In this query is three fields:

    Field 1: RecordID

    Field 2 : ReferralName ( which is the row source for combo box 2 - SELECT DISTINCTROW qrySyncReferralName.ReferralName FROM qrySyncReferralName ORDER BY qrySyncReferralName.ReferralName)

    Field 3: ReferralCategory (criteria in query is defined as: [Forms]![frmCallForm]![frmCallReferralSub]![ReferralCategory]


    After combo #1 is selected, there is a macro on the "After Update" property.
    Action: Requery
    Control Name: cmbReferralName

    If you can't make head or tells of this, I can send the database to you. Thanks Hans.

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

    Re: 2 Combo Box Filter in a Sub-form (2000 - SR2)

    Thanks.

    Try setting the criteria for ReferralCategory in the query qrySyncReferralName to [Forms]![frmCallForm]![frmCallReferralSub]![cmbReferralCategory] instead of [Forms]![frmCallForm]![frmCallReferralSub]![ReferralCategory].

  8. #8
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: 2 Combo Box Filter in a Sub-form (2000 - SR2)

    I still get an error message but now it appears to be related to the Macro. Should I define the control name in more detail, such as using the subform name, and then control name?

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

    Re: 2 Combo Box Filter in a Sub-form (2000 - SR2)

    I never use macros, so I can't help you with that. If you're willing to write one line of VBA code, it would go like this:

    Open the subform in design view.
    Select the cmbReferralCategory combo box.
    Activate the Event tab of the Properties window.
    Select Event Procedure from the dropdown list in the After Update event.
    Click the Builder button (the three dots to the right of the dropdown arrow)
    You will be taken to the Visual Basic Editor, with the first and last line of the event procedure already created for you.
    Make the code look like this:

    Private Sub cmbReferralCategory_AfterUpdate()
    Me.cmbReferralName.Requery
    End Sub

    Switch back to Access (Alt+F11 or use the Windows task bar.)

  10. #10
    2 Star Lounger
    Join Date
    Jun 2003
    Location
    Indiana, USA
    Posts
    141
    Thanks
    2
    Thanked 0 Times in 0 Posts

    Re: 2 Combo Box Filter in a Sub-form (2000 - SR2)

    Hans,

    That DID it!!!! Thanks so much for your time and help.

Posting Permissions

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