Form to Report Question

  • Thread starter Thread starter fakeemail
  • Start date Start date
F

fakeemail

I have an Input form that includes data from a subform.

When I make a report to print out the form the data from the second
table(the Subform) scrables and is out of order on the report.

I have checked everything I can think of and there's nothing that
should be causing the subform data to print out of order.
No sorts, et......

The form itself is set to print everything in order based on a record
number that is an autonumber field.

The data from the "secondary" table HAS to print in order as there
are cases where the first few entries and the last few entries refer
to different things.

It's ingredients for recipes.

The first five may be ingredients for the main item and the second
five ingredients may belong to an associated item.

For instance.
A Cake
The icing for the cake.

I don't want to separate them as this might be an icing only for this
one type of cake.

But, if the ingredients get scrambled on the printout, they won't make
as much sense.

The problem only occurs on these two parters. On single item recipes
it doesn't matter if the ingredients scramble. Though it would be nice
to know why it is happening.
 
I have an Input form that includes data from a subform.
Click to expand...

Well, to be precise, it includes data from a Table which is displayed
on a subform. The data is not stored in the subform but in the Table.
When I make a report to print out the form the data from the second
table(the Subform) scrables and is out of order on the report.
Click to expand...

And a Table HAS NO ORDER. The order in which you input records on the
Subform has no guaranteed bearing on the order in which a Report will
be printed.
I have checked everything I can think of and there's nothing that
should be causing the subform data to print out of order.
No sorts, et......
Click to expand...

If you have no sorting, then the order in which records will print on
a report is unpredictable. Access will print them in any order it
finds convenient. If you want to see the records in a particular
order, you must - no options! - specify a sort order somewhere - in a
Query or (more commonly for a report) in the Sorting and Grouping
property of the report.
The form itself is set to print everything in order based on a record
number that is an autonumber field.
Click to expand...

Again... a form contains no data. The order in which records are
displayed on a form depends on how that form is set up.
The data from the "secondary" table HAS to print in order as there
are cases where the first few entries and the last few entries refer
to different things.
Click to expand...

Then you must - no option! - have some field or combination of fields
in this table, and you must specify those fields in the Form's Sorting
and Grouping dialog.
It's ingredients for recipes.

The first five may be ingredients for the main item and the second
five ingredients may belong to an associated item.

For instance.
A Cake
The icing for the cake.

I don't want to separate them as this might be an icing only for this
one type of cake.

But, if the ingredients get scrambled on the printout, they won't make
as much sense.

The problem only occurs on these two parters. On single item recipes
it doesn't matter if the ingredients scramble. Though it would be nice
to know why it is happening.
Click to expand...

It's happening because you're not specifying the desired sort order.
Just putting them in order on the form will NOT do this; you must
specify it. Right mouseclick the little square at the upper left of
the Report in design view and select Sorting and Grouping; select your
autonumber field.


John W. Vinson[MVP]
 
Well, to be precise, it includes data from a Table which is displayed
on a subform. The data is not stored in the subform but in the Table.


And a Table HAS NO ORDER. The order in which you input records on the
Subform has no guaranteed bearing on the order in which a Report will
be printed.


If you have no sorting, then the order in which records will print on
a report is unpredictable. Access will print them in any order it
finds convenient. If you want to see the records in a particular
order, you must - no options! - specify a sort order somewhere - in a
Query or (more commonly for a report) in the Sorting and Grouping
property of the report.


Again... a form contains no data. The order in which records are
displayed on a form depends on how that form is set up.


Then you must - no option! - have some field or combination of fields
in this table, and you must specify those fields in the Form's Sorting
and Grouping dialog.


It's happening because you're not specifying the desired sort order.
Just putting them in order on the form will NOT do this; you must
specify it. Right mouseclick the little square at the upper left of
the Report in design view and select Sorting and Grouping; select your
autonumber field.


John W. Vinson[MVP]
Click to expand...


I checked the Sorting and Grouping and it is set to the autonumber
field in the primary table, Ascending Order.

Just for grins, I set it to the autonumber field in the secondary
table and it made no difference. The records in the group still
scramble out of order.

Sigh.
 
I checked the Sorting and Grouping and it is set to the autonumber
field in the primary table, Ascending Order.

Just for grins, I set it to the autonumber field in the secondary
table and it made no difference. The records in the group still
scramble out of order.
Click to expand...

Without knowing anything about the nature of your data or the query,
all I can suggest is that you may need *two* fields sorted in the
Report sorting and grouping - sort first by the main table's ID (to
get the grouping correct, all data for an individual main table record
together) and on the next line, sort ascending by the related table
ID.

Note that Autonumbers may well not be ideal for this purpose: they can
become random. If you really need to control the sort order you may
want to consider having a numeric sort field in the table; you can
fill it in using the BeforeInsert event of your subform:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSort = NZ(DMax("[Sortkey]", "[subtable]", "[ID] = " & [ID])) + 1
End Sub

John W. Vinson[MVP]
 
I checked the Sorting and Grouping and it is set to the autonumber
field in the primary table, Ascending Order.

Just for grins, I set it to the autonumber field in the secondary
table and it made no difference. The records in the group still
scramble out of order.
Click to expand...

Without knowing anything about the nature of your data or the query,
all I can suggest is that you may need *two* fields sorted in the
Report sorting and grouping - sort first by the main table's ID (to
get the grouping correct, all data for an individual main table record
together) and on the next line, sort ascending by the related table
ID.

Note that Autonumbers may well not be ideal for this purpose: they can
become random. If you really need to control the sort order you may
want to consider having a numeric sort field in the table; you can
fill it in using the BeforeInsert event of your subform:

Private Sub Form_BeforeInsert(Cancel as Integer)
Me!txtSort = NZ(DMax("[Sortkey]", "[subtable]", "[ID] = " & [ID])) + 1
End Sub

John W. Vinson[MVP]
Click to expand...


I tried setting the sort on both Autonumber fields. Everything but two
records in the secondary table are in order now.
Still not 100%

So I went into Access Help to see how they deal with reports and sub
data and they talk about putting whole sub-reports in place and that
is NOT the way this is set up by the damn access Report wizard.

So, I tried that and it screwed everything up.

Autonumber in access is a pain in the ass, especially when it is
looking for an interval of 1 and the interval is not one because
autonumber does not handle deletes well.

I am going to say screw it and delete all the secondary recipes that
go with the primaries. Thus eliminating the issue.
 
I tried setting the sort on both Autonumber fields. Everything but two
records in the secondary table are in order now.
Still not 100%
Click to expand...

Well... Access is (I'm sure) sorting the records the way you're
*telling* it to (which is of course different from the way you *want*
it to).
So I went into Access Help to see how they deal with reports and sub
data and they talk about putting whole sub-reports in place and that
is NOT the way this is set up by the damn access Report wizard.

So, I tried that and it screwed everything up.
Click to expand...

umm... did you save a backup?
Autonumber in access is a pain in the ass, especially when it is
looking for an interval of 1 and the interval is not one because
autonumber does not handle deletes well.
Click to expand...

An Autonumber is designed for one purpose, and one purpose ONLY: to
provide a guaranteed unique key. This means, among other things, that
an autonumber value will never be reused, even if you delete a record.
This is "handled well" *FOR THAT PURPOSE* - the new autonumber can be
assured to be unique.

If you're assuming that autonumbers are sequential and free of gaps,
the error is in your assumption, not in the implementation of the
autonumber.
I am going to say screw it and delete all the secondary recipes that
go with the primaries. Thus eliminating the issue.
Click to expand...

If that meets your needs, fine. I'm sure that the tables and queries
can be modified to do what you wish, however.

John W. Vinson[MVP]
 
Well... Access is (I'm sure) sorting the records the way you're
*telling* it to (which is of course different from the way you *want*
it to).


umm... did you save a backup?


An Autonumber is designed for one purpose, and one purpose ONLY: to
provide a guaranteed unique key. This means, among other things, that
an autonumber value will never be reused, even if you delete a record.
This is "handled well" *FOR THAT PURPOSE* - the new autonumber can be
assured to be unique.

If you're assuming that autonumbers are sequential and free of gaps,
the error is in your assumption, not in the implementation of the
autonumber.


If that meets your needs, fine. I'm sure that the tables and queries
can be modified to do what you wish, however.

John W. Vinson[MVP]
Click to expand...
Actually, I've banged this aroundand the only way I can think of to
solve it and guarantee it to print the way I want is to put a NUMBER
or letter next to each ingredient in the input form.

1.
2.
3.
4.

Then sort on that field in ascending order.

Not worth it for the current task but will keep it in mind for the
future.

As usual, I do appreciate your help. And I do pass it forward.
 
Actually, I've banged this aroundand the only way I can think of to
solve it and guarantee it to print the way I want is to put a NUMBER
or letter next to each ingredient in the input form.
Click to expand...

That's probably the best way out. Glad to have been of assistance and
good luck!

John W. Vinson[MVP]
 
Back
Top