Printing a new line based on previous line's field value 2

[drazeni]

I have an invoice report (generic/text only).

How do I get a new line in the detail section of the report based on a field's value in the previous line. Here's an example of the detail line:

qty         grade         type           description
1           3CR12         PLATE          1200x1250x1.6
1           304           CHANNEL        1200x1250x1250x1.3
                                         CUT SIZE:
2           3CR12         RING           1200X1250X1.5

So in other words, whenever the previous field in the report contains the word "CHANNEL" the next line must be automatically produced with "CUT SIZE:".

I am using 1 query referencing 2 tables ([Invoice] & [Invoice Details]).

I'm also assuming that this would be done on the detail format event, but haven't got an idea how to go about it.

Any help will be appreciated.
Mario. "All is not as it seems"

 

[Lozere]

Could be simple:
Move your present Detail section as a group section based on some field that changes for each line (even if not printed).
Then put in the new Detail section only the text box with the value "CUT SIZE:" at the right location.

Give a name to the field in your report (now) low-level group containing <type>, said TB_Type.

Create a Format event procedure at the Detail level with this only statement:
>>If Me.TB_Type <> &quot;CHANNEL&quot; then Cancel=True

This procedure will make Access skip the formatting (and printing) of the detail level but when type=CHANNEL.

 

[drazeni]

Thanks for your response, and it does work, except I notice that if there are 2 consecutive values one after another then it only prints &quot;CUT SIZE:&quot; after the last one. I need it to print after each value.

CURRENT EXAMPLE

CHANNEL
CHANNEL
           CUT SIZE:
PLATE
RING

REQUIRED

CHANNEL
           CUT SIZE:
CHANNEL
           CUT SIZE:
PLATE
RING

Your help is very much appreciated.

Thanks
Mario. &quot;All is not as it seems&quot;

 

[Lozere]

????Amazing

Try something I'm not able to check now:
Add a bound text field in the detail section with the <Visible> parameter set to NO.
That will make no visible difference but may be lead Access to run as you want.

Anyway run a trial of your report without the statement in the Format event procedure - you normally get &quot;CUT SIZE:&quot; between each item. If not, try to change parameters of the Detail section or in the Sort/Group parameters of the Report.

 

[drazeni]

Thanks, I found my problem, executing the code on every change of the &quot;type&quot; group instead of the &quot;description&quot; group, which would then check every instance due to the description value being different for every instance.

I suppose if the description ends up being the same for a consecutive record I could always create a dummy field with a unique value for each line and group on that.

Thanks a lot, learnt something today.
&quot;All is not as it seems&quot;

 

[drazeni]

Hi again,

Your method works great, but unfortunately, because of the group footer I've added on the description field, the detail records on the report are now being sorted because the group sort feature cannot be disabled and the client doesn't want that.

So I think I need a different method unless this sort can be disabled in some way. Anymore suggestions?

Thanks again. &quot;All is not as it seems&quot;

 

[Lozere]

In order to be able to suggest a walkaround, I need the description of your both tables involved in the request.

There is probably another way you can try:

Go back to your initial report.

Just change the text box of the detail section containing the value of the Description field to make it 2 times higher (that is 2 lines high rather than 1) and put the parameter CanShrink to YES both on this text box and on the Detail Section

In the Print Event procedure of the Detail Section, put a statement like:

If Me.TYPE_TextBox = &quot;Channel&quot; Then
Me.Description_TextBox = Me.Description_TextBox & vbCrLf & &quot;CUT SIZE:&quot;
End If

Due to the Can Shrink option, the section will print 2 lines high when type=CHANNEL and 1 line elsewhere.

 

[drazeni]

Hi Lozere, I have applied your instruction but I get a run-time error as follows:

&quot;You can't assign a value to this object&quot;.

I have tried playing with the coding of the IF statement such as adding .value to the end of the description, but no luck.

You have a quick answer for this? &quot;All is not as it seems&quot;

 

[Lozere]

Oh, sorry!
For sure, you can't, it's a bound field!

To achieve this result you can try to create a separate field for this purpose whose content is Null (or &quot;&quot;??)or &quot;CUT SIZE:&quot; depending on the Type value, of course with the CanShrink attribute set to YES.
Let your Description field one line high and put this new one under.
If it works, that is the simplest way at all.....

 

[drazeni]

I figured out a variation of your solution that seems to work. The code is as follows:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
Let cut_size_description.Visible = True
If preorderdet_type <> &quot;CHANNEL&quot; Then let cut_size_description.Visible = False
End Sub

cut_size_description is the new field placed under the original description field. I always set the .visible property to true unless the value in type is CHANNEL.

Thanks for your help. &quot;All is not as it seems&quot;