Too Many Fields Defined

[Danu]

Hi -

I have a large macro which takes codes and descriptions and makes tables.
For each group, there are two tables. In one table, one field is the code the
second is the description. In the second table, one field is the description,
the second is the code.

Each of these table is then transferred to Excel. There is no issue
transferring any of the tables which have the code as the first field and the
description of the second and begin inserting in column A. However, the
second table, which has the description first and the code second, must go on
the same worksheet in Excel in columns further down on the worksheet, e.g.,
columns D or E, etc.

I have drilled down to where the errors are occurring in the macro and it is
where the table is exported to the spreadsheet starting at columns D or E,
etc. All of these fail. I receive the error Too Many Fields Defined from
Access. Am I using incorrect naming convention? In the macro the Action is
TransferSpreadsheet, the range in the transfers that work is APPLE!A2:C450.
The range in the transfers that don't work is APPLE!D2:F450, etc.

The tables are mirror images of each other, just the fields reversed. Any
idea what I have done wrong?

Thanks.

 

[John W. Vinson]

Hi -

I have a large macro which takes codes and descriptions and makes tables.
For each group, there are two tables. In one table, one field is the code the
second is the description. In the second table, one field is the description,
the second is the code.

It's almost certainly NOT necessary to make any new tables! You can
TransferSpreadsheet from a Query just as easily as from a table. Can you
perhaps create a single query (with fields for code, description, description
and code, let's say) and export that query?

John W. Vinson [MVP]

 

[Danu]

Hi, John-
The reason for code/description and description/code is that the first set
is sorted alphabetically by code and the second set is sorted alphabetically
by description. Same info just arranged differently. I cannot do it in the
one query. The entire query sorts on the first field which requests a sort.

 

[Douglas J. Steele]

You should never make any assumptions about the order of the data in a
table. Tables don't have any implicit order: they're "sacks of data", where
Access puts the rows wherever it feels like.

How does query sort on the first field which requests a sort?

 

[Danu]

I could be approaching this all wrong...

query reads

description code code description
Sort: ascending ascending

The results of the query have the first column sorted in ascending order, no
other fields are sorted.

....where did I go wrong?

 

[Douglas J. Steele]

It would probably be a good idea to step back and explain exactly what
you're trying to do.

In general, having two identical tables would be wrong: you should never
store data redundantly. What's wrong with having two separate queries?

 

[Danu]

I have approximately 5000 codes and accompanying descriptions which must be
broken out into categories. This information is then transferred to an Excel
workbook. On each page for each category must appear the code (in
alphabetical order) and the accompanying description. Then a couple of
columns over the description (in alphabetical order) and the code. Each
category has its own worksheet. This workbook will be used as a reference for
others who might have a code but no description or vice versa.

 

[John W. Vinson]

I have approximately 5000 codes and accompanying descriptions which must be
broken out into categories. This information is then transferred to an Excel
workbook. On each page for each category must appear the code (in
alphabetical order) and the accompanying description. Then a couple of
columns over the description (in alphabetical order) and the code. Each
category has its own worksheet. This workbook will be used as a reference for
others who might have a code but no description or vice versa.

It sounds like you should be able to create two queries on the same table -
one sorted by code and the other by description - and export them, as queries
(without a MakeTable). Or, it might be simpler to export just the first, and
run a macro in Excel to copy the data to the second range and sort it in
place.

John W. Vinson [MVP]

 

[Danu]

Thank you, all!

It sounds like you should be able to create two queries on the same table -
one sorted by code and the other by description - and export them, as queries
(without a MakeTable). Or, it might be simpler to export just the first, and
run a macro in Excel to copy the data to the second range and sort it in
place.

John W. Vinson [MVP]