Duplicated field in columns

[jyoung729]

I have a report for door pricing. I have it set up to be in multiple columns
so that each door is one column with its pricing listed vertically beneath
it. The sizes available are listed on the left with the price next to it. The
problem I'm having is that I want to have the sizes only listed once on the
left, not in each column. How do I tell it to only show that field once on
the page?

Thanks!

Jennifer

 

[KARL DEWEY]

Open report in design view, click on the size text box, double click, select
Properties.
Scroll to the Hide Duplicates and set to Yes.

 

[jyoung729]

I tried that and it doesn't work. I think it only looks for duplicates in
each column, not on each page.

 

[Marshall Barton]

I have a report for door pricing. I have it set up to be in multiple columns
so that each door is one column with its pricing listed vertically beneath
it. The sizes available are listed on the left with the price next to it. The
problem I'm having is that I want to have the sizes only listed once on the
left, not in each column. How do I tell it to only show that field once on
the page?

This article demonstrates a way to do that.
http://support.microsoft.com/kb/210044

 

[jyoung729]

The sizes are a populated text field, not a label. My report looks basically
like this:

Door 6 Panel Door
--------------------- ------------------------------
Size Price 2/8 x 6/8 $150
3/0 x 6/8 $155


With door in a header and the size and price in the detail. Do you have an
article about not duplicating a text field?

 

[Marshall Barton]

The sizes are a populated text field, not a label. My report looks basically
like this:

Door 6 Panel Door
--------------------- ------------------------------
Size Price 2/8 x 6/8 $150
3/0 x 6/8 $155


With door in a header and the size and price in the detail. Do you have an
article about not duplicating a text field?

The same article can be used with a text box instead of a
label. I don't see whaere your example demonstrates
multiple columns.

If you think it does not fit your needs, please explain why
not.

 

[jyoung729]

Maybe this is partly my ignorance as I'm not very knowledgeable about the
visual basic code. I don't understand how to change the code to be for only
one text field without the label. Here is a better visual of the end report:

2 Panel Door 3 Panel Door 4 Panel Door

1/6 x 6/8 $50 1/6 x 6/8 $50 1/6 x 6/8
$50
1/8 x 6/8 $55 1/8 x 6/8 $55 1/8 x 6/8
$55
2/0 x 6/8 $60 2/0 x 6/8 $60 2/0 x 6/8
$60
2/2 x 6/8 $65 2/2 x 6/8 $65 2/2 x 6/8
$65
2/4 x 6/8 $70 2/4 x 6/8 $70 2/4 x 6/8
$70
2/6 x 6/8 $75 2/6 x 6/8 $75 2/6 x 6/8
$75
2/8 x 6/8 $80 2/8 x 6/8 $80 2/8 x 6/8
$80
2/10 x 6/8 $85 2/10 x 6/8 $85 2/10 x 6/8
$85
3/0 x 6/8 $90 3/0 x 6/8 $90 3/0 x 6/8
$90

The design view looks like the following:

Door Name

Size Price


I have it set up to be in 3 columns right now as that's all the fits on the
page with the duplicated size field. I need to have the size field only show
once on the left so I can fit 5 doors on one page. I hope I've explained this
better.

 

[Marshall Barton]

Maybe this is partly my ignorance as I'm not very knowledgeable about the
visual basic code. I don't understand how to change the code to be for only
one text field without the label. Here is a better visual of the end report:

2 Panel Door 3 Panel Door 4 Panel Door

1/6 x 6/8 $50 1/6 x 6/8 $50 1/6 x 6/8
$50
1/8 x 6/8 $55 1/8 x 6/8 $55 1/8 x 6/8
$55
2/0 x 6/8 $60 2/0 x 6/8 $60 2/0 x 6/8
$60

The design view looks like the following:

Door Name

Size Price


I have it set up to be in 3 columns right now as that's all the fits on the
page with the duplicated size field. I need to have the size field only show
once on the left so I can fit 5 doors on one page. I hope I've explained this
better.

Because you only have one "row" of controls in the detail
section, the code you need is simpler than the article's
code:

If Me.Left <Me.Width Then
Me.NextRecord = False
Me.Size.Visible = True
Me.Price.Visible = False
Else
Me.Size.Visible = False
Me.Price.Visible = True
End If

Note 1) The two text boxes need to have one on top of the
other.

Note 2) The door names need to be in a header section.
This can be an issue if the names are different for
different products. I would need more information if this
really is an issue.

Note3) Each size must have the same number of records. If
not, then the columns will not line up with the header
names. There is another way to address this part of the
problem, but again, more information is needed.

 

[jyoung729]

So I tried that code and it didn't work. I put the price field on top of the
size field like you said and I literally had them on top of each other, I
must have something wrong in the code. I triple checked what I typed to what
you gave me and I typed it correctly. I'm not sure what I'm doing wrong.

How does the top portion of the code translate? I understand the size and
price visibilty being set to true and false.

The door names are in a header section. There are different door names for
the different products, ie. 2 panel, 3 panel, 4 panel, etc.

Each size does have the same number of records in the price field.

I really appreciate all your help.

 

[Marshall Barton]

If I had thought your situation all the way through, I would
have added:

Note 4) The article's code requires that the report be as
wide as the column width. This is kind of a silly
requirement because you obviously need the report and/or
page headers to be the paper width (less margins).

Assuming you are using A2002 or later, I think you should
change the If statement to:
If Me.Left = Me.Printer.LeftMargin Then

The Me.Left provides the position of the section from the
left edge of the paper. The first column will be at the
left margin and the If statement cheks for that. The second
column will be at the left margin plus the column width, so
the If simply checks for the first column when you want to
display the door size.

The line:
Me.NextRecord = False
tells Access to process the detail record again. The second
time the record is processed, the If condition will not be
True so the Else block will make the price visible to
display it in the second column.

 

[jyoung729]

I made the change and it still isn't working. I'm using Access 2007. It
automatically shows up with some code already in there and I don't know what
it means. It puts the cursor for me to type in between it, so that's where I
put it. Here is exactly everything it says:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Left = Me.Printer.LeftMargin Then
Me.NextRecord = False
Me.Size.Visible = True
Me.Price.Visible = False
Else
Me.Size.Visible = False
Me.Price.Visible = True
End If

End Sub

 

[Marshall Barton]

I made the change and it still isn't working. I'm using Access 2007. It
automatically shows up with some code already in there and I don't know what
it means. It puts the cursor for me to type in between it, so that's where I
put it. Here is exactly everything it says:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Left = Me.Printer.LeftMargin Then
Me.NextRecord = False
Me.Size.Visible = True
Me.Price.Visible = False
Else
Me.Size.Visible = False
Me.Price.Visible = True
End If

End Sub

You put it in the right place and it looks good to me.

What do mean by "isn't working"? Is something in the wrong
place? Do you get an error message? I need as many clues
as you can provide.

 

[jyoung729]

Sorry. I'm still getting the columns overlapping as they are literally placed
in the report. It doesn't appear to be following what the code is telling it
to do.

 

[Marshall Barton]

I think that implies that either the text boxes are not
named Size and Price or that the code is not being executed.

You can verify the latter by putting a breakpoint on the If
statement (click in the gray bar at the left of the line.

If the code does not stop, then check to make sure that the
detail section's OnFormat property contains:
[Event Procedure]

If the code stops there, check the values of everything in
the procedure by hovering the mouse over each one in turn
and report back.

 

[jyoung729]

I figured out that my macros were disabled. I enabled them and it worked. Now
the problem I have is, it is omitting the first door's pricing. It shows the
first door name over the sizes and then moves on to the second door and its
pricing. How do I get it to show the sizes and then show the doors and their
pricing?

I think that implies that either the text boxes are not
named Size and Price or that the code is not being executed.

You can verify the latter by putting a breakpoint on the If
statement (click in the gray bar at the left of the line.

If the code does not stop, then check to make sure that the
detail section's OnFormat property contains:
[Event Procedure]

If the code stops there, check the values of everything in
the procedure by hovering the mouse over each one in turn
and report back.
--
Marsh
MVP [MS Access]

Sorry. I'm still getting the columns overlapping as they are literally placed
in the report. It doesn't appear to be following what the code is telling it
to do.

 

[Marshall Barton]

Macros? How did macros come into the question?

I don't think I follow your description of what's happening.
What column displays what information?

Put a breakpoint on the If statement and check the values of
Me.Left and Me.Printer.LeftMargin for the first couple of
lines? Post those values so I can see if they are as
expected. I should know what you have set for the left
margin and column width.

Just in case, post a Copy/Paste of the code so I can double
check it as you have it.
--
Marsh
MVP [MS Access]

I figured out that my macros were disabled. I enabled them and it worked. Now
the problem I have is, it is omitting the first door's pricing. It shows the
first door name over the sizes and then moves on to the second door and its
pricing. How do I get it to show the sizes and then show the doors and their
pricing?

I think that implies that either the text boxes are not
named Size and Price or that the code is not being executed.

You can verify the latter by putting a breakpoint on the If
statement (click in the gray bar at the left of the line.

If the code does not stop, then check to make sure that the
detail section's OnFormat property contains:
[Event Procedure]

If the code stops there, check the values of everything in
the procedure by hovering the mouse over each one in turn
and report back.

Sorry. I'm still getting the columns overlapping as they are literally placed
in the report. It doesn't appear to be following what the code is telling it
to do.

:

jyoung729 wrote:

I made the change and it still isn't working. I'm using Access 2007. It
automatically shows up with some code already in there and I don't know what
it means. It puts the cursor for me to type in between it, so that's where I
put it. Here is exactly everything it says:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Left = Me.Printer.LeftMargin Then
Me.NextRecord = False
Me.Size.Visible = True
Me.Price.Visible = False
Else
Me.Size.Visible = False
Me.Price.Visible = True
End If

End Sub


You put it in the right place and it looks good to me.

What do mean by "isn't working"? Is something in the wrong
place? Do you get an error message? I need as many clues
as you can provide.

 

[jyoung729]

I was doing some "trial and error" and I got a message that said my macros
were disabled and to see "help" to enable them. So I did that and then the
report executed the code. I don't really know why that would make a
difference. I also had a problem of the same size repeating down the page. I
changed the Me.NextRecord to equal True and it listed the sizes correctly.

The report is listing the 1st door with the sizes under it. Then it is
listing the 2nd door in the 2nd column, with it's pricing under it. It is not
listing the pricing of the 1st door. So it looks like this:

Door #1 Door #2 Door #3

Sizes Price #2 Price #3


It says Me.Left = 1080 and Me.Printer.LeftMargin = 1080. The left margin is
at .75" and the column width is at 1".

Here is the copy and paste of the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Left = Me.Printer.LeftMargin Then
Me.NextRecord = True
Me.Size.Visible = True
Me.Price.Visible = False
Else
Me.Size.Visible = False
Me.Price.Visible = True
End If
End Sub

Macros? How did macros come into the question?

I don't think I follow your description of what's happening.
What column displays what information?

Put a breakpoint on the If statement and check the values of
Me.Left and Me.Printer.LeftMargin for the first couple of
lines? Post those values so I can see if they are as
expected. I should know what you have set for the left
margin and column width.

Just in case, post a Copy/Paste of the code so I can double
check it as you have it.
--
Marsh
MVP [MS Access]

I figured out that my macros were disabled. I enabled them and it worked. Now
the problem I have is, it is omitting the first door's pricing. It shows the
first door name over the sizes and then moves on to the second door and its
pricing. How do I get it to show the sizes and then show the doors and their
pricing?

I think that implies that either the text boxes are not
named Size and Price or that the code is not being executed.

You can verify the latter by putting a breakpoint on the If
statement (click in the gray bar at the left of the line.

If the code does not stop, then check to make sure that the
detail section's OnFormat property contains:
[Event Procedure]

If the code stops there, check the values of everything in
the procedure by hovering the mouse over each one in turn
and report back.


jyoung729 wrote:

Sorry. I'm still getting the columns overlapping as they are literally placed
in the report. It doesn't appear to be following what the code is telling it
to do.

:

jyoung729 wrote:

I made the change and it still isn't working. I'm using Access 2007. It
automatically shows up with some code already in there and I don't know what
it means. It puts the cursor for me to type in between it, so that's where I
put it. Here is exactly everything it says:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)

If Me.Left = Me.Printer.LeftMargin Then
Me.NextRecord = False
Me.Size.Visible = True
Me.Price.Visible = False
Else
Me.Size.Visible = False
Me.Price.Visible = True
End If

End Sub


You put it in the right place and it looks good to me.

What do mean by "isn't working"? Is something in the wrong
place? Do you get an error message? I need as many clues
as you can provide.

 

[Marshall Barton]

I also had a problem of the same size repeating down the page. I
changed the Me.NextRecord to equal True and it listed the sizes correctly.

The report is listing the 1st door with the sizes under it. Then it is
listing the 2nd door in the 2nd column, with it's pricing under it. It is not
listing the pricing of the 1st door. So it looks like this:

Door #1 Door #2 Door #3

Sizes Price #2 Price #3


It says Me.Left = 1080 and Me.Printer.LeftMargin = 1080. The left margin is
at .75" and the column width is at 1".

Here is the copy and paste of the code:

Private Sub Detail_Format(Cancel As Integer, FormatCount As Integer)
If Me.Left = Me.Printer.LeftMargin Then
Me.NextRecord = True
Me.Size.Visible = True
Me.Price.Visible = False
Else
Me.Size.Visible = False
Me.Price.Visible = True
End If
End Sub

That result is what I expect when you use NextRecord = True,
change it back to False and show me what happens. Use a few
lines of real output instead of just Sizes and Price#2, etc.

You probably want tp move the header labels to the right so
they line up with the second column.

 

[jyoung729]

I changed NextRecord back to false. I don't understand what you mean about
moving the header over to be over the 2nd column. If I move it over, it is
not shown on the report. I feel like I need to give it a code to execute that
tells it to not be visible over the sizes and only over the prices.

Now that I changed it back to false, it is showing the 2 panel pricing but
not under the 2 panel heading. And now the same size is repeated every time.

Here is a sample of how the report looks now:

2 Panel $227 3 Panel 4 Panel 5 Panel 6 Panel
$227
1/6 x 6/8 $238 $230 $190 $248 $190
1/6 x 6/8 $262 $230 $190 $248 $190
1/6 x 6/8 $238 $240 $203 $257 $203
1/6 x 6/8 $238 $266 $239 $292 $239
1/6 x 6/8 $245 $240 $203 $257 $203
1/6 x 6/8 $251 $240 $203 $257 $203
1/6 x 6/8 $262 $249 $210 $269 $210
1/6 x 6/8 $254 $218 $273 $218
1/6 x 6/8 $266 $239 $292 $239
1/6 x 6/8
1/6 x 6/8
1/6 x 6/8
1/6 x 6/8
1/6 x 6/8
1/6 x 6/8
1/6 x 6/8
1/6 x 6/8

 

[Marshall Barton]

I changed NextRecord back to false. I don't understand what you mean about
moving the header over to be over the 2nd column. If I move it over, it is
not shown on the report. I feel like I need to give it a code to execute that
tells it to not be visible over the sizes and only over the prices.

Now that I changed it back to false, it is showing the 2 panel pricing but
not under the 2 panel heading. And now the same size is repeated every time.

Here is a sample of how the report looks now:

2 Panel $227 3 Panel 4 Panel 5 Panel 6 Panel
$227
1/6 x 6/8 $238 $230 $190 $248 $190
1/6 x 6/8 $262 $230 $190 $248 $190
1/6 x 6/8 $238 $240 $203 $257 $203
1/6 x 6/8 $238 $266 $239 $292 $239
1/6 x 6/8 $245 $240 $203 $257 $203
1/6 x 6/8 $251 $240 $203 $257 $203
1/6 x 6/8 $262 $249 $210 $269 $210
1/6 x 6/8 $254 $218 $273 $218
1/6 x 6/8 $266 $239 $292 $239
1/6 x 6/8
1/6 x 6/8
1/6 x 6/8

I am having trouble dreaming up a scenario that can explain
that output from what I think you said the report's records
contain and how you have laid out the report's design.

That second column starting in the header really baffles me.
It seems as if the header is not in its own section.

The repeating first column makes it appear that each size
price pair are not in their own record.

Can you post a list of the report sections along with the
controls in each section?

I think I should also look at the report's record source
query along with about a dozen of its records.