Solved

Change Data Inside Report At Runtime

Posted on 2001-07-11
20
217 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
Comment Utility
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
Comment Utility
If you cant get to work, post your report code
0
 

Author Comment

by:DSE
Comment Utility
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
 

Author Comment

by:DSE
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 

Author Comment

by:DSE
Comment Utility
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
Comment Utility
>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
Comment Utility
that might just work...will let you know! :-)

Cheers,
David
0
 

Author Comment

by:DSE
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Per recommendation, force-accepted by
Netminder
CS Moderator
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

743 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

18 Experts available now in Live!

Get 1:1 Help Now