A2K-adp; Problem when creating a report

  • Thread starter Thread starter =?iso-8859-1?Q?Alexander_Fr=FChwirth?=
  • Start date Start date
?

=?iso-8859-1?Q?Alexander_Fr=FChwirth?=

Good morning,

when i try to create a report with the help of the wirzard
im receiving the following error(s):

"this key is already in use by another element of the
list" (always)
"Index out of range" (sometimes, after the first error
message)

A2K (SP3) is the frontend and SQL Server 2000 (SP3) works
as Backend.
Yesterday i tried to create a report in a completly
different config and the same problem occured.

Any suggestions?
Thanks Alex
 
Alex:

My guess is that when the report wizard is filling the list of fields or
other elements, that it is running accross a set of similar field names
(possible if you've got joined tables in a view that you are using for the
recordsource where both sides of the join are displayed?) so when it tries
to fill a list / tree control it's getting a duplicate key error. Have you
tried the wizard based on a very simple view and then backed into your
current recordsource to see if you can pin point the error?
 
So you mean that this could be a problem with duplicate
names?

Connecting to the Northwind Database: when i try to make a
rep over the tables 'orders' and 'order details' the error
also occurs. And there are no duplicate names (expect the
orders.orderID and orderdetail.orderID).

When i select colums eg OrderID, Unit Price, Quantity from
OrderDetail i've got the OrderDetail standing in the
Recordsource.
I could make a select Statement int the 'dev-view' (don't
know the englisch expression) and that works. But the
other users wont make a report in that way. They like
wizard based systems.

Thanks
Alex
 
Alex:

I've tried to replicate your problem using Northwind on both SQL 7 (SP4) and
SQL 2000 servers (SP3a on the 2000 server) using Access 2000 SP 3. I used
the standard orders and order details tables to create a view that included
fields from both sides of the join. (no criteria). I tested using the
common field (orderID) from each side of the join and from both sides of the
join (with the second instanced aliased.) Everything worked with Report
Wizard just fine. Note I was logged in in an dbo.admin role on the server.

What's the SQL on the view(s) you were testing where the wizard failed?
What type of log in are you using when trying to use the Wizard, and what
level of permissions does that log in have on the tables/view?
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

So you mean that this could be a problem with duplicate
names?

Connecting to the Northwind Database: when i try to make a
rep over the tables 'orders' and 'order details' the error
also occurs. And there are no duplicate names (expect the
orders.orderID and orderdetail.orderID).

When i select colums eg OrderID, Unit Price, Quantity from
OrderDetail i've got the OrderDetail standing in the
Recordsource.
I could make a select Statement int the 'dev-view' (don't
know the englisch expression) and that works. But the
other users wont make a report in that way. They like
wizard based systems.

Thanks
Alex
 
Select statement for the view should look like this

SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID,
dbo.Orders.EmployeeID, dbo.Orders.OrderDate,
dbo.Orders.RequiredDate, dbo.Orders.ShippedDate,
dbo.Orders.ShipVia, dbo.Orders.Freight,
dbo.Orders.ShipName, dbo.Orders.ShipAddress,
dbo.Orders.ShipCity, dbo.Orders.ShipRegion,
dbo.Orders.ShipPostalCode, dbo.Orders.ShipCountry, dbo.
[Order Details].OrderID AS Expr1, dbo.[Order
Details].ProductID, dbo.[Order Details].Quantity, dbo.
[Order Details].UnitPrice, dbo.[Order Details].Discount

FROM dbo.[Order Details] INNER JOIN dbo.Orders ON dbo.
[Order Details].OrderID = dbo.Orders.OrderID

If i create the view (eg qryOrders) and build the report
on this view everything works fine.

I start the wizard, select the table orders an all of its
columns (all columns/field of the table orders appear in
the selected fields list box). Then i select the table
order details an all of its columns (all columns in
the 'selected fields' list box).
If i continue with creating the report this way i get this
error when i click on the 'continue button'.

On my PC is ADO 2.8 installed. Where could i find out
which MDAC Version this is? (Help/info/systeminfo/MS
Access?)

Thanks
 
Alex:

OK; I can now repo your problem because I understand what you were doing
(combining the tables via the Wizard rather than using a view to start
with). The A2000 wizard internally is evidently not handling the aliasing
of the fields appropriately.

Interestingly, if you connect A2000 to a SQL 7 version of Northwind with the
same scenario, you get a different error stating that you've choose fields
from two non-related tables. (Wrong!) and the Wizard shuts down.

I don't think you are going to find a fix for this any time soon for Access
2000. I should note that in Access 2002 (Xp) and in 2003, your scenario
described below work just fine in the Report Wizard and it appropriately
chooses the parent table in the one to many relationship for setting up the
report's grouping. If you are going to work in ADPs substantially, I
highly recommend an upgrade to Access 2002 at a minimum because its ADP
capabilites are much better than Access 2000's by far; its often considered
the ADP bug fix. <G>.

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg



Select statement for the view should look like this

SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID,
dbo.Orders.EmployeeID, dbo.Orders.OrderDate,
dbo.Orders.RequiredDate, dbo.Orders.ShippedDate,
dbo.Orders.ShipVia, dbo.Orders.Freight,
dbo.Orders.ShipName, dbo.Orders.ShipAddress,
dbo.Orders.ShipCity, dbo.Orders.ShipRegion,
dbo.Orders.ShipPostalCode, dbo.Orders.ShipCountry, dbo.
[Order Details].OrderID AS Expr1, dbo.[Order
Details].ProductID, dbo.[Order Details].Quantity, dbo.
[Order Details].UnitPrice, dbo.[Order Details].Discount

FROM dbo.[Order Details] INNER JOIN dbo.Orders ON dbo.
[Order Details].OrderID = dbo.Orders.OrderID

If i create the view (eg qryOrders) and build the report
on this view everything works fine.

I start the wizard, select the table orders an all of its
columns (all columns/field of the table orders appear in
the selected fields list box). Then i select the table
order details an all of its columns (all columns in
the 'selected fields' list box).
If i continue with creating the report this way i get this
error when i click on the 'continue button'.

On my PC is ADO 2.8 installed. Where could i find out
which MDAC Version this is? (Help/info/systeminfo/MS
Access?)

Thanks
 
Thanks for your help.
Alex

-----Original Message-----
Alex:

OK; I can now repo your problem because I understand what you were doing
(combining the tables via the Wizard rather than using a view to start
with). The A2000 wizard internally is evidently not handling the aliasing
of the fields appropriately.

Interestingly, if you connect A2000 to a SQL 7 version of Northwind with the
same scenario, you get a different error stating that you've choose fields
from two non-related tables. (Wrong!) and the Wizard shuts down.

I don't think you are going to find a fix for this any time soon for Access
2000. I should note that in Access 2002 (Xp) and in 2003, your scenario
described below work just fine in the Report Wizard and it appropriately
chooses the parent table in the one to many relationship for setting up the
report's grouping. If you are going to work in ADPs substantially, I
highly recommend an upgrade to Access 2002 at a minimum because its ADP
capabilites are much better than Access 2000's by far; its often considered
the ADP bug fix. <G>.

HTH
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg



"Alexander Frühwirth"
Click to expand...
Select statement for the view should look like this

SELECT dbo.Orders.OrderID, dbo.Orders.CustomerID,
dbo.Orders.EmployeeID, dbo.Orders.OrderDate,
dbo.Orders.RequiredDate, dbo.Orders.ShippedDate,
dbo.Orders.ShipVia, dbo.Orders.Freight,
dbo.Orders.ShipName, dbo.Orders.ShipAddress,
dbo.Orders.ShipCity, dbo.Orders.ShipRegion,
dbo.Orders.ShipPostalCode, dbo.Orders.ShipCountry, dbo.
[Order Details].OrderID AS Expr1, dbo.[Order
Details].ProductID, dbo.[Order Details].Quantity, dbo.
[Order Details].UnitPrice, dbo.[Order Details].Discount

FROM dbo.[Order Details] INNER JOIN dbo.Orders ON dbo.
[Order Details].OrderID = dbo.Orders.OrderID

If i create the view (eg qryOrders) and build the report
on this view everything works fine.

I start the wizard, select the table orders an all of its
columns (all columns/field of the table orders appear in
the selected fields list box). Then i select the table
order details an all of its columns (all columns in
the 'selected fields' list box).
If i continue with creating the report this way i get this
error when i click on the 'continue button'.

On my PC is ADO 2.8 installed. Where could i find out
which MDAC Version this is? (Help/info/systeminfo/MS
Access?)

Thanks

-----Original Message-----
Alex:

I've tried to replicate your problem using Northwind on both SQL 7 (SP4) and
SQL 2000 servers (SP3a on the 2000 server) using Access 2000 SP 3. I used
the standard orders and order details tables to create a view that included
fields from both sides of the join. (no criteria). I tested using the
common field (orderID) from each side of the join and from both sides of the
join (with the second instanced aliased.) Everything worked with Report
Wizard just fine. Note I was logged in in an dbo.admin role on the server.

What's the SQL on the view(s) you were testing where the wizard failed?
What type of log in are you using when trying to use the Wizard, and what
level of permissions does that log in have on the tables/view?
--
Steve Arbaugh
ACG Soft
http://ourworld.compuserve.com/homepages/attac-cg

"Alexander Frühwirth"
Click to expand...
So you mean that this could be a problem with duplicate
names?

Connecting to the Northwind Database: when i try to make a
rep over the tables 'orders' and 'order details' the error
also occurs. And there are no duplicate names (expect the
orders.orderID and orderdetail.orderID).

When i select colums eg OrderID, Unit Price, Quantity from
OrderDetail i've got the OrderDetail standing in the
Recordsource.
I could make a select Statement int the 'dev-view' (don't
know the englisch expression) and that works. But the
other users wont make a report in that way. They like
wizard based systems.

Thanks
Alex
displayed?)
so when it tries



.
Click to expand...


.
Click to expand...
 
Back
Top