Page 1 of 2 12 LastLast
Results 1 to 15 of 16
  1. #1
    shinobi is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    9

    Conditional Formatting or Message Box based on another table

    Hi All,



    my first post here. I am not sure if I am putting it in the right section.

    I have 4 tables:

    TableClients
    ID
    ClientName

    TableEmployees
    ID
    EmployeeName

    TableBlackListedEmployees
    ID
    EmployeeName
    ClientName

    TableVisits
    ID
    VisitDate
    ClientName
    EmployeeName

    TableVisits and TableBlacklistedEmployees are not linked yet. My question is, is it possible to link them so:

    when in TableVisits you enter Client and then Employee that is listed in TableBlacklistedEmployees for that Client, a message box comes up or a record is highlighted (conditional formatting).

    (Also, one employee may be blacklisted by more than one client)


    Any help would be much appreciated.

    Regards,
    shinobi

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    wouldn't it be better to not show employees blacklisted by that client in the first place?

    Also, tblBlacklisted and tblVisits should have clientID and EmployeeID fields rather than clientname and employeename

  3. #3
    shinobi is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    9
    Hi Ajax,

    in TableVisits both Client and Employee fields are lookup fields linking to tblClients and tblEmployees.

    If in TableVisits, based on the Client selected the Employee list can be restricted to only the ones that are not blacklisted that would be great. I am not sure how to restrict it though.

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    using lookup fields in tables is a really bad idea - ok for diddly little db's that don't do much but otherwise should be avoided. Just look at what you have now - a field called employeename which implies text, but is actually a number. Tale a look at these links

    http://access.mvps.org/access/lookupfields.htm
    https://bytes.com/topic/access/answe...-lookup-fields

    better to change you field back to a number type and change the name to EmployeeID - then you know what you are working with.

    Also consider giving your fields relevant names - ID on its own is meaningless, make it ClientID, EmployeeID to be clear and unambiguous.

    So for your table visits assuming clients and employees are selected through comboboxes

    combo for clients we will call cboClients and will have a rowsource of

    SELECT ID, ClientName
    FROM tableClients
    ORDER BY ClientName

    and for employees a combo called cboEmployees, the rowsource to the combobox would be

    SELECT tableEmployees.ID, tableEmployees.EmployeeName
    FROM tableEmployees LEFT JOIN tableBlackListedEmployees ON tableEmployees.ID = tableBlackListedEmployees.EmployeeName
    WHERE tableBlackListedEmployees.EmployeeName is null or tableBlackListedEmployees.ClientName<>[cboClients]

    both combos should have the following property values
    bound column - 1
    column count - 2
    column widths - 0

    And in the cboEmployees on enter event put

    cboEmployees.requery

  5. #5
    shinobi is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    9
    I presume this can only be achieved by using comboboxes on a form.

    What if the form type has to be datasheet (the fastest way of inputting the data due to high volume) and in most cases only VisitDate and ClientID fields are populated first and the EmployeeID field is populated at a later stage, after contacting available employees and confirming it with them (meaning that there may be a list of VisitDates and ClientIDs and an empty EmployeeID field for a number of records, with the EmployeeID being populated later on).

    I presume there is no way of running this query directly on a table field.

    Thanks for all the useful info. I will definitely have a look at the comboboxes option, even just for learning purposes.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you can use combos on datasheets.

    with regards tables - no. other than test data all input should be via forms or imports. And if you want a list - all you need to do is change the combo to a listbox (right click on the control, select Change To then select listbox - obviously resize the control so you can see more than one row.

    With regards contacting employees to check availability - if you are recording their existing dates, the rowsouce can be modified further to exclude those that already have a visit planned for that day.

  7. #7
    shinobi is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    9
    I have come across a slight problem with the JOIN statement.

    Some employees can be blacklisted by more that one client and the JOIN only seems to work for employees who are blacklisted by one.

    (In the table below, there could be one EmployeeID that could have several ClientIDs)

    TableBlackListedEmployees
    BlacklistedID
    EmployeeID
    ClientID

    I have been trying to amend the SQL statement but cannot get this to work. Any suggestions?

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    not without seeing your current sql statement

  9. #9
    shinobi is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    9
    As per the suggestion, I have changed the tables a bit:

    tblClients
    ClientID
    ClientName


    tblEmployees
    EmployeeID
    EmployeeName


    tblBlacklistedEmployees
    BlacklistedID
    EmployeeID
    ClientID


    tblVisits
    VisitID
    ClientID
    EmployeeID


    the row source for the second combo box:


    SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName
    FROM tblEmployees LEFT JOIN tblBlacklistedEmployees ON tblEmployees.EmployeeID = tblBlacklistedEmployees.EmployeeID
    WHERE tblBlacklistedEmployees.EmployeeID is NULL OR tblBlacklistedEmployees.ClientID<>[cboClients];


    This one only works when in cases where an employee is only blacklisted by one client.


    The statement would have to be expanded for instances such as:

    tblBlacklistedEmployees
    BlacklistedID EmployeeID ClientID
    1-------------------1-----------1
    2-------------------1-----------2
    3-------------------1-----------3

    4-------------------2-----------1
    5-------------------3-----------1



    I have attempted adding another condition to the WHERE clause, but this is not working either:


    WHERE tblBlacklistedEmployees.EmployeeID is NULL AND tblBlacklistedEmployees.ClientID is null OR tblBlacklistedEmployees.ClientID<>[cboClients]

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    hmm, can see a couple of issues with my original thoughts on the query - an employee blacklisted by 3 clients would still appear twice for the other two clients, so a change to the query

    SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName
    FROM tblEmployees LEFT JOIN (SELECT EmployeeID FROM tblBlacklistedEmployees WHERE ClientID=[cboClients]) As BL ON tblEmployees.EmployeeID = tBL.EmployeeID

  11. #11
    shinobi is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    9
    I am getting 'Syntax error in JOIN operation'.

    What does the BL stand for in the statement? Can't find anything in google about it.

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    What does the BL stand for in the statement?
    it's an alias for the subquery - just stands for BlackList

    just noticed a typo in my post -

    tBL.EmployeeID should be BL.EmployeeID

  13. #13
    shinobi is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    9
    amending the typo fixed the syntax error but the statement does not seem to filter the blacklisted employees out, regardless of whether they are blacklisted by one or by more employers.

  14. #14
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry - missed a line as well (not my day)

    Code:
    SELECT tblEmployees.EmployeeID, tblEmployees.EmployeeName
     FROM tblEmployees LEFT JOIN (SELECT EmployeeID FROM tblBlacklistedEmployees WHERE  ClientID=[cboClients]) As BL ON tblEmployees.EmployeeID = BL.EmployeeID
    WHERE BL.EmployeeID is null

  15. #15
    shinobi is offline Novice
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2016
    Posts
    9
    Great. It is working now! Amazing!

    Just a quick question about the datasheet. There seems to be some kind of glitch with some of the records under cboEmployees disappearing on the form. They are saved in the linked table but for some reason when a new record is being added some of the previous records may be not be visible.

    Click image for larger version. 

Name:	Screen1.png 
Views:	9 
Size:	29.1 KB 
ID:	25929

    They are back being visible when you click into the field in question.

    Click image for larger version. 

Name:	Screen2.png 
Views:	9 
Size:	28.5 KB 
ID:	25930

    Any idea what may be causing it?

    If this helps:
    Employee 1 is blacklisted by Client 1 and 2
    Employee 2 is blacklisted by Client 1
    Employee 3 is not blacklisted

Page 1 of 2 12 LastLast
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Conditional Formatting based on result
    By Skyace00 in forum Reports
    Replies: 1
    Last Post: 01-13-2016, 05:19 PM
  2. Conditional Formatting Based On Another Column
    By Rustin788 in forum Reports
    Replies: 3
    Last Post: 08-13-2014, 01:52 PM
  3. Conditional Formatting based on another field - Part II
    By Harley Guy in forum Programming
    Replies: 8
    Last Post: 10-17-2013, 12:46 PM
  4. Replies: 5
    Last Post: 10-15-2013, 07:49 AM
  5. Replies: 2
    Last Post: 09-08-2011, 01:10 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