Results 1 to 6 of 6
  1. #1
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159

    Apply Filter to subform, based on cmb box selection

    The title explains what I'm looking for. I have a Form with a subform on it. I have a combo box that I would like to use to filter the subform. Based on what is selected in the combo box, all of the records in the subform would be filtered by that.



    Not sure if I need to add a command button or just change the cmb box's After Update stuff.

    Not quite sure what the code needs to look like either.

    I feel like these two links provide some input on what I'm looking for, but I'm not getting anywhere with this.

    https://www.accessforums.net/forms/f...-box-5426.html
    http://database.ittoolbox.com/groups...filter-1694353

  2. #2
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    More info:

    I have a form called : Colleges with a record source, a query, called "College Rosters"

    The subform on the "Colleges" form is called: College Rosters subform

    The combo box on the "Colleges" form is named: cmbSchool

    The combo box is based on a query that contains College.ID, College.School, College.Conference, where College.ID is what would bound everything together.

  3. #3
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    I was really close to having this work earlier. I would select a school, hit the command button, but instead of filtering the records immediately, I would get a message box that would ask "Me.School." Once I typed the school name in and pressed OK, then it would filter.

    I think I had some VBA code for Do.Cmd.ApplyFilter. Don't remember what it was now.

  4. #4
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    http://www.access-programmers.co.uk/...d.php?t=121662

    The zip file at the bottom of this link is exactly what I want to do, but I have a many questions about how they do that.

    One question is, how do they have their combo boxes only showing one of each selectable option. For example, on the Region combo box, they are able to show just the four values even though the table below has multiple values for each one of those. If I were to make a combo of the same thing, it would list every Northern, Southern, etc. down the list, not just one of each.

  5. #5
    yes sir is offline Competent Performer
    Windows Vista Access 2007
    Join Date
    Aug 2010
    Posts
    159
    anyone know whats going on?

  6. #6
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    To filter records in a subform based on a combobox on the main form, I wouldn't use the applyfilter command. I personally try to avoid any kind of filtering commands since they tend to be problematic. Especially when dealing with odd characters in the field you're trying to apply the filter on.

    What I typically do is put criteria into the subform's recordsource and then simply issue a Forms!MyMainFormName!MySubformName.requery command in the afterupdate event of the combobox on the main form.

    For example (and assuming that your main form is not based on a recordset itself), I will have criteria in the subform's recordsource under the appropriate field such as: Like Forms!MyMainFormName!MyComboboxName & "*". And then again, when the 'MyComboboxName is updated on the main form, issue the requery command to the subform.

    Now if the main form is based on a main table and the subform is based on a relational table, it's a little different since you want to find the main record on the main form and then find the subform's record. In this case, I may use the FindRecord command where the combobox has the ID (ie. autonumber) value as the value returned (which may be hidden). Then in the afterupdate event of the combobox, I'll set the field to the autonumber field on the main form and issue a Docmd.FindRecord me.MyComboboxName.value. Once the main record is found on the main form, I'll use the 2nd column of the combobox value (which has the autonumber of the relational table) to then set the focus to the subform and the autonumber field and then again, issue a Docmd.FindRecord me.MyComboboxName.column(1).

    There are a couple of different techniques to how you want to set this up. You could also setup a popup search type form to find the record.

    Here are some examples I've posted in the code repository which may be helpful:
    https://www.accessforums.net/code-re...hods-7689.html

    And some reading on how setting things up:
    http://www.dbforums.com/microsoft-ac...de-bank-6.html

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 29
    Last Post: 08-16-2011, 05:52 PM
  2. Replies: 0
    Last Post: 08-24-2010, 06:38 PM
  3. Apply filter command
    By miziri in forum Forms
    Replies: 6
    Last Post: 01-21-2010, 02:22 AM
  4. Replies: 0
    Last Post: 12-16-2009, 01:14 PM
  5. Replies: 1
    Last Post: 03-01-2009, 09:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums