Results 1 to 8 of 8
  1. #1
    sitrav is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    4

    Conditional Formatting on Continuous Form

    Hello,



    Ive been scouring the internet looking for an answer to this issue, with no solution as of yet. they may be a Query questions as well, depending on the method used to solve it.

    I have an unbound Form [FrmSearch] that has a subform [SubFrmPossibleMatch]. FrmSearch uses a combo box to pull up information from a table [TblLidInfo], whos primary key is [LidID]. SubFrmPossibleMatch is a continuous form driven by a Query [QueryPossiblematch] that pulls numbers from the main form and displays results that fall with a range. SubFrmPossibleMatch is linked via [LidID] but the displayed results are from a table [tblCupInformation], whos primary key is [CupID]. There is a third table [TblVerified] whos primary key is [VerifiedID] and contains the two other primary keys: CupID and LidID.

    What I would like to do is have verified CupID's in the subform's entry change color if the combination of CupID and LidID is recorded.

    Ive been trying to get the VerifiedID to populate using QueryPossiblematch, but when I add the field, it will only populate the entries that are actually verified. I had hoped it would just place the VerifiedID in a textbox in the continuous form and then I could use conditional formatting to look for 'Not Null' values. unfortunately the query only returns the items that actually show up in the verified table.

    hopefully this isn't too confusing. Does any one have any suggestions on getting this to work?


    Thanks

  2. #2
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    Provide db for analysis. Follow instructions at bottom of my post. Only way I will be able to figure this out. I suspect query joins need to be changed.
    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
    sitrav is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    4
    I have attached the database as a zip file. it was saved in access 2007 format. If the form does not auto open on load, it is called "Search-LidGiven"

    other items of interest:

    Tables:
    verified
    SubFrmCupSearch-Search-LidGiven

    Queries:
    QueryCupSearch
    QueryReturnCupsVerfied

    Thanks!
    Attached Files Attached Files

  4. #4
    June7's Avatar
    June7 is offline VIP
    Windows XP Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,815
    The names cited in your first post don't equate with the names in the db. There is no form called 'FrmSearch, no query called 'QueryPossibleMatch'.

    A search form does open and it is bound.

    Verified table does not save the LidID, it saves the lid description into a text field. Verified table has records with CupManIDs that are not in UniqueCupInfo table.

    However, consider this query:

    SELECT CupInput.*, UniqueCupInfo.Manufacturer, UniqueCupInfo.Cup_ID, UniqueCupInfo.[Hot/Cold], UniqueCupInfo.[Material&Type], UniqueCupInfo.Volume, [Lid Data].Lid_Item, [Lid Data].[Nominal-Seal_ID]
    FROM (UniqueCupInfo LEFT JOIN CupInput ON UniqueCupInfo.CupMan_ID = CupInput.CupMan_ID) LEFT JOIN ([Lid Data] RIGHT JOIN Verified ON [Lid Data].Lid_Item = Verified.Lid_Item) ON UniqueCupInfo.CupMan_ID = Verified.CupManID;
    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
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I downloaded your DB and I've done some conditional formatting in the subform you mentioned without an issue. Are you looking to format an entire record or just certain items within the record when you do your conditional formatting? Can you tell me which items in the sample database are supposed to be highlighted?

    EDIT: After re-reading your post I agree with June, it appears you want to remove items from appearing in your POTENTIAL CUP FITS tab if they appear in your VERIFIED CUP FITS tab, if that's the case it's definitely an issue with your select query for the first tab. You currently have a cartesian query driving that subform but you can modify it just a little and get the results you want I believe.

    First Make this Query:
    Code:
    SELECT [Lid Data].*, UniqueCupInfo.*
    FROM [Lid Data], UniqueCupInfo;
    Call it CupLidCartesian


    Rename your existing QueryCupSearch to something else (so you have something to go back to if this fails to do what you want)

    Create a new QueryCupSearch with this SQL code:

    Code:
    SELECT CupInput.CupEntryNum, CupInput.CupMan_ID, CupLidCartesian.Manufacturer, CupLidCartesian.[Lid Data].Cup_ID, CupLidCartesian.[Hot/Cold], CupLidCartesian.[Material&Type], CupLidCartesian.Volume, CupInput.Date_of_Arrival, CupInput.[Spec_Nominal-Rim_Diameter], CupInput.[Measure_Average-Rim_Diameter], CupLidCartesian.[Nominal-Seal_ID], CupLidCartesian.Lid_Item, Verified.IDFROM (CupLidCartesian LEFT JOIN CupInput ON CupLidCartesian.CupMan_ID = CupInput.CupMan_ID) LEFT JOIN Verified ON (CupLidCartesian.CupMan_ID = Verified.CupManID) AND (CupLidCartesian.Lid_Item = Verified.Lid_Item)
    WHERE (((CupLidCartesian.[Hot/Cold]) Like IIf(IsNull([forms]![Search-LidGiven]![txtoptiontranslate]),"*","*" & [forms]![Search-LidGiven]![txtoptiontranslate] & "*")) AND ((CupInput.[Spec_Nominal-Rim_Diameter])>[Forms]![Search-lidGiven]![cup_search-min] And (CupInput.[Spec_Nominal-Rim_Diameter])<[Forms]![Search-lidGiven]![cup_search-max]) AND ((Verified.ID) Is Null)) OR (((CupLidCartesian.[Hot/Cold]) Like IIf(IsNull([forms]![Search-LidGiven]![txtoptiontranslate]),"*","*" & [forms]![Search-LidGiven]![txtoptiontranslate] & "*")) AND ((CupInput.[Measure_Average-Rim_Diameter])>[Forms]![Search-lidGiven]![cup_search-min] And (CupInput.[Measure_Average-Rim_Diameter])<[Forms]![Search-lidGiven]![cup_search-max]) AND ((Verified.ID) Is Null))
    ORDER BY CupLidCartesian.Manufacturer, CupLidCartesian.[Lid Data].Cup_ID;
    See if it does what you are looking for (anything that appears in the VERIFIED CUP FITS will not appear in the POTENTIAL CUP FITS

  6. #6
    sitrav is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    4
    Thank you for your replies.

    June7:

    I apologize for the confusion. I adjusted the names in the opening post in an effort to reduce confusion, since my naming structure gets pretty long. Also, the search form was bound (I should have said it used an unbound combo box).

    I applied your Query suggestions and the results show the 4 entries that fall in the verified table, so that is on the right track, but I would like to have all results populate, then Highlight the ones that are on table verified.

    rpeare:

    your initial understanding of the opening post is correct; i would like to use conditional formatting to highlight the entries of potential cups that are on the verified table.




    I'll try to explain what I would like using a specific example in the uploaded DB.

    Using the search-LidGiven from I use combo4 to select lid LW-P-OHLB.

    There are 2 tabbed subforms. Tab 'Potential cup fits' returns 8 results whose measurements fall within the search range. Tab 'Verified Cup Fits' returns 3 results of CupManID's that are on table 'verified' with Lid_Item 'LW-P-OHLB'

    Of the 8 results on Tab 'Potential cup fits' there are 4 entries which I would like to be highlighted green (to show they have been verified):

    Dopaco Cup1 (CupmanID:158)
    Dopaco Cup 4 (CupmanID:161)
    Dopaco Cup 5 (CupmanID:162)
    Dopaco Cup 5(CupmanID:162)


    Thanks Again.

  7. #7
    rpeare is offline VIP
    Windows XP Access 2003
    Join Date
    Jul 2011
    Posts
    5,442
    I'm not sure why you want information repeated on both tabs, I thought after looking at your layout that you wanted potential matches only on one tab and verified matches only on the second tab which the two queries I gave you would have done. However I'm enclosing an attachment that is your database modified so that the potential has all the 'verified' matches in it and are highlighted in green, you'll have to play with it to get the formatting you want but it works.

    upload Cup-Lid Matrix-Updated.zip

  8. #8
    sitrav is offline Novice
    Windows XP Access 2007
    Join Date
    Sep 2012
    Posts
    4
    Quote Originally Posted by rpeare View Post
    I'm not sure why you want information repeated on both tabs, I thought after looking at your layout that you wanted potential matches only on one tab and verified matches only on the second tab which the two queries I gave you would have done. However I'm enclosing an attachment that is your database modified so that the potential has all the 'verified' matches in it and are highlighted in green, you'll have to play with it to get the formatting you want but it works.

    upload Cup-Lid Matrix-Updated.zip
    That looks like it will do what I want it to! i will also keep a copy of your first suggestion if we (my boss) decides that is a better way to go

    thanks again Rpeare

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

Similar Threads

  1. Replies: 6
    Last Post: 09-27-2012, 08:27 AM
  2. Conditional formatting inside a form
    By DataRick in forum Programming
    Replies: 14
    Last Post: 02-12-2012, 06:23 PM
  3. Replies: 5
    Last Post: 05-02-2011, 11:02 AM
  4. Conditional formatting on form
    By ngruson in forum Forms
    Replies: 11
    Last Post: 09-17-2010, 12:15 PM
  5. Conditional Formatting (on Open form?)
    By christopheb in forum Forms
    Replies: 0
    Last Post: 03-16-2010, 07:07 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