Results 1 to 12 of 12
  1. #1
    Hagridore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Anchorage, Alaska
    Posts
    21

    Filter by Form with combo box value as criteria when combo box contains secondary table values

    I have created a database that displays this issue so I can show what I'm trying to do, as I'm not allowed to show the actual database I'm working on. The database opens with the Faculty form showing. The text boxes txtOffice and txtBuilding pull the room number (Office) and the Building from the tblDepartment as selected in cboDepartment and display them automatically. This form presupposes using Filter by Form to search. Clicking the New Search button opens the form in that mode and hides the button. It also hides txtBuilding and displays cboBuilding, which populates with the unique values from tblDepartment...or at least it's supposed to. What happens is that cboBuilding does show up but is not accessible.



    In addition to making it accessible, I want to be able to code it to display the unique values of the field Building from tblDepartment, and I want those values to be able to use whatever value selected in cboBuilding in the Filter by Form process as a filter/search criteria.

    I thought replacing the textbox-and-combo-box solution with a single combo box might work, but I found the combo box
    inaccessible as well. This makes me think I've done something incorrectly in the setup of this control, but I can't find what in its properties.

    Here's the database. I appreciate any help I can get on this.

    11 Never Happen University Database.zip

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    First, I would not use code to populate the Office and Building textboxes. Expressions in ControlSource property can do that.

    =cboDepartment.Column(1)

    Office and Building are not available for search because those fields are not in the form's RecordSource. Change the RecordSource to an SQL:

    SELECT tblFaculty.*, tblDepartment.Office, tblDepartment.Building
    FROM tblDepartment RIGHT JOIN tblFaculty ON tblDepartment.[Department ID] = tblFaculty.Dept;

    Now the textboxes can be bound to fields and set those boxes as Locked Yes and TabStop No.
    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.

  3. #3
    Hagridore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Anchorage, Alaska
    Posts
    21
    Thank you, June7, for your reply. I think I'm closer now. I'm attaching the database with what I understand your recommendations to be. I'm still obviously missing something. First, cboBuilding is still not accessible. I was looking for how to bind these controls.

    11 Never Happen University Database.zip

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1. did not change the form RecordSource as recommended

    2. did not bind textboxes to fields (set ControlSource) and set other properties as recommended

    3. did not delete the unnecessary VBA code

    4. there is a combobox for Building behind textbox - delete the combobox and delete the form Filter and ApplyFilter macros that must not have been there in other db.
    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.

  5. #5
    Hagridore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Anchorage, Alaska
    Posts
    21
    Oh, shoot...just realized I sent the wrong zip file. I'm attaching it here. My plan was to use the cboBuilding to be the source for any search where one wants to use the name of the building to filter by. Is this not the way to do it? Also, I didn't see how to bind the text boxes to fields when the references to the parent combo box are already in their ControlSource properties. Here's what I MEANT to send this last time.

    11 Never Happen University Database (2).zip

  6. #6
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    1. good

    2. not done

    3. still unnecessary VBA code, remove it

    4. not done
    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.

  7. #7
    Hagridore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Anchorage, Alaska
    Posts
    21
    Sorry for being so dense. I'm not sure what code you think needs to be deleted. My macros in Filter and ApplyFilter appear to be necessary to me. First, they relate to the "New Search" button. Since that button isn't operational when the FilterbyForm process is initiated, I don't want it to even show up at that point. And the combo box cboBuilding is what I'm hoping can be used to search or filter for records that are in the selected building. I don't want to use a textbox for that because I want people to be able to select the specific value (building name) rather than typing it in. This particular piece of the whole is the main point of my question. I need folks to be able to select a value there if they need to and have it act as a search/filter criteria, but only show the building input through its attachment to the cboDepartment if the user isn't going to search/filter based on the building. Could it be that I could use a combo box there instead of the textbox, one which could be accessed in both display-only and active modes? If I do need to use both controls in my form to meet my need, these lines do exactly what I want them to do. The combo box needs to show up at Filter, and the textbox at ApplyFilter, so this is doing exactly what I want it to do. If the code you're talking about isn't this, could you be more specific? I also indicated in my message that I don't see how to bind the textboxes when the ControlSource already has the information in it about being populated from actions taken in the cboDepartment. Again, thanks for your help. I really do appreciate it.

  8. #8
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    If you want to use Filter by Form - the changes I implemented worked.

    I was originially talking about the VBA code behind form that populated the Office and Building textboxes. But later version showed other code.

    Also, I removed the embedded macros.

    Other methods of searching for records would involve UNBOUND controls for user input of filter parameters. My preferred method http://www.allenbrowne.com/ser-62.html
    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.

  9. #9
    Hagridore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Anchorage, Alaska
    Posts
    21
    Does this mean there's a copy of this database with changes you made somewhere? I'd love to see it. :-)

  10. #10
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Do the simple edits I did with your posted db and then you will see it.
    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.

  11. #11
    Hagridore is offline Novice
    Windows 7 32bit Access 2010 32bit
    Join Date
    Jan 2015
    Location
    Anchorage, Alaska
    Posts
    21
    I'm sorry...I must be really dense. I just clicked on the link to the database above and found no change in the ability to select a building in the building list box. I think I'm not understanding how to see the changes you made. Can you help me understand? Is there another link besides the one showing in my message above where I should be able to see your changes? If you're saying that I should follow your directions, as per my note above I don't know how. Again, I'm really sorry for being so dense.

  12. #12
    June7's Avatar
    June7 is offline VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    No. I did not post edited database. I am saying if YOU do the edits I describe on the first db YOU posted, then you will see what I see.

    1. Change the RecordSource to an SQL:

    SELECT tblFaculty.*, tblDepartment.Office, tblDepartment.Building
    FROM tblDepartment RIGHT JOIN tblFaculty ON tblDepartment.[Department ID] = tblFaculty.Dept;

    Now the Office and Building textboxes can be bound to fields and set those boxes as Locked Yes and TabStop No.

    2. delete unnecessary VBA code

    3. delete building combobox - create and attach label control to txtBuilding

    4. delete the Filter and ApplyFilter macros

    Now FilterByForm should work.
    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.

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

Similar Threads

  1. Choosing A Secondary List In A Combo Box
    By HectorTheInspector in forum Forms
    Replies: 5
    Last Post: 01-27-2014, 11:29 PM
  2. Replies: 1
    Last Post: 10-01-2013, 09:25 PM
  3. Combo Box filter not showing values
    By Chatholo in forum Forms
    Replies: 7
    Last Post: 08-01-2013, 08:53 AM
  4. Use Option Group to Filter Combo Box Values
    By dgj32784 in forum Programming
    Replies: 2
    Last Post: 06-06-2011, 12:04 PM
  5. Replies: 1
    Last Post: 04-27-2010, 09:30 PM

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