Help with Text Boxes

[bissonette]

I need to make a text box dependent on a field in a report. For example I use the "Can Grow" and "Can Shrink Options" for fields that might not have data in them for a particule record. I need to label the field and make the label only appear if there is data in the field. I also need the text box to display a label name and not the contents of the field.

1)Name: Account: Balance: Comment:
xxxxxxx xxxxx xxxxx xxxxxxx

2)Name: Account: Balance:
xxxxxxx xxxxx xxxxx

3)Name: Account: Balance: Comment:
xxxxxxx xxxxx xxxxx xxxxxxx

4)Name: Account: Balance: Comment:
xxxxxxx xxxxx xxxxx xxxxxxx

Record 2 is a graphical example of what i want the report to look like. In the real report there are 20 different comments so that is why i need to make this work.


thanks....
Mike

 

[WayneRyan]

Mike,

You can use the Report's DetailPrint & DetailFormat events to execute
some code.

If "SomeCondition" Then
   Me.SomeTextBox.Caption = "SomeCaption"
   Me.SomeControl.Visible = False
   Me.SomeTextBox = "SomeValue"
End If

Just some general ideas,
Wayne

 

[KenHigg]

Looks like you have a label for each records, so...

For the underlying query, have a column called commentLabel. As the value , do an iiF() where if the field has a value do "Comment", else "".

Make sense?
ken

 

[bissonette]

Sorry guys unfortunately i have no idea what you are talking about. I am a newbie at this and dont know Visual Basic to well or at all for that matter. Can you explain it step by step if its not too much trouble.

Thanks For Helping

 

[KenHigg]

To try to keep this from getting out of hand, may I ask you read Access Help on IIF(). Otherwise known a the 'Immediate If' function. When you get the jist of that, repost and I'll try to show you how to use it in a query...

???
ken

 

[WayneRyan]

Bisonette,

Hey! the weekend is upon us. If you can post a sample DB, along with what
you would like it to do, maybe we can make something work.

Wayne

 

[kupe]

That was really very generous of you, WayneRyan. We should have a star service for outstanding performers. Thanks on behalf of bissonette.

 

[bissonette]

Hey Wayne.....
I missed your last message. And We're back at Monday. I am posting from work and dont usually check this site on the weekend. But I would love to take you up on your offer. How do I post a sample DB on here? I could email to you if that would make it eaiser. thanks again.

 

[WayneRyan]

Hi b,

Remove anything not related to problem,
Tools --> Database Utilities --> Compact/Repair,
Then zip,
Then attach.

Wayne

 

[bissonette]

Ok Wayne.....

Here we go. On the Missing Techs Report I want Only the Items with and "x" next to them to appear on the report. I have other reports that this type of thing needs to apply to but once i get it on one report i think i'll be able to make the solution apply to the others. Thanks

Mike
If it is easier for you to email my address is
mbissonette5@aol.com

 

[bissonette]

Ohhhh....
P.S.
I also left in a module that mite be a solution... I got it off another forum but unfortunately i cannot make it work...

 

[WayneRyan]

b,

YOU ARE IN SO MUCH TROUBLE!!!

The solution was to use the Criteria = "x" for each of your fields.
Since it is an OR condition they will "staircase" as you look at them
in the query grid. The problem is that you can't have as many OR
conditions as you have fields.

The SHORT TERM WORKAROUND is to use VBA to check whether or not a record
should be printed. I added a function call to the end of your query.
That function is in Module1 and it just returns a True/False for whether
or not to print the record.

(This is the part where you're in trouble). THE REAL ANSWER is to read
the threads on normalization here. All those "x" fields shouldn't be in
your table. They should be in another table. That would make this
effort a lot easier. AND allow you to easily add more "x" fields as things
change.

I can't do much more (I'm at lunch now). I did make it so that record
#12 had no "x" fields (it doesn't print).

Hope to see your post in the Tables forum. It won't be that difficult
to evolve your DB. You won't even lose any data in the process.

See ya,
Wayne

 

[bissonette]

Ahhhh what do i do now?

I popped the code into the report and nothing different happens? What do you mean about a different table. The techs need to apply to each record?

 

[WayneRyan]

b,

The changes made were:

Your query now has a new True/False function call that looks up each
record individually. It had to do this, because with so many of the "x"
fields, the QBE grid couldn't support it.

The deficiency with the query was that; if the fields were "ANDed", that
is, all were checked, the Criteria would all be in a HORIZONTAL line. OK.

In your case, with an OR condition, the Criteria for each field would have
to be in a seperate line (Staircase). You can't do that, there are too
many.

The short-term solution is to call a function (which used VBA) to evaluate
each record. VBA doesn't have the constraints of the query grid.

The real solution is to break the "x" fields into a seperate table. Then
this is a trivial problem.

Wayne

 

[WayneRyan]

b,

You have to delete your query, and replace it with the one in my last download.

You have to delete "Module1" and replace it with the "Module1" in my last download.

Wayne

 

[bissonette]

What do you mean buy putting the "x" fields in their own table? The "x" is just and indication that a certian type of statement is missing from a "loan file".

What I am doing is using the database to write loan reviews and then make financial statements from the reviews. To break it down a little more I am makeing a database template to be used for each Review I do for a Bank. Since there are a bunch of different banks each database will be its own Bank Name. Each record is one loan review. The table I have now is all the required fields I need to make one review or record. The report you have now is one amoung a bunch that is used to indicate certian financial statements that might be missing from a Bank customers file. Like if you have a mortgage and in your file at the bank you didnt submit a 2003 tax return. I would need that to analyze your financial capicity to repay the debt. The missing techs report indicates all the financial statements needed for each loan in a certian portfolio.

If I separate the table wouldn't that make it hard to find data for each record?

 

[WayneRyan]

bisonnette,

No, putting them in one table would make it easier. Then you just report
on records that are present. And it makes it a lot easier if you have the
need to add one (or more) items to your list.

The Query design grid just can't support that many "OR" clauses. That's
an indication that the structure of your data is not what the good folks
at Microsoft anticipated.

As it is now, your DB is functional, I'll try to give you a sample structure
later.

Wayne