How to test a Control on a Access Report

stevid
stevid used Ask the Experts™
on
i have an access form which has a checkbox that can be ticked or not labeled discount

If i click Discount on the form a discount box appears and allows the user to enter a discount value,

Now i have a report that displays the invoice but i only want the discount box to show on the report if dicount has been ticked in the form already,

How do i test if for this particular record discount has been ticked and subsequently make the discount box appear and change the font colour,

Any ideas??

Thanks,
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
When the form applies the discount, then there must be some indicator in the underlying table(s).  Those indicators (discount%, perhaps?) need to be passed to the report.  Probably the easiest way is to add a discount field to the form's record source.   Conditional formatting of the report based on a column in the Record Source is trivial.
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
Try something like this perhaps:

If me.DiscountChecboxControlField=True Then
   Me.txtDiscountAmount.visble=True
ElseIf me.DiscountChecboxControlField=False Then
   Me.txtDiscountAmount.visble=False
End if

<make the discount box appear and change the font colour,>
I'm confused, if the box is not visible, the font color is irrelevant...

Perhaps you should list the *exact* field names and control names of all the objects involved please?
(So we know if you are referring to the discount check "Box" or the Discount amount Text"Box")

In any event, you can select the control in design view, and click "Conditional" to conditionally format the font based on a condition.

JeffCoachman
Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
...Oh, ...my code would go on the Format event of the Detail Section.

Then the report must be opened in "Print Preview" specifically.
11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Author

Commented:
yes i have a simple tick box, discount

If you tick it the discount boxs appear and if its false they stay hidden

This tick box value is stored in the underlying tables, I have it passed to the report also,

I have been trying to use the onload event for the form to test the control and then usinf IF statements to say display or not but no good

Author

Commented:
I need this to also work if i print or just display the form

Author

Commented:
Tick Box  = [apply discount]
Text Box i want to show and hide = [txtTotalIncDis]

Author

Commented:
Here is the code i have,

If Me.Apply_Discount.Value = True Then
    Me.txtTotalIncDis.Visible = True
Else
    Me.txtTotalIncDis.Visible = False
End If


Cant get it to work though, I reckon its to do with what event is generating it
Commented:
As per Jeff, Use the OnFormat event of the detail section.

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
You'll have to be a bit more specific when you say:
"Cant get it to work though,"
;-)

...And try to avoid spaces in your object names.
The "_" that vba inserts is a generic character it uses for any character it does not like...

(See here for notes on standard naming conventions:
http://www.xoc.net/standards/rvbanc.asp
http://en.wikipedia.org/wiki/Leszynski_naming_convention)


So in the format event of the detail section, it would look something like this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

    If Me.chkApplyDiscount.Value = True Then
        Me.txtTotalIncDis.Visible = True
    Else
        Me.txtTotalIncDis.Visible = False
    End If

End sub


Finally, remember you *Must* Open the report in "Print Preview" explicitly, to see this work.


;-)


JeffCoachman

Author

Commented:
Any ideas how i can get the above to work when i open it in normal view or print preview???

Thanks,
MIS Liason
Most Valuable Expert 2012
Commented:
<Any ideas how i can get the above to work when i open it in normal view or print preview???>
OK, Sorry, I forgot that this was a Form.


This code should go on the Current Event of the form:

Private Sub Form_Current()
   
    If Me.chkApplyDiscount.Value = True Then
        Me.txtTotalIncDis.Visible = True
    Else
        Me.txtTotalIncDis.Visible = False
    End If

End Sub


JeffCoachman

Author

Commented:
No its a report alright,

I have it all working on the form part fine??

Author

Commented:
Sorry was not a question above,

I have the form set up and the Discount Box appear and dissapear as i want,

I now want this to work on the Report for the form,

Commented:
Can you post the database.

Author

Commented:
not really,

It contains a lot of confidentail info and is based on a back end also,

I could export the form and report in question but it is based on back end tables so not sure how well that would work??

Jeffrey CoachmanMIS Liason
Most Valuable Expert 2012

Commented:
<No its a report alright,>

Ok, this was confusing:
<How to test a Control on a Access Report>
then...
<i have an access form which...>

So again, this code should work on the Detail_Format event.

I'll back out now, I am sure dqmq can handle it from here...

Commented:
Sure, export the report, and if possible, copy the report's recordsource into a make table query and give us a few rows of actual data.   You can change names and places, etc. to protect the innocent.  

Author

Commented:
Ok have changed it all around and now just have a permanent special pricing box so everyone gets a special price :-)

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial