Results 1 to 4 of 4
  1. #1
    sberti is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    33

    Filter Form based on Table with Combo based on Query?

    The following is a simplified version.

    I have a table called tblOps with the following fields:
    OpsID; Dept; Section



    The data entry for the fields 'Dept' & 'Section' is done from combo boxes.
    The combo boxes are based on tables:
    tblDept with fields: DeptID; DeptName
    tblSection with fields: Section ID; Section Name

    In each case when the selection is made, the ID is entered into the table, not the Name.

    I have a form for this the tblOps and would like to be able to filter the records with a combo box. When I use the Combo Box Wizard, of course the choices in the list show only the ID numbers not the Names in the fields. This is not useful for the end users, since they don't know all the ID numbers.

    I've made a combo box based on a query that includes, the not only the tblOps, but also has linked the tables tblDept & tblSection.
    In this query I have selected the following fields to create the drop-down list:
    OpsID; DeptName; SectionName

    The combo box works on my form with a usable drop-down list, but does not filter my records. How do I get it to filter the records in my form?

    Am I on the right track? Can I filter the records in a form using a combo box based on a query?

    Thanks ahead of time, if anyone can help me.

  2. #2
    burrina's Avatar
    burrina is offline VIP
    Windows 8 Access 2010 32bit
    Join Date
    Oct 2012
    Location
    Freeport,Texas
    Posts
    1,383
    Probably because you don't have the correct criteria for the combo. Normally this uses the afterupdateevent with something like:
    ' Find the record that matches the control.
    Dim rs As Object




    Set rs = Me.Recordset.Clone
    rs.FindFirst "[jobid] = " & Str(Nz(Me![cbosearch], 0))
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark

    HTH... Hard to tell without seeing what you have!

  3. #3
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The name should not be entered, only the ID. Otherwise that is duplicating data.

    What is purpose of tblOps? What is relationship of department and section? Sections are part of departments - departments are higher level? Why not just put DeptID in tblSection?

    Use query that joins related tables.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  4. #4
    sberti is offline Advanced Beginner
    Windows 7 64bit Access 2010 32bit
    Join Date
    Nov 2012
    Posts
    33
    Thanks for your responses.
    It had been a while since I had done this, but I discovered that my solution was very simple. I should have remembered.
    In the Link Master property, I had not put the combo box name. It was still linking by the OpsID in both the Link Master and Link Child properties.
    It's working fine now.
    Thanks again.

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

Similar Threads

  1. Replies: 1
    Last Post: 12-29-2013, 11:21 AM
  2. Replies: 1
    Last Post: 04-11-2013, 07:56 AM
  3. Replies: 5
    Last Post: 01-16-2013, 11:51 AM
  4. filter form based on combo box
    By lloyddobler in forum Forms
    Replies: 8
    Last Post: 09-10-2009, 07:33 AM
  5. Form Based Query/Filter
    By Micon in forum Access
    Replies: 0
    Last Post: 11-07-2008, 09:25 AM

Tags for this Thread

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