Can't INSERT record from subform

[Dave]

I have an adp project that I upgraded from an Access mdb database.

In my project I have a form and a sub form that represents a parent and
child relationship, say orders and parts. The order form contains a subform
whose control source is a query against the orderparts and parts table. The
query returns the orderid and the partid from orderparts table (junction
tabble) and the part name from the parts table.

I drag and drop the orderparts form on to the order form and I set the link
child fields and the link master fields on the orderid.

This works fine and as I navigate through my orders in this form I see all
of the associated parts in the orderparts subform.

However, when I try to add a new part to an order in the form, it freezes. I
get an error message that says "MS Access can't find object 'SELECT partid,
partname FROM part'.

I can change an existing part on an order from the sub form but I cannot add
a new part.

What am I doing wrong?

The orderparts table contains 3 fields: orderpartid, orderid, partid. The
primary key is a composite of orderid and partid. The recordset for the
orderpart form is: SELECT op.orderid, op.partid, p.partname FROM part p JOIN
orderpart op ON op.partid.p.partid There is a composite PK on the
orderpart table of orderid-partid.

Why can't I add a part to my order in the adp project? It worked in the
Access mdb before I upgraded?

 

[Dave]

Perhaps I should add that this is an Access 2000 file format ADP project
pointing to a SQL Server 2000 database.

 

[Sylvain Lafontaine]

Hi, I don't have the time to duplicate your problem at this time but as far
as I can remember, ADP had a lot of trouble with composite primary key and
the fact that you are using ADP 2000 instead of 2003 make things even worse.
You should try using single field primary keys. You might have to set the
UniqueTable and ResyncCommand properties (search this newsgroup with Google
or take a few hours reading the previous posts). However, because you are
using ADP 2000, I'm not sure that you won't have any problem at all, even if
you are setting the UniqueTable and ResyncCommand properties.

Taking a look with the SQL-Server Profiler would also help because it will
show you what Access/ADP is trying to do on the SQL-Server.

 

[Charles Wang [MSFT]]

Hi Dave,
I would like to add more comments here. The error messaged said "MS Access
can't find object 'SELECT partid, partname FROM part", however I noticed
that you said you had three tables: Orders, Parts and Orderparts, so is it
a typo error for the table name ("part"->"Parts") in the query?

Please check it again and let us know the truth. Also I would like to let
you know that Office 2000 has not been supported by Microsoft now and we
recommend that you use Office 2003 or 2007 from now on.

Best regards,
Charles Wang
Microsoft Online Community Support
===========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
===========================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.

Note: The MSDN Managed Newsgroup support offering is for
non-urgent issues where an initial response from the community
or a Microsoft Support Engineer within 1 business day is acceptable.
Please note that each follow up response may take approximately
2 business days as the support professional working with you may
need further investigation to reach the most efficient resolution.
The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by
contacting Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
============================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Charles Wang [MSFT]]

Hi Dave,
What is this issue going on?

If there is any issue, please feel free to post back. We are very glad for
further assistance.

Have a good day!

Best regards,
Charles Wang
Microsoft Online Community Support
=========================================================
Delighting our customers is our #1 priority. We welcome your
comments and suggestions about how we can improve the
support we provide to you. Please feel free to let my manager
know what you think of the level of service provided. You can
send feedback directly to my manager at: (e-mail address removed).
=========================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
=========================================================

 

[Dave]

Charles:

I rebuilt the form and it works.

Thanks
Dave