Solved

Change Data Inside Report At Runtime

Posted on 2001-07-11
20
223 Views
Last Modified: 2008-03-04
Hi,

I have a query that produces a table from which a report creating labels is run. Inside of this table some fields may be empty.
What I would like to be able to do is make an item on the report invisible for records that have a specific empty field.

I've tried using the Report_Activate Event but it only works for the first page of the labels report and not subsequent pages.

Can anyone help me out...quite urgent :-)

Thanks in advance,
David.
0
Comment
Question by:DSE
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 4
  • 2
  • +3
20 Comments
 
LVL 1

Expert Comment

by:underground712
ID: 6272711
Try your code on
double on the grey bar above your label, put your code on print event
0
 
LVL 1

Expert Comment

by:underground712
ID: 6272717
If you cant get to work, post your report code
0
 

Author Comment

by:DSE
ID: 6272816
This is the code that looks at the label contents:
**************************************************

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

If enddate <> "" Then
sbcode.Visible = False
msgbox "DATE ENTERED"
Else
BARCODE1.Visible = False
msgbox "DATE NOT ENTERED"
End If

End Sub

***************************************************

The message boxes display correctly dependent upon the contents of the date field but the visibility option seems to have no effect.

As a note: The fields I am attempting to change the visibilty of are BARCODES.

Thanks,
David.
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:DSE
ID: 6272833
As a test...I changed the code to hide both barcode fields if no date was held.

The message box told me it had reached the line of code that was supposed to hide the barcodes...but they still show.

Am I using the visibility option correctly?
0
 
LVL 8

Accepted Solution

by:
dovholuk earned 75 total points
ID: 6272925
have you tried to explicitly refer to the controls? not that it'll help, but you never can tell.... use the Me!BARCODE notation and maybe it'll make a difference. also, are the barcodes textboxes, labels, pictures, what???

oh one other thing, when you are testing the label, use the NZ() method. that way, you'll get null values as well. you might be having a null value issue.

try this:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
       If NZ(enddate) <> "" Then
              sbcode.Visible = False
              msgbox "DATE ENTERED"
       Else
              BARCODE1.Visible = False
              msgbox "DATE NOT ENTERED"
       End If
End Sub


btw, why do the two conditions have two differently named controls? (sbcode and BARCODE1)

dovholuk
0
 

Author Comment

by:DSE
ID: 6273015
I tried explicitly referring to the controls but it made no difference...worth a try though :-)

NZ didn't seem to effect it either because both barcodes are still being displayed.

The two controls are barcodes...one has more data then the other, it is necessary to one of these which means I must hide the barcode that is not relevant.

Appreciate your help,

David.
0
 

Author Comment

by:DSE
ID: 6273182
Another thing I noticed is that when using the Visibility method of a control it isn't listed in the controls methods but Access recognises this as being a keyword and does not moan about it.

This does make me wonder though as to whether or not the statement BARCODE1.Visibility is correct at all.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6273244
it's fine that the two controls are barcodes, what 'type' of control are they? (listboxes, textboxes, labels, bound images, etc.)

i don't know why, but the .visibility property isn't recognized in reports. i've noted that to myself a bunch of times. when i do a "test" report, i can set the visibility of textboxes to on/off without a problem. so it's definately correct syntax.

my other question dealt with the difference between barcode1 and sbcode. why are the two different? i would think that the two branches of your if statement would use the same control, either sbcode or barcode1... why is there a difference?

dovholuk
0
 

Author Comment

by:DSE
ID: 6273303
the barcodes are textboxes that a piece of software called ABarCode2000 places the barcodes into.

sorry...not sure I quite understand your question about the differenct controls.

They are both visible at design time with one sitting on top of the other...the code needs to be able to hide one of them leaving the other visible.

Not sure if that answers your question?

Thanks,
David.
0
 

Author Comment

by:DSE
ID: 6276233
Okay...I have discovered that the barcode software alters the textbox control somehow, hides it and leaves the barcode showing. Ultimately, I have no control over the visibility of the barcode.

What I can do however is move the barcode around the label using VBA.

How do I change the Access options to use pixels as opposed to centimetres...or how do I use cms in VBA?

Please help if you can.

Thanks,
David.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6276585
>How do I change the Access options to use pixels as opposed to centimetres...
>or how do I use cms in VBA?
not quite sure what you mean. but instead of setting the visible property to true, how about setting the width property to 0 (or 1 if 0 isn't allowed). is this the idea you're going for when you want to deal in centimeters?

access works with what they call twips. for some (insert deity) forsaken reason, 1 twip is = to 1/1440 inches. that's a little obsene if you ask me. but 1440 IS divisible by 2, 3, 4, 6, 12, 16, ... so maybe it made sense to some silly american.

here's an excerpt from A97 help file:
"twip

Unit of measurement used by Microsoft Access that is equal to 1/20 of a point, or 1/1440 of an inch. There are 567 twips in a centimeter."

the problem is that when you're using forms/reports you need to convert everything into twips from inches/centemeters.

hope this helps. if not, just let us know.

dovholuk
0
 

Author Comment

by:DSE
ID: 6276590
that might just work...will let you know! :-)

Cheers,
David
0
 

Author Comment

by:DSE
ID: 6276724
I tried it but no luck :-(
I worked out that 5.7cms is 3231.9 twips...?
So, based on that I did the following code:

***********************************
If (enddate) <> "" Then

sbcode.Width = 0
BARCODE1.Width = 3231.9
Else

BARCODE1.Width = 0
sbcode.Width = 3231.9

End If
***********************************

It seems that I can make the barcode increase in size once...then it refuses to change size again!

Thanks,
David.
0
 
LVL 8

Expert Comment

by:dovholuk
ID: 6276915
the problem you have is that tiwps are whole values. i don't believe that you can use the decimal point. round up to 3232 and try again...
0
 

Author Comment

by:DSE
ID: 6277415
Is it possible to use some kind of layers or frames such as those in VB to act as a container for the controls?
0
 
LVL 11

Expert Comment

by:joekendall
ID: 6510298
DSE:

Did you get it to work? If not, please post again as to were you are in the process.

Thanks!

Joe
0
 

Expert Comment

by:amp072397
ID: 6744764
DSE hasn't logged in since 23-December.

This is a free technical support site.

Experts who answer questions here receive no pay or other compensation except Expert points as a result answering questions and being awarded points.

As a result, and as a Moderator of this forum, my goal is to ensure that Experts and Askers alike no longer allow questions to get stale.

Please provide that feeback now. It's greatly appreciated!

If you need any help at all in cleaning up this or any other questions, don't hesitate to contact me via email. I will assist in any way that I can.

thanks
amp
community support moderator
amp@experts-exchange.com
0
 

Expert Comment

by:amp072397
ID: 6783636
DSE:

You have several open questions:

http://www.experts-exchange.com/jsp/qShow.jsp?qid=20245369
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20234443
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20149045
http://www.experts-exchange.com/jsp/qShow.jsp?qid=20144369

To assist you in your cleanup, I'm providing the following guidelines:

1.  Stay active in your questions and provide feedback whenever possible. Likewise, when feedback has not been provided by the experts, commenting again makes them receive an email notification, and they may provide you with further information. Experts have no other method of searching for questions in which they have commented, except manually.

2.  Award points by hitting the Accept Comment As Answer button located above and to the left of that expert's comment.

3.  When grading, be sure to read:
http://www.experts-exchange.com/jsp/cmtyQuestAnswer.jsp#3
to ensure that you understand the grading system here at EE. If you grade less than an A, you must explain why.

4.  Questions that were not helpful to you should be PAQ'd (stored in the database for their valuable content?even if not valuable to you) or deleted. To PAQ or delete a question, you must first post your intent in that question to make the experts aware. Then, if no experts object after three full days, you can post a zero-point question at community support to request deletion or PAQ. Please include the link(s) to the question(s).
CS:  http://www.experts-exchange.com/jsp/qList.jsp?ta=commspt
At that point, a moderator can refund your points and PAQ or delete the question for you. The delete button does not work.

5.  If you fail to respond to this cleanup request, I must report you to the Community Support Administrator for further action.

Our intent is to get the questions cleaned up, and not to embarrass or shame anyone. If you have any questions or need further assistance at all, feel free to ask me in this question or post a zero-point question at CS. We are very happy to help you in this task!


thanks!
amp
community support moderator

2/4
0
 
LVL 5

Expert Comment

by:Netminder
ID: 6958284
Per recommendation, force-accepted by
Netminder
CS Moderator
0

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

As tax season makes its return, so does the increase in cyber crime and tax refund phishing that comes with it
In Part II of this series, I will discuss how to identify all open instances of Excel and enumerate the workbooks, spreadsheets, and named ranges within each of those instances.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

719 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