Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!
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
ProductCode;AllergenCheck
1; S
2; S
3; S
4; EMW
5; S
6; S
7; S
8; SEMW
9; SEM
Do more with
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
...and also compare row 3 to row 2?
Please post a sample scenario of the exact results you are looking for
Please post a sample scenario of the exact results you are looking for
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
Ron
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.
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?
Ron
Ron
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!
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!
???
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...
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...
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.
Again,...
Please post a graphical representation of the "exact" output you are expecting,
...don't just say:
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
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
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?
Ron
Ron
In that case, add the following function to your form's module:
Here's what the attached sample db looks like:
Allergens.accdb
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
Here's what the attached sample db looks like:
Allergens.accdb
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.
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.
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 yetLet us know what problems you're encountering. We're here to help.
Ron
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![frmproductionschedu
ScreenShots.docx
The module tests without error after I change it from me to Forms![frmproductionschedu
ScreenShots.docx
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.
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
Ron
Ron - I have tried to do exactly what you indicated and attached a test example. Can you offer suggestions or corretions?
Test.accdb
Test.accdb
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
Ron
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?
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.
hnasr - The data base was a test. ProductCode can be entered into the form multiple times. I appreciate your comments.
Premium Content
You need an Expert Office subscription to comment.Start Free Trial