?
Solved

MS Access 97 Conditional Formatting

Posted on 2004-11-29
11
Medium Priority
?
236 Views
Last Modified: 2008-02-26
MS Access 97 does not support conditional formatting feature, so I tried making my own for reports.
The onLoad event does not help as the fields are not populated at that time. But onActivate function solves the problem. e.g. I can change the backColor or a txtBox based on its value using this event. Works fine!!

But prob arises in the following case: Parent Report A has a section subReport B. in the onActivate function of B I have conditional formatting code. If I open A (which indirectly opens B within itself) the onActivate function is not called. Hence not performing the conditional formatting.

Is there an alternative to this? i.e. Either calling onActivate function or using some other way to perform the same task?

0
Comment
Question by:ulatif
  • 5
  • 5
11 Comments
 
LVL 36

Expert Comment

by:SidFishes
ID: 12697250
rather than recreate something...youi might want to have a look here...

http://www.lebans.com/formatbycriteria.htm

0
 
LVL 58

Expert Comment

by:harfang
ID: 12698045
The event to use for dynamic formatting is <section name>_Format. You will need one event for each section, but it will be called for each record.

Good Luck :)
0
 
LVL 58

Expert Comment

by:harfang
ID: 12698151
BTW: thanks for the link, SidFishes, I never thought of *that* trick (create background bitmap on the fly). It's cool. But it's not really about conditional formatting, is it?
Cheers :)
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 

Author Comment

by:ulatif
ID: 12698269
harfang - can you give me an example - i did not quite understand what you are suggesting:

this is what i have in the function B_onActivate()
' say parent report is "A"
' subReport is "B"
' B has a field txtFieldName
' where do you want me to add txtFieldName_Format???

select case txtFieldName
Case 1:
 Me.txtFieldName.BackColor = 255
Case 2:
 Me.txtFieldName.BackColor= 125
End select



0
 
LVL 58

Accepted Solution

by:
harfang earned 400 total points
ID: 12699932
Open the report in design view, select the section containing txtFieldName and find the On Format event. Select [event procedure] and click the builder (like this: [...]). You now have an event handler for the format event of the section, something like:

Private Sub <section name>_Format( <params> )

End Sub

Place your code between those lines and you are rolling. Ah, yes, use a "Case Else" to reset the back color to the default, just in case :)
BTW: there is no txtFieldName_Format event. This exists only for sections, not controls...

cheers :)
0
 

Author Comment

by:ulatif
ID: 12704093
Strange:
i've tried doing what you said:

open report in design view. Right click on txtFieldName, select properties. Click on Event Tab...
BUT... its empty...
theres no even a single event option!!!!

where do you expect me to find this on Format event anyways!
0
 

Author Comment

by:ulatif
ID: 12704096
as i said at the very start - its Access97 (just in case you wanna know)
0
 

Author Comment

by:ulatif
ID: 12704379
ok - ive found the solution

instead of using an onActivate in the subreport, you can reference to subreport control directly from parent report

http://support.microsoft.com/kb/q113352/

Sub ParentReport_Activate()
 'Me is the parent report here
Select Case Me.subReportName.Report![ControlName]
Case 1
Me.subReportName.Report![ControlName].BackColor = blue
Case 2
Me.subReportName.Report![ControlName].BackColor = Green
End Select

End Sub
0
 
LVL 58

Expert Comment

by:harfang
ID: 12706232
You haven't been reading properly... :)

I did say that there are no events for text boxes, only for sections. Furthermore, the event you want is not Activate (the window gets the focus) but Format (the section is formatted).

When *formatting* (not activating) a *section* (not a control), you can format all the controls in that section, including subreport controls and controls within the subreport control. As you discovered, you can access subreport controls. But: you code runs once, not for every record in the subreport, so it is not *conditional formatting*...

Try it again: open the report in design view, select the SECTION containing the control, look for the FORMAT event.

Good luck :)
0
 

Author Comment

by:ulatif
ID: 12706746
Thanks
all the time i got confused by *section*
i thought it mean the area defined for subreport :(
0
 
LVL 58

Expert Comment

by:harfang
ID: 12706810
No problem, I probably wasn't too clear. These things are easier face-to-face... :)
BTW: I would call the "area defined for subreport" the "subreport control", which can of course fill an entire section...

Good luck not, and tell me how it goes.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Explore the ways to Unlock VBA Project Password Excel 2010 & 2013 documents. Go through the article and perform the steps carefully to remove VBA Excel .xls file.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

588 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question