Highlight order issues within a form

I have created a form (frmProductionSchedule) that contains a combo box (ProductCode).  Based on the entry of that combo box and AllergenCheck field is populated.  This field contains combinations of the letters S, W, M and E (representing various allergens).  I would like record two's AllergenCheck results to be compared to record one's results.  If record two contains all the same letters as record one (and potentially more letters), than no highlighting, but if record two is missing a letter that is contained in record one than I would like it highlighted.  The record in question would always be compared to the one directly above it.  In the scenario below, I would like AllergenCheck field for ProductCode 4 and 9 to be highlighted.  Can you help?

ProductCode;AllergenCheck
1;      S
2;      S
3;      S
4;      EMW
5;      S
6;      S
7;      S
8;      SEMW
9;      SEM
KJKAccessAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
...and also compare row 3 to row 2?

Please post a sample scenario of the exact results you are looking for
Jeffrey CoachmanMIS LiasonCommented:
Does this have to be in a form, ...or will a report suffice?
IrogSintaCommented:
I'm a bit confused with your request.  What is the purpose of the Combo box?  Does making a selection here add your selection as the next record in a table?  Also, how are you viewing the different records?  Do you show them all in a Continuous Form, a Listbox, a Report?

Ron
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

Get Your eBook
Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
And why would row 8 and row 5 not be highlighted?

Row 5 contains data entirely different from row 4

Row 8 contains the same element as row 7 (S), with three more added.

As others have said, you really need to firm up your requirements before we can help. If you can specifically define when a row is considered "different" from the one above, and also define how you are sorting those records, we might be able to come up with a plan.
IrogSintaCommented:
So you only want to highlight records that are missing an allergen that existed in the previous record; that's why only rows 4 and 9 get highlighted.  Am I right about this?

Ron
KJKAccessAuthor Commented:
I clearly did a very poor job of defining the situation.  I apologize.  I will try to do a better job the second time around and answer your questions.  Forget the discussion of a combo box and the autofill of a field based on that combo box entry…that part works…I was just trying to give a depiction of the form.  

I have a text field called AllergenCheck.  Within this field, I would like to compare record 2 to record 1, record 3 to record 2, record 4 to record 3 and so on.  Record 2 must contain whatever record 1 contains, but it is allowed to contain more than what record 1 contains.  If this criteria is met, record 2 would NOT be highlighted, but if it is NOT met, record 2 WOULD BE highlighted.
ProductCode;AllergenCheck
1; S
2; S
3; S
4; EMW
5; S
6; S
7; S
8; SEMW
9; SEM    
It is necessary that this is in a Form.  It is a continuous form.
It is true that round 5 should be highlighted in addition to rows 4 and 9.  Row 8 would NOT be highlighted because it does contain Letter S like row 7 (and is allowed to contain more letters).

Thanks!
Jeffrey CoachmanMIS LiasonCommented:
???

Again, ...it might be simpler if you posted a graphical representation of the "exact" output you are expecting, ...based on your sample data...
...and/or post a scaled down sample of this database...
KJKAccessAuthor Commented:
I have attached a screen shot of the form.  I enter the ProductCode and Batches.  AllergenCheck populates based on the ProductCode entry.  I am looking to highlight the AllergenCheck field when the record does not contain at least all the allergens in the prior record.
KJKAccessAuthor Commented:
File referenced above.
FormScreenShot.docx
Jeffrey CoachmanMIS LiasonCommented:
Again,...
Please post a graphical representation of the "exact" output you are expecting,
...don't just say:
I am looking to highlight the AllergenCheck field when the record does not contain at least all the allergens in the prior record.
...Because it is still not clear what this means...

Please take you screenshot and use an image editing program (MsPaint.exe, ...for example)
...to actually Highlight exactly what you want, ...in exactly the way you want.

At that point, an expert may immediately see what it is you are after, ..and post a solution.

If not, we may post questions for further clarification based on your mark-up...

Thanks

JeffCoachman
KJKAccessAuthor Commented:
I am sorry.  I am not able to provide what you need to help.  Is anyone else able to help with my question?
IrogSintaCommented:
I don't see what sets the order in the screenshot you posted.  How are you ordering these records?  Obviously it's not by ProductCode.  Do you have another field that's not being shown that sets the order?  An Autonumber field perhaps?

Ron
KJKAccessAuthor Commented:
Hi Ron.  Yes, I do.  There is an autonumber field called "ID" that sets the order.
IrogSintaCommented:
In that case, add the following function to your form's module:
Public Function DiffAllergen(ID) As Boolean
    Dim newAllergenCheck As String
    Dim prevAllergenCheck As String
    Dim Allergen As String
    Dim i As Integer
    
    With Me.RecordsetClone
        .FindFirst "[ID]=" & ID
        
        newAllergenCheck = !AllergenCheck
        .MovePrevious
        
        If Not .BOF Then
            prevAllergenCheck = !AllergenCheck
            
            For i = 1 To Len(prevAllergenCheck)
                Allergen = Mid(prevAllergenCheck, i, 1)
                If InStr(newAllergenCheck, Allergen) = 0 Then
                    DiffAllergen = True
                    Exit Function
                End If
            Next
        End If
    End With

End Function

Open in new window

Then you can use Conditional Formatting on your fields.  Set your condition to "Expression Is" and your expression to "DiffAllergen([ID])=True" (without the quotes).  

Here's what the attached sample db looks like:imgAllergens.accdb

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Hamed NasrRetired IT ProfessionalCommented:
Hello KJKAccess,

If IrogSinta's comment reflects the understanding of your issue, then he deserves a credit for that.

If still not answered, then you can youse IrogSinta's uploaded database to clarify what JeffCoachman asked for.
KJKAccessAuthor Commented:
I appreciate IrogSinta's post and intend to give him credit.  I have not been able to get it to work quite yet (I am sure my fault and not his), but wanted to leave the conversation open incase I am not able to figure it out without further clarification.  If that is not how the site works, I appologize.  I really am trying to do this correctly.
Hamed NasrRetired IT ProfessionalCommented:
Try to add more test records to the uploaded database. Imagine the output, or sketch it on paper. Run the form and if displays what you want then it is done.

If you upload the database with needed extra records and upload a graphic of expected result, experts can help you in getting the expected results.
IrogSintaCommented:
I have not been able to get it to work quite yet
Let us know what problems you're encountering.  We're here to help.

Ron
KJKAccessAuthor Commented:
Honestly feeling a bit stupid at this stage of the game.  I hate to have to ask further because I figure I should be able to take what you provided and run with it, but I am failing miserably.  I tried putting the code in a new module and calling it via a macro through an event associated with the overall form.  I tried "on data change" and have actually tried a few others at this point..."After update", "On lost focus"...  I also tried putting the code directly in the event code, but don't know how to incorporate the two if that is the correct way to do it.  

The module tests without error after I change it from me to Forms![frmproductionschedule].  I am probably having to change this because I do not have it associated with the form module like you indicated (because I am not sure how to fit it in there), but rather a "generic" one.  When I run the macro, I get an error regarding "ID"...again, I assume this is because it is not associated with the form yet.  When I associate it with the event, add the conditional formatting and make a change, nothing happens at all.  No error, highlighting or otherwise.  I have tried to provide screen shots assoicated with what I am stating in the attachment.  I figure all of this is because I am not putting the code in the right place to start with or not tweaking something necessary to better fit my data/field names.  The last screenshot is of the module associated with the form.  Can you help me understand how to incorporate and call out a public function on an event cue within the form?  So sorry :(
ScreenShots.docx
Hamed NasrRetired IT ProfessionalCommented:
KJKAccess,

Do the results you get from running IrogSinta's demo database, agree with your expected output?

If yes, then fine, otherwise, then we need an answer to our requests of the expected results. How do get the results, assuming doing the job manually.

The issue may be solved using a different approach, and that depends on our understanding of the issue.
IrogSintaCommented:
Why are you putting this in a separate stand alone module rather than the form's module itself?  Do you intend to use this function for more than one form?  If not, then just stick it in the form's module.  It does not go in any event.  It's a stand alone function so just copy and paste it into your module.  Let me know what happens when you do that.

Ron
KJKAccessAuthor Commented:
Ron - I have tried to do exactly what you indicated and attached a test example.  Can you offer suggestions or corretions?
Test.accdb
IrogSintaCommented:
Your test database works fine on my computer.  Do you get a security warning when you open the db about "some active content has been disabled?"   If so, be sure to Enable it.

Ron
Hamed NasrRetired IT ProfessionalCommented:
I think you need to have a close look at your database design.
Calculated values are in table ALLERGY2 (Violating normalization).
The table should contain only 4 entries for M, E, S, and W.

Any reason why ProductCode is repeated twice10042?
KJKAccessAuthor Commented:
Thank you Ron!  It does seem that I needed to enable it.  I truly appreciate all your help.  I realize my lack of understanding makes it a challenge to help, but really appreciate you hanging in there with me!!

hnasr - The data base was a test.  ProductCode can be entered into the form multiple times.  I appreciate your comments.
KJKAccessAuthor Commented:
Awesome!  Thank you again for hanging in there with me!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.