Avatar of KJKAccess
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?

ProductCode;AllergenCheck
1;      S
2;      S
3;      S
4;      EMW
5;      S
6;      S
7;      S
8;      SEMW
9;      SEM
Microsoft AccessDatabases

Avatar of undefined
Last Comment
KJKAccess

8/22/2022 - Mon
Jeffrey Coachman

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

Please post a sample scenario of the exact results you are looking for
Jeffrey Coachman

Does this have to be in a form, ...or will a report suffice?
IrogSinta

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
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
Scott McDaniel (EE MVE )

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

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
KJKAccess

ASKER
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!
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Jeffrey Coachman

???

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

ASKER
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.
KJKAccess

ASKER
File referenced above.
FormScreenShot.docx
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Jeffrey Coachman

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
KJKAccess

ASKER
I am sorry.  I am not able to provide what you need to help.  Is anyone else able to help with my question?
IrogSinta

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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
KJKAccess

ASKER
Hi Ron.  Yes, I do.  There is an autonumber field called "ID" that sets the order.
ASKER CERTIFIED SOLUTION
IrogSinta

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Hamed Nasr

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

ASKER
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.
Your help has saved me hundreds of hours of internet surfing.
fblack61
Hamed Nasr

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

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
KJKAccess

ASKER
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
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
Hamed Nasr

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

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
KJKAccess

ASKER
Ron - I have tried to do exactly what you indicated and attached a test example.  Can you offer suggestions or corretions?
Test.accdb
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
IrogSinta

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 Nasr

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

ASKER
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.
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
KJKAccess

ASKER
Awesome!  Thank you again for hanging in there with me!!