Problem adding record with with multi-table form

[Jay]

I have a project I upsized from a .MDB to an Access
2002 .ADP using SQL Server 2000 as the back end. I have
been using Access for some time, but am very new at
using .ADP projects with SQL Server. I have a form which
works in every respect, except for when I try to add a
record. A simplified example will illustrate my
problem. I have 3 tables.

Table1 Table2 Table3
ClientID(int,PK) ClientID(int,PK) ClientID(int,PK)
Data1(varchar) Data2(varchar) Data3(varchar)

I have set up the relationships, which are simple one-to-
one relationships between Table1-Table2 and Table1-
Table3. In Table1 I have set ClientID to be an Identity
value. I have a form which allows me to view ClientID,
Data1, Data2 and Data3. Works great and I can edit the
data fields also. But when I try to add a new record, I
get either "You can't update the record because another
user or application deleted it or changed the value of
its primary key" or "Invalid input parameter values.Check
the status values for details.". The same scenario
worked fine in the original .MDB. I have tried a number
of options, all of which have failed so I won't recount
them here except for two things. Firstly selecting any
table as the "Unique Table" property of the form has not
worked. Secondly, I have tried creating a new PK in
Table2 and Table3, then using ClientID as a FK to relate
the tables to Table1, in which case I get record locking
errors and no satisfaction. I also know it appears that
I am using three tables, with the same primary key, when
it would seem that I might be better off with a single
table using ClientID, Data1, Data2 and Data3, but this is
not a workable solution for my situation. I need to
continue to use the three dinstinct tables, but add
information from a single form. I thank you for any
help you can provide!

 

[SFAxess]

I suggest using subforms, so all the relationships will
be tied together properly. You can also use an unbound
form and write code to handle the calls between the
client and SQL server rather than Access, but why (re)
create something that Access can already do for you?

 

[Jay]

I thought that this was one of the improvements offered
by the CDM in Access 2002 - the ability to modify all
fields in a multi-table view. Then again, I guess I CAN
modify the fields, it is only adding (insert) that causes
the problem. Also, I have tried and found that I can get
this to work without subforms if I am only working with
two tables, but it seems to fall apart with three. The
reason I am so stubborn about using subforms in this
instance it that my example is a very simplified from the
actual project, does include 3 similar tables, but with
also about 120 other fields plus another one-to-many
joined table which I AM using as a subform. Since this
is an existing project being upsized from a .MDB I need
to be able to retain the design of the form for the sake
of the users - this would just not be possible using
subforms because fields from any one table are not
necessarily grouped together, but may be spread between
the main form and a tab contol. Using an unbound form
may be my only option, which make me think I should just
skip Access and go straight to VB...... I will keep
digging and see if I can find a way to make this work and
will return to this post. Thanks so much for your input!