Solved

Change Data Inside Report At Runtime

Posted on 2001-07-11
20
220 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
  • 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
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 

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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Introduction When developing Access applications, often we need to know whether an object exists.  This article presents a quick and reliable routine to determine if an object exists without that object being opened. If you wanted to inspect/ite…
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.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

815 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now