Shrink Horizontal Whitespace AND Format Individual Controls

[trandel]

Hi,

I know how to shrink whitespace between horizontal controls by using a single control and code BUT I need to apply different colours to each component control.

An example of what I am trying to achieve is showing the areas a company is represented in and using the correct colour for that area:

XYZ company - GP KZN
ABC Co - WP EP GP
DEF Co Ltd. - KZN

etc

I am currently doing this via WORD Mail Merge and Field Codes but want to switch to native Access mode reports.

Any ideas welcome

Regards

Tony Randell

Any Ideas??

 

[Pat Hartman]

You'll need to purchase a rich text control. Normal form/report controls have only a single set of properties so there is no way for a single field to render multiple colors.

 

[trandel]

Hi,

Yes I appreciate that a control only has one set of properties. But before I look at buying rich text control I was thinking of some sort of coded function.

At this point let me say that I do not consider my coding skill very highly. However, my idea is to populate a set of individual unbound controls on the line in the report.( I only need 10 and nothing follows them on that line) My challenge then, is to determine which is the next available control to update. Then it would be possible to set the content and properties of that control using IIF logic to ensure that null entries are excluded?

I hope the above makes sense?

Regards

Tony Randell

 

[WayneRyan]

Tony,

Definitely possible, but a lot of work. Even with a third-party control,
it's a lot of work.

In your current scenario, use the DetailFormat event of the report:

1) Use the split function to break down each company's list of areas
into individual array elements.

2) Assign each array element to one of your textboxes.

2a) Use the Case statement to set the ForeColor of the textbox, based on
the area name

2b) If there is no array element, make the textbox invisible.

In retrospect, not that difficult to do.

btw,

If the areas were stored in a Child table (as opposed to one record),
you could accomplish this a whole lot easier.

Wayne

 

[trandel]

Hi Wayne,

Thanks for your reply.

I am not great at VB and did not completely understand your response.

Perhaps I should explain a little more of my (I am sure very simple) problem.

I have a report based on a crosstab query which is working fine. There is one record per customer and it contains a field defined for each of the 9 areas. I had the query simply assign the first Customer ID to each region encountered for a Customer name. (There can be duplicates in the same region but I only need to show that they are represented in that region)

I populate nine text controls with a three character descriptor unless the
field is null using an IIF statement in the source property value. e.g. for the control named EP I have the following source =IIf(IsNull([1]),"","EP") and so on.

Now I can easily set the colours for each text control but I will end up with a ton of whitespace where there are null entries.

I have tried a simple macro on the format event to set the visible property of each control but even I could get to work (nothing happens) I think I will still have the whitespace.

Regards

Tony R