Maddening
Aug 19 2010, 08:24 AM
Hi There,
I have been googling almost all day trying to find help for this issue.
I have created a report that when the button is clicked on my form it generates the report containing only that records information and does not show all my records. This works fine, however on my report I have a row of unbound text boxes which are to look like a table would on a word document. What is supposed to happen is, depending on my one fields result (Bound textbox), one of the unbound text boxes background color is supposed to change to grey.
I battled a bit to get it to change to grey but it does highlight it, however (again) it only highlights the first unbound text box as the first record contains the right word to trigger it to be highlighted. On my second record however when the report is generated from my form it does not highlight any and produces an error on the form where other information would have gone.
If I open the report as normal without loading it from the form then it highlights the first unbound text box for each record I have. It does not change them as required.
Below is my code that I have put in. I first used a Select Case but that gave the same result as these If statements. I changed it to the If statements as I thought there might be a problem.
Not sure what the problem is.
Private Sub Report_Load()
If BodyPart = "Head" Then *BodyPart is my bound textbox and Head is the word that must trigger the colour change in my first unbound text box (HeadOrNeck)
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Neck" Then
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Ear" Then
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Face" Then
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Mouth" Then
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Nose" Then
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Teeth" Then
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Eye" Then
Me.Eye.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Back" Then
Me.Trunk.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Buttocks" Then
Me.Trunk.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Stomach" Then
Me.Trunk.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Ribs" Then
Me.Trunk.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Chest" Then
Me.Trunk.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Groin" Then
Me.Trunk.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Finger" Then
Me.Finger.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Hand" Then
Me.Hand.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Wrist" Then
Me.Hand.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Arm" Then
Me.Arm.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Elbow" Then
Me.Arm.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Shoulder" Then
Me.Arm.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Ankle" Then
Me.Foot.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Foot" Then
Me.Foot.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Toe" Then
Me.Foot.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Hip" Then
Me.Leg.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Knee" Then
Me.Leg.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Leg" Then
Me.Leg.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Internal" Then
Me.Internal.BackColor = RGB(216, 216, 216)
ElseIf BodyPart = "Multiple" Then
Me.Multiple.BackColor = RGB(216, 216, 216)
End If
End Sub
I know its a lot to grasp but I have tried it with a select case and I have tried to run the code in Report_Load, Report_Detail On Format and Report_Detail On Paint.
Please help,
Thanks.
doctor9
Aug 19 2010, 08:52 AM
QUOTE (Maddening @ Aug 19 2010, 08:24 AM)

Below is my code that I have put in. I first used a Select Case but that gave the same result as these If statements. I changed it to the If statements as I thought there might be a problem.
Ack! No, put it back, put it back!!

No, really, the Select Case is much better suited for this. You shouldn't have any more Cases than you have textboxes to color.
QUOTE (Maddening @ Aug 19 2010, 08:24 AM)

Private Sub Report_Load()
I think this is your culprit. Move the code to the OnFormat event of the section where your textboxes appear - I'm guessing it would be the Detail section. That way the code fires for every record, not just the first record.
You may need to add some code to reset the background color of all the unbound textboxes before highlighting one of them, but I'm not certain of that without creating a test report on my end. Test your report before adding this, though.
Hope this helps,
Dennis
Maddening
Aug 20 2010, 03:46 AM
Ok here goes, my Select cases are below.
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Select Case Me.BodyPart *BodyPart is the bound text box
Case "Head" *Head is one of the possible answers that can be given in BodyPart
Me.HeadOrNeck.BackColor = RGB(216, 216, 216) *HeadOrNeck is the applicable UNBOUND textbox that needs to be highlighted in the report
Case "Neck"
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
Case "Ear"
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
Case "Face"
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
Case "Mouth"
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
Case "Nose"
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
Case "Teeth"
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
Case Else
Me.HeadOrNeck.BackColor = RGB(255, 255, 255)
End Select
Select Case Me.BodyPart
Case "Eye"
Me.Eye.BackColor = RGB(216, 216, 216)
Case Else
Me.Eye.BackColor = RGB(255, 255, 255)
End Select
Select Case Me.BodyPart
Case "Back"
Me.Trunk.BackColor = RGB(216, 216, 216)
Case "Buttocks"
Me.Trunk.BackColor = RGB(216, 216, 216)
Case "Stomach"
Me.Trunk.BackColor = RGB(216, 216, 216)
Case "Ribs"
Me.Trunk.BackColor = RGB(216, 216, 216)
Case "Chest"
Me.Trunk.BackColor = RGB(216, 216, 216)
Case "Groin"
Me.Trunk.BackColor = RGB(216, 216, 216)
Case Else
Me.Trunk.BackColor = RGB(255, 255, 255)
End Select
Select Case Me.BodyPart
Case "Finger"
Me.Finger.BackColor = RGB(216, 216, 216)
Case Else
Me.Finger.BackColor = RGB(255, 255, 255)
End Select
Select Case Me.BodyPart
Case "Hand"
Me.Hand.BackColor = RGB(216, 216, 216)
Case "Wrist"
Me.Hand.BackColor = RGB(216, 216, 216)
Case Else
Me.Hand.BackColor = RGB(255, 255, 255)
End Select
Select Case Me.BodyPart
Case "Arm"
Me.Arm.BackColor = RGB(216, 216, 216)
Case "Elbow"
Me.Arm.BackColor = RGB(216, 216, 216)
Case "Shoulder"
Me.Arm.BackColor = RGB(216, 216, 216)
Case Else
Me.Arm.BackColor = RGB(255, 255, 255)
End Select
Select Case Me.BodyPart
Case "Ankle"
Me.Foot.BackColor = RGB(216, 216, 216)
Case "Foot"
Me.Foot.BackColor = RGB(216, 216, 216)
Case "Toe"
Me.Foot.BackColor = RGB(216, 216, 216)
Case Else
Me.Foot.BackColor = RGB(255, 255, 255)
End Select
Select Case Me.BodyPart
Case "Hip"
Me.Leg.BackColor = RGB(216, 216, 216)
Case "Knee"
Me.Leg.BackColor = RGB(216, 216, 216)
Case "Leg"
Me.Leg.BackColor = RGB(216, 216, 216)
Case Else
Me.Leg.BackColor = RGB(255, 255, 255)
End Select
Select Case Me.BodyPart
Case "Internal"
Me.Internal.BackColor = RGB(216, 216, 216)
Case Else
Me.Internal.BackColor = RGB(255, 255, 255)
End Select
Select Case Me.BodyPart
Case "Multiple"
Me.Multiple.BackColor = RGB(216, 216, 216)
Case Else
Me.Multiple.BackColor = RGB(255, 255, 255)
End Select
End Sub
As you can see I have made seperate cases for each unbound text box however they all use the same bound textbox to obtain its answer....I used the on format one now and it still only hightlights the first record IF i run the report from the form. If I run the report just by opening it then it highlights nothing.
I tried the code in the On_Paint as well. (Found in the details section of the report) This highlights the correct unbound textboxes for each record however this is if I run the report by opening normally. If I run it from my form then it does not highlight either.
Help Please!!! LOL
I have attached an example picture of what the table looks like in my report. So depending on what variable is in bodypart, the relavant block must highlight as it would have had to be done manually in a word document.
Maddening
Aug 20 2010, 03:53 AM
I figured it out!! When I posted my select case in the previous post I noticed that my last Case Else statement had trunk in instead of multiple which I changed but I did not try the code again the On_Format event. I tried it now by opening my report from my form and it is working!
I must thank you though for you second comment you made on your reply about adding code to clear the backround. Previously I never added the Case Else statement to make it white again. I did now and it seems to have resolved the issue of keeping them all highlighted.
If I open my report normally without using the form it still hightlights nothing but I Will only be opening it using the form. So I must thank you for the advice. Thanks
niesz
Aug 20 2010, 07:15 AM
I think we can significantly reduce your code to something like:
CODE
'turn them all white
Me.HeadOrNeck.BackColor = vbWhite
Me.Eye.BackColor = vbWhite
Me.Trunk.BackColor = vbWhite
Me.Finger.BackColor = vbWhite
Me.Hand.BackColor = vbWhite
Me.Arm.BackColor = vbWhite
Me.Foot.BackColor = vbWhite
Me.Leg.BackColor = vbWhite
Me.Internal.BackColor = vbWhite
Me.Multiple.BackColor = vbWhite
Select Case Me.BodyPart
Case "Head", "Neck", "Ear", "Face", "Mouth", "Nose", "Teeth"
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
Case "Eye"
Me.Eye.BackColor = RGB(216, 216, 216)
Case "Back", "Buttocks", "Stomach", "Ribs", "Chest", "Groin"
Me.Trunk.BackColor = RGB(216, 216, 216)
Case "Finger"
Me.Finger.BackColor = RGB(216, 216, 216)
Case "Hand", "Wrist"
Me.Hand.BackColor = RGB(216, 216, 216)
Case "Arm", "Elbow", "Shoulder"
Me.Arm.BackColor = RGB(216, 216, 216)
Case "Ankle", "Foot", "Toe"
Me.Foot.BackColor = RGB(216, 216, 216)
Case "Hip", "Knee", "Leg"
Me.Leg.BackColor = RGB(216, 216, 216)
Case "Internal"
Me.Internal.BackColor = RGB(216, 216, 216)
Case "Multiple"
Me.Multiple.BackColor = RGB(216, 216, 216)
End Select
Maddening
Aug 20 2010, 07:38 AM
Thanks so much Walter, that simplifies it a lot.
niesz
Aug 20 2010, 07:56 AM
Maddening
Jan 19 2011, 03:01 AM
Oh my goodness why does access have to be so complicated.
When I created my code in the previous posts I created it as a beta version of the current database I am using. Sort of like a version 2.0 you could say.
It worked perfectly in the beta database but now that I have copied my reports and forms to my existing database it will not work. I have added all the required fields to my table and it should work but it does not colour anything. When I run my report from my form it adds all the field values where required but does not shade my text boxes.
Please help!!! Not sure why it is doing this.
Thanks,
Maddening!
doctor9
Jan 19 2011, 01:22 PM
QUOTE (Maddening @ Jan 19 2011, 02:01 AM)

When I run my report from my form it adds all the field values where required but does not shade my text boxes.
Maddening,
First, let's try the most obvious stuff...
Open your report in Design View, and select the Detail section. Does "[Event Procedure]" appear in the properties box next to the On Format property?
If so, view the VBA code for that event, and set up a Breakpoint in the code so when you open the report, the code will pause and highlight that line. Then, use F8 to step through the code to see if it's behaving as you think it ought to.
Hope this helps,
Dennis
Maddening
Jan 20 2011, 01:30 AM
Hi there,
My on format does show [Event Procedure]. I am not sure how the breakpoint thing works though. I can toggle a breakpoint in my code from the debug tab but how to use it, I dont know. When I open the report and press F8 nothing happens.
Thanks,
Maddening.
doctor9
Jan 20 2011, 10:15 AM
Maddening,
The help system in the visual basic editor can give you more detail on debugging code, but here are the basics:
Toggle the Breakpoint on one of the first lines of code (one that actually DOES something, not a "DIM" statement).
Open the report normally, as a user would.
At this point, you should be taken back to the visual basic editor, with the line of code highlighted.
Hit F8 to move the code forward one line at a time.
At any point, you can hover your cursor over variable names to see a pop-up displaying that variable's current value.
Hope this helps,
Dennis
Maddening
Jan 21 2011, 01:41 AM
Hi Dennis,
I toggled the Break point on Me.HeadOrNeck.BackColor = RGB(216, 216, 216) and it does nothing.
What happens is my report is opened from a button on a form. The report shows only the data that was open on the form at the time (only that record) and then opens the report in Print Preview.
I tried toggling on my case statements as well as the change statements (where the colour is supposed to change) but it does not go back to the VB Editor.
If I click on compile database in the debug tab it also does nothing. Shouldnt it atleast give you some message? I think the VB Editor is simply not reading my code at all and I dont know why.
Maddening
doctor9
Jan 21 2011, 10:00 AM
Maddening,
The good news is that you're learning to debug code, which is a very important step in the programming process.
QUOTE (Maddening @ Jan 21 2011, 12:41 AM)

I toggled the Break point on Me.HeadOrNeck.BackColor = RGB(216, 216, 216) and it does nothing.
That's not one of the first lines of code that Walter posted. Assuming that you've used his code without changing it, I'd put your breakpoint at:
'turn them all white
Me.HeadOrNeck.BackColor = vbWhiteThat way, when you use F8 to step through your code, you can see where the code jumps in the Select Case and why.
QUOTE (Maddening @ Jan 21 2011, 12:41 AM)

I tried toggling on my case statements as well as the change statements (where the colour is supposed to change) but it does not go back to the VB Editor.
That's probably because the code not getting to the line you've marked. It's probably skipping to a different case for some reason. That's why you need to set the breakpoint BEFORE the Select Case statement.
QUOTE (Maddening @ Jan 21 2011, 12:41 AM)

If I click on compile database in the debug tab it also does nothing. Shouldnt it atleast give you some message? I think the VB Editor is simply not reading my code at all and I dont know why.
When you click on Compile Database, it will only give you a message if it finds a syntax error of some sort. So getting no messages is a good thing.
Hope this helps,
Dennis
Maddening
Jan 24 2011, 03:09 AM
Hi Dennis,
Still no luck....Is it perhaps because my report opens in print preview? Or perhaps that I open the report using a button on my form?
Below is my code that I am using.
CODE
Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Me.HeadOrNeck.BackColor = vbWhite //Body Parts
Me.Eye.BackColor = vbWhite
Me.Trunk.BackColor = vbWhite
Me.Finger.BackColor = vbWhite
Me.Hand.BackColor = vbWhite
Me.Arm.BackColor = vbWhite
Me.Foot.BackColor = vbWhite
Me.Leg.BackColor = vbWhite
Me.Internal.BackColor = vbWhite
Me.Multiple.BackColor = vbWhite
Select Case Me.BodyPart
Case "Head", "Neck", "Ear", "Face", "Mouth", "Nose", "Teeth" //Body Parts
Me.HeadOrNeck.BackColor = RGB(216, 216, 216)
Case "Eye"
Me.Eye.BackColor = RGB(216, 216, 216)
Case "Back", "Buttocks", "Stomach", "Ribs", "Chest", "Groin", "Torso"
Me.Trunk.BackColor = RGB(216, 216, 216)
Case "Finger"
Me.Finger.BackColor = RGB(216, 216, 216)
Case "Hand", "Wrist"
Me.Hand.BackColor = RGB(216, 216, 216)
Case "Arm", "Elbow", "Shoulder"
Me.Arm.BackColor = RGB(216, 216, 216)
Case "Ankle", "Foot", "Toe"
Me.Foot.BackColor = RGB(216, 216, 216)
Case "Hip", "Knee", "Leg"
Me.Leg.BackColor = RGB(216, 216, 216)
Case "Internal"
Me.Internal.BackColor = RGB(216, 216, 216)
Case "Multiple"
Me.Multiple.BackColor = RGB(216, 216, 216)
End Select
Me.Sprain.BackColor = vbWhite //Type of Injury
Me.Wound.BackColor = vbWhite
Me.Fracture.BackColor = vbWhite
Me.Burn.BackColor = vbWhite
Me.Amputation.BackColor = vbWhite
Me.Shock.BackColor = vbWhite
Me.Asphyxiation.BackColor = vbWhite
Me.Unconsciousness.BackColor = vbWhite
Me.Poison.BackColor = vbWhite
Me.OccDisease.BackColor = vbWhite
Select Case Me.Type
Case "Sprain", "Strain" //Type of Injury
Me.Sprain.BackColor = RGB(216, 216, 216)
Case "Abrasion", "Bruise", "Laceration", "Soft Tissue", "Swelling"
Me.Wound.BackColor = RGB(216, 216, 216)
Case "Dislocation", "Fracture"
Me.Fracture.BackColor = RGB(216, 216, 216)
Case "Burn"
Me.Burn.BackColor = RGB(216, 216, 216)
Case "Amputation"
Me.Amputation.BackColor = RGB(216, 216, 216)
Case "Shock"
Me.Shock.BackColor = RGB(216, 216, 216)
Case "Asphyxiation"
Me.Asphyxiation.BackColor = RGB(216, 216, 216)
Case "Concussion", "Faint", "Avulsion"
Me.Unconsciousness.BackColor = RGB(216, 216, 216)
Case "Poison"
Me.Poison.BackColor = RGB(216, 216, 216)
Case "Asthma", "Epilepsy"
Me.OccDisease.BackColor = RGB(216, 216, 216)
End Select
Me.Days13.BackColor = vbWhite //Period of disablement
Me.Weeks4.BackColor = vbWhite
Me.Weeks16.BackColor = vbWhite
Me.Weeks52.BackColor = vbWhite
Me.Permanent.BackColor = vbWhite
Me.Fatal.BackColor = vbWhite
Select Case Me.POfDisablement //Period of disablement
Case "0 - 13 days"
Me.Days13.BackColor = RGB(216, 216, 216)
Case "2 - 4 weeks"
Me.Weeks4.BackColor = RGB(216, 216, 216)
Case "4 - 16 weeks"
Me.Weeks16.BackColor = RGB(216, 216, 216)
Case "16 - 52 weeks"
Me.Weeks52.BackColor = RGB(216, 216, 216)
Case "52 weeks or permanent"
Me.Permanent.BackColor = RGB(216, 216, 216)
Case "Fatal"
Me.Fatal.BackColor = RGB(216, 216, 216)
End Select
Me.CCYes.BackColor = vbWhite //Other
Me.CCNo.BackColor = vbWhite
Me.PYes.BackColor = vbWhite
Me.PNo.BackColor = vbWhite
If Me.ReportedTo = "Compensation Commissioner" Then
Me.CCYes.BackColor = RGB(216, 216, 216)
Else
Me.CCNo.BackColor = RGB(216, 216, 216)
End If
If Me.ReportedTo = "Police Service" Then
Me.PYes.BackColor = RGB(216, 216, 216)
Else
Me.PNo.BackColor = RGB(216, 216, 216)
End If
End Sub
doctor9
Jan 24 2011, 10:19 AM
Maddening,
Preview mode won't make a difference - I use it all the time when debugging report code.
"Still no luck" isn't telling me much about what's going wrong. When you use F8 to step through the code, where does it go? Is there a place in the first Select Case where you think it should change a BackColor value, but instead skips that section? If so, what is the value of Me.Bodypart at the start of the code, and what is the value in the Case statement that you think it's supposed to be matching? Are they REALLY a match? Or is there a subtle difference? That's what you should be focusing on.
Hope this helps,
Dennis
Maddening
Jan 25 2011, 01:16 AM
Hi Dennis,
It does not open the VB Editor at all, that is the problem. The break point is on the first Line of code. If I press F8 nothing happens either, I am presuming that is because the VB Editor does not open.
I dont think my database is reading my code at all by the looks of it. Somethings wrong.
doctor9
Jan 25 2011, 09:44 AM
Maddening,
The Detail section's Format event should fire every time you print the Detail section. I'm running out of ideas at this point.
Would it be possible to post the file so we can take a look at it? You'll probably want to remove/change any personal data to protect privacy. Don't forget that you need to ZIP the file before attaching it to a post.
Dennis
Maddening
Jan 26 2011, 02:16 AM
Hi Dennis,
When I copied my form, report and table to a new database to send to you and compiled it it worked perfectly....That means that the new form, report and table I have pasted in my existing database is conflicting somehow...Will have to see what I can find.
Thanks!
Maddening
Jan 26 2011, 05:39 AM
Ok now its not colouring it again. I have attached the mini database for you to look at. Hope you can figure it out!
SteveH2508
Jan 26 2011, 06:53 AM
You need to run the Report in Print Preview NOT Report View
Report View does not run code apparently - another nasty little Access 2007 quirk!
Maddening
Jan 26 2011, 07:00 AM
My reports do run in Print Preview. They open when the Generate SHEQ SD 17 button is clicked on my forms. The report used is determined on the complainant category chosen. If Staff is chosen then the SD 17 A report is opened. If the complainant category is any other the SD 17 B report is opened. When the button is clicked it should only show the details that have currently been worked on in the form and it should highlight the relevant blocks depending on which options were selected.
Thanks,
Maddening.
doctor9
Jan 27 2011, 09:47 AM
Maddening,
Steve seems to have a point. I didn't know you were in Access 2007 until you posted your database. There is a new feature that is not available in Access 2003 and earlier - Report View. I'm just getting started with the new features myself - my company is upgrading from 2003 to 2010 in the next couple months - and this does seem to be the root of the problem.
If you right-click the report and select Print Preview, the code runs and the boxes shade. If you double-click the report from the "All Access Objects" box, the report opens in Report View, which apparently does NOT run VBA code.
When I click the button on the form, the code runs and the boxes shade. If I double-click the report from the All Access Objects box, it opens in Report View, and the code does NOT run.
Hope this helps,
Dennis
Maddening
Jan 28 2011, 01:31 AM
Hi Dennis,
Did it shade the relevant boxes grey depending on what information was in the Type of Injury and Body Part fields??
When I open it in print preview it does not shade anything.
SteveH2508
Jan 28 2011, 02:52 AM
Yes it does do the shading - that is how I found the solution.
Is your database in a Trusted Location?
Maddening
Jan 28 2011, 03:11 AM
Hi Steve,
It wasnt in a trusted location. Have added it and its working now.
Was hoping that would be the reason why its not working in my existing database but no luck yet.
Thanks for the help.
Maddening
Jan 28 2011, 03:19 AM
Hi Steve,
I cannot believe I did not realize it was the trusted location problem. I had a minor problem with the record source but thats fixed now and it is shading in my existing database!!
Thanks very much to you and to Dennis for your patience and help!
Kind regards,
Maddening!!!
This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please
click here.