Results 1 to 7 of 7
  1. #1
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52

    Filter records not working

    Hi
    I am trying to filter records based upon the Dept.

    I have the following tables.

    Tbl_Start_Leaver
    PtSubDept (e.g. PT-271
    EffectiveDate
    ChangeType
    Cdsid
    Surname
    Forename
    Title


    Grade
    SAP Position No
    Position Type
    Comments

    Tbl_Pt_Dept
    PtSubDept PT-271
    PtDept PT-27
    SeniorMgrType PT-2

    I have a form that has a combo box to try and filter the records.

    Click image for larger version. 

Name:	Filter.jpg 
Views:	13 
Size:	140.6 KB 
ID:	25301

    The problem is that the drop down for the combo box appears to work but I can see that something is not right in the way that I am setting this up.

    in the row Source of the combobox I have following SQL :

    SELECT Tbl_Start_Leaver.ID, Tbl_Pt_Dept.ID, Tbl_Start_Leaver.PtSubDept, Tbl_Pt_Dept.PtSubDept
    FROM Tbl_Start_Leaver
    INNER JOIN Tbl_Pt_Dept ON Tbl_Start_Leaver.ID = Tbl_Pt_Dept.ID;

    This clear does not appear to be the correct way.

    I need to be able to have PT-27 and I would only only have two records in my display i.e.
    PT-27 and PT-271 likewise if I chose PT-60 I would get PT-60 and PT-601.

    Hope I have explained correctly.

    Thanks in advance for any help.
    Last edited by mond007; 07-28-2016 at 09:23 AM. Reason: typo

  2. #2
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    Once you make a selection from the combo box, what do you do with it, i.e. how are you attempting to filter the records?

    Is it a main form - subform arrangement?

  3. #3
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Ok, so the form is a single table form. The Top half is the obviously the table fields and the lower being a Datasheet Read Only view for quick navigation.

    So the first problem is that the combobox values are incorrect owing to (I think) the Id's being stored in the Tbl_Start_Leaver.PtSubDept. So the Depts shown in my diagram are actually incorrect.

    As per the image, if the
    Tbl_Start_Leaver.PtSubDept records contains the following :

    PT-272
    PT-601
    PT-27
    PT-60

    In the Combobox (that is for the filter) I would expect to see is the following :

    PT-27 (That is because PT-271 is a sub dept of PT-27)
    PT-60 (That is because PT-601 is a sub dept of PT-60) - Hope that make sense.

    I think I need a select distinct in the Rowsource in there somewhere. I also know that I need to take the Left 4 chars of all the Tbl_Start_Leaver.PtSubDept to arrive at the correct higher level combobox values.???

    The second part is to be able to select say PT-27 and only all Depts that belong to that hierarchy of set would be displayed - in the above case only PT-27 & PT-271. etc..

    I am struggling on both counts. Any advice would be greatly appreciated. Thanks

  4. #4
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    INNER JOIN Tbl_Pt_Dept ON Tbl_Start_Leaver.ID = Tbl_Pt_Dept.ID;
    That join doesn't seem to make sense. What are the ID fields of the two tables - you don't list them in the table fields. If they are just ID's of the records in each table, then the join is wrong for sure.

    It's not clear what you want to list in the combo box. If you are trying to filter based on dept, you don't need both tables on the combo box row source at all - all you need is the list of departments from Tbl_Pt_Dept, hopefully with a description so users know what they are selecting.

    Now to the next question - once you make a selection from the combo box, what happens then? Combo boxes do not magically do filtering by themselves - you have to write a little bit of code to make the filtering happen.

  5. #5
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Hi, Yes the join is definitely wrong. I am not sure how to achieve what I need to. I will try to explain again.
    Yes correct - "If they are just ID's of the records in each table, then the join is wrong for sure." they are only Id's from the tables.

    Click image for larger version. 

Name:	Tbl_Pt-Dept.jpg 
Views:	10 
Size:	94.6 KB 
ID:	25309

    Click image for larger version. 

Name:	Tbl_Stater_Leaver.jpg 
Views:	10 
Size:	83.7 KB 
ID:	25310

    There are PtSubDept (i.e. PT-271) are within PtDept (i.e. PT-27) which are within SeniorMgrDept (i.e.PT-2)

    The Combobox drop down is the 1st part I need to get right which I am not sure how to achieve. For the above pictured example the Combobox values should return PT-27 & PT-60.

    This is because PT-271 belongs to PT-27 etc. & PT-601 belongs to PT-60.
    I do not need reference the Tbl_PT_Dept Table at all if I can get the DD values by using LEFT(Tbl_Start_leaver.PtSubDept,5). i.e. PT-NN

    The problem is that the Tbl_Start_leaver.PtSubDept contains Id's that point to the text in Tbl_Pt_Dept (not the actual text i.e. PT-271).

    Once I have the Combobox working I am not sure how to filter the record to ONLY display records based upon the selected Combobox Value. Where would I put the code ?. So if I selected PT-27 from the combobox then I would expect only to see PT-27 & PT-271 in the bottom half datasheets view.

    Hope that makes better sense. Sometimes its hard to visualise.

  6. #6
    John_G is offline VIP
    Windows 7 32bit Access 2010 32bit
    Join Date
    Oct 2011
    Location
    Ottawa, ON (area)
    Posts
    2,615
    First for the combo box row source - that's easy: Select distinct PTDept from Tbl_Pt_Dept

    That will give you a list of all the distinct Departments. You don't need any other fields.

    The problem is that the Tbl_Start_leaver.PtSubDept contains Id's that point to the text in Tbl_Pt_Dept (not the actual text i.e. PT-271).
    That statement is a red flag. Is Tbl_Start_leaver.PtSubDept a lookup field? If it is, that's the problem, and it's one reason why lookup fields should never, ever be used - they hide what is really happening, and they make querying a major headache.

    That rant aside, you might be able to filter using the department ID, rather than the department name. It's easy enough to fix the combo box to use the Department ID.

    But I don't know if that works or not - I'll have to leave it someone who knows how to do that. I'd like to know that, too. Anyone?

  7. #7
    mond007's Avatar
    mond007 is offline Advanced Beginner
    Windows 7 Access 2007
    Join Date
    May 2010
    Posts
    52
    Hi thanks for your reply. (Major headache - yes indeed atm).

    Ok so I thought I had done the "not having IDs in the Tbl_Starter_Leaver.PTSubDept field)

    In fact I spent ages trying to create a combo field that does exactly that and not have a "lookup field" See.
    https://www.accessforums.net/showthr...973#post323973

    It was always intended not to be a lookup field and agree they hide what is really happening. I don't want to get into the realm of pursuing how to filter using ID's as this seems to be the incorrect way of doing things. I'd rather get this right from the start which makes it easy not harder.

    I will first try and get the combo fixed.

    Even when I get the combo fixed ... how can I go about filtering the records with the Tbl_Starter_Leaver.PTSubDept ?

    Thanks in advance.

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

Similar Threads

  1. Replies: 9
    Last Post: 02-24-2015, 11:19 AM
  2. Filter Not Working
    By data808 in forum Access
    Replies: 2
    Last Post: 09-24-2014, 12:20 AM
  3. Form filter not working
    By workuser in forum Forms
    Replies: 4
    Last Post: 02-04-2013, 02:48 AM
  4. Macro Filter not working....
    By avarusbrightfyre in forum Access
    Replies: 2
    Last Post: 06-18-2011, 04:09 PM
  5. Filter Not Working
    By BigCat in forum Reports
    Replies: 13
    Last Post: 06-06-2011, 12:48 PM

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