Link to home
Start Free TrialLog in
Avatar of KJKAccess

asked on

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?

1;      S
2;      S
3;      S
4;      EMW
5;      S
6;      S
7;      S
8;      SEMW
9;      SEM
Avatar of Jeffrey Coachman
Jeffrey Coachman
Flag of United States of America image

...and also compare row 3 to row 2?

Please post a sample scenario of the exact results you are looking for
Does this have to be in a form, ...or will a report suffice?
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?

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.
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?

Avatar of KJKAccess


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.
1; S
2; S
3; S
4; EMW
5; S
6; S
7; S
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).


Again, 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...
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.
File referenced above.
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) actually Highlight exactly what you want, 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...


I am sorry.  I am not able to provide what you need to help.  Is anyone else able to help with my question?
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?

Hi Ron.  Yes, I do.  There is an autonumber field called "ID" that sets the order.
Avatar of IrogSinta
Flag of United States of America image

Link to home
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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.
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.
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.
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.

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 :(

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.
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 - I have tried to do exactly what you indicated and attached a test example.  Can you offer suggestions or corretions?
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.

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?
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.
Awesome!  Thank you again for hanging in there with me!!