code for report

[russellito]

I am building a report, and I'm stuck. My access knowledge comes to an end with VBA, and I think thats what I need for this problem.

I'm trying to build a report that changes according to values in the source query. There are 11 yes/no fields, and, I want the report to enter specific text if the box checked. Some, all, or none of them may be true.

I guess i'm looking for the code I should use to do this. I basically need help from private sub to end sub. Let me know if you need specific information on my data.

thanks in advance.

 

[ByteMyzer]

Where do you wish to display the "specific text"? In a textbox? In a header? In the Report's Caption? Elaborate, please.

 

[russellito]

I want to put it into the detail somewhere. Whether its a text box, label, whatever is the best.

 

[GaryPanic]

this is pretty easy

put all of your yes/no fileds on the report (you can make them invisible if you like)

then go to the detail section of the report and right hand click - build event - code example :-

If Me.Level3 = True Then
Me.Basisofcover = "The Basis of cover is on Level 1 option - cover up to 10 hectacres"

so if level3 = true then the following text appears in a text box on the report

this is a simple version - and i have not finished this as I might need to do variables on my options but it should get you going in the right area

 

[GaryPanic]

oops missed a bit whee the details section is broken from the header its a grey bar right hand click on this bit .....

sometimes me.xxx does not work - switch it to me!xxx (not 100% sure why - but mess around and see )

 

[russellito]

I"m sorry, but that doesn't make a lot of sense to me. I can't tell what part of that is code and what isn't. I also don't know how to define the variables. Help there too?

 

[ByteMyzer]

You don't need VBA to accomplish this. Assuming that your report has a checkbox (Check1) and a TextBox (Text2), you can set your textbox's control source to something like:

=Iif(Me.Check1 = True, 'Checked', 'Not Checked')

 

[russellito]

Fantastic! That works great!
What I didn't use the Me. though. I used this
=IIf([Receipt Retainment]=True,'Receipt Retainment: An original, itemized receipt was not retained for the following transaction(s):','')
What i'd like to do now is bold just the heading of the text. Is there a way I can do that? maybe with the HTMLencode function?

 

[ByteMyzer]

Unfortunately, you can not apply boldface to only part of a textbox. However, you can use two separate text boxes, and have one of them boldface and the other one not.

 

[russellito]

won't this work?
=IIf([Receipt Retainment]=True,HtmlEncode('<b>Receipt Retainment:</b> An original, itemized receipt was not retained for the following transaction(s):'),'')

 

[russellito]

Nevermind, I tried it, well, I tried this =HtmlEncode('<b>this</b> is text') and it just brought up nonsense characters for the html tags... whats the HtmlEncode function for then?

 

[russellito]

Ok, forget all that if it can't be done. But can I set the Iif not true part of the expression to make the text box invisible? I end up with a big blank area if they are all false.

 

[ByteMyzer]

In the Report's Design, set the CanGrow and CanShrink properties to Yes for both the textbox(es) and the section containing the textbox(es), like the Detail section.

 

[russellito]

ya, all of the boxes and the detail section have can grow/shrink to yes. But there is still lots of white space. The detail section size is set to certain length, is that the problem? I even set auto height to yes. Still lots of space...

I know this has deviated from the code topic, but I still appreciate the help on this.

 

[Kryst51]

Something I tried once was an if then else statement that looked something like this (However, I am not positive how to put AND statements into vba):

'Here you would have all of your text boxes although you
'can probably do this with the checkboxes too with a similar concept.
IF Example.visible = False and Example2.Visible = False THEN

DetailSectionName.Visible = False ELSE
DetailSectionName.Visible = True
EndIf

End Sub

It worked for me, and I was fairly ecstatic, as I have always wante to suppress sections if there wasn't anything in them.

 

[russellito]

I guess I need you to walk me through it a little more. I did this, cause i just need the objects to be invisible not the whole detail section.

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

Dim ReceiptRetainment, UTSalesTax, BusinessPurpose, Review, Approval, Notes, SplitSale, ApproversReceiptReview, TravelStatus As Boolean
Dim ReceiptRetainment_txt, UTSalesTax_txt, BusinessPurpose_txt, Review_txt, Approval_txt, Notes_txt, SplitSale_txt, ApproversReceiptReview_txt, TravelStatus_txt As Object

If ReceiptRetainment = False Then ReceiptRetainment_txt.Visible = False Else ReceiptRetainment_txt.Visible = True
If UTSalesTax = False Then UTSalesTax_txt.Visible = False Else UTSalesTax_txt.Visible = True
If BusinessPurpose = False Then BusinessPurpose_txt.Visible = False Else BusinessPurpose_txt.Visible = True
If Review = False Then Review_txt.Visible = False Else Review_txt.Visible = True
If Approval = False Then Approval_txt.Visible = False Else Approval_txt.Visible = True
If Notes = False Then Notes_txt.Visible = False Else Notes_txt.Visible = True
If SplitSale = False Then SplitSale_txt.Visible = False Else SplitSale_txt.Visible = True
If ApproversReceiptReview = False Then ApproversReceiptReview_txt.Visible = False Else ApproversReceiptReview_txt.Visible = True
If TravelStatus = False Then TravelStatus_txt.Visible = False Else TravelStatus_txt.Visible = True

End Sub

But this doesn't work... It says "object required" in the debugger.

 

[ByteMyzer]

Remove the Dim statements.

 

[russellito]

Thanks. That works, it makes the boxes invisible. But Still, the detail section doesn't shrink.
There is still lots of white space. I changed the event timing to report load instead of detail format. didn't work the other way.

 

[Kryst51]

HI Russellito,

Very soon, I will learn not to assume too much when reading people's questions. My mind jumps ahead a lot. I apologize, my thought, with my reply to you, was that the detail section was empty (When the boxes were invisible), and that is why you wanted to shrink it.

 

[Kryst51]

Hey, I didn't want to ruin your new thread this time. LOL. But, I just had a thought. Can you set the section through code to a certain height using if then?

If text1.visible= false and text2.visible = false and textn.visible=false then
'I don't really know much about vba so don't know what the syntax would be
'or if it is possible
detailsectionname.height= 3" Else
detailsectionname.height= auto

or something like that.....