Trouble getting form using two joined tables to work

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

moyrai - 29 Nov 2003 20:33 GMT

I am trying to create a form that is based on two tables: Members and
MemberDetails.

The Members table has the following fields
- MemberID (primary key)
- FirstName
- LastName

The MemberDetails Table has the following fields
- MemberID (primary key)
- Address
- City
- State
- Zip

MemberDetails is linked to Members through the MemberID field. My
problem is that when I'm creating a form in an ADP project connected
to SQL Server 2000, I get the following error: "You can't update the
record because another user or application has deleted it or changed
the value of its primary key."

The form has the following fields/textboxes MemberID (bound to
MemberID in the Members table), FirstName, LastName, Address, City,
State, Zip. When I create a form in MS Access in a local mdb file, it
works fine. I have read numerous posts trying to find a solution all
to no avail. Triggers, Resync command, you name it. I've come to the
point where I'm just running around in circles.

What is the simplest (and hopefully the most effctive) way of getting
my form to work? Any help in trying to sort this out would be greatly
appreciated. My experience in access and databases is somewhat limited
but I can assure you that I do need the two tables kept separate.
Thank you!

Reply to this Message

Vadim Rapp - 01 Dec 2003 01:08 GMT

m> I am trying to create a form that is based on two tables:
m> Members and MemberDetails.

m> The Members table has the following fields
m> - MemberID (primary key)
m> - FirstName
m> - LastName

m> The MemberDetails Table has the following fields
m> - MemberID (primary key)
m> - Address
m> - City
m> - State
m> - Zip

m> MemberDetails is linked to Members through the
m> MemberID field. My problem is that when I'm
m> creating a form in an ADP project connected to SQL
m> Server 2000, I get the following error: "You can't
m> update the record because another user or
m> application has deleted it or changed the value of
m> its primary key."

1. Is the error message coming out when you are _creating_ the form? or when
you are already working with it and trying to update the data?

2. please post the exact recordsource of the form

3. are there any triggers on these tables?

Vadim

----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com

Reply to this Message

moyrai - 03 Dec 2003 00:46 GMT

> 1. Is the error message coming out when you are _creating_ the form? or when
> you are already working with it and trying to update the data?
[quoted text clipped - 10 lines]
> 847-685-9073
> www.vadimrapp.com

1. The error comes up after I create the form. I can use it to view
existing data but when I try to add new data I get the error.

2. The record source is as follows:

SELECT dbo.Members.MemberID, dbo.Members.FirstName,
dbo.Members.LastName,
dbo.MemberDetails.Address, dbo.MemberDetails.City,
dbo.MemberDetails.State, dbo.MemberDetails.Zip,
FROM dbo.Members INNER JOIN dbo.MemberDetails
ON dbo.Members.MemberID = dbo.MemberDetails.MemberID

3. No triggers on either of the tables.

Reply to this Message

Vadim Rapp - 03 Dec 2003 05:41 GMT

m> 2. The record source is as follows:

m> SELECT dbo.Members.MemberID,
m> dbo.Members.FirstName, dbo.Members.LastName,
m> dbo.MemberDetails.Address,
m> dbo.MemberDetails.City,
m> dbo.MemberDetails.State,
m> dbo.MemberDetails.Zip, FROM dbo.Members INNER
m> JOIN dbo.MemberDetails
m> ON dbo.Members.MemberID =
m> dbo.MemberDetails.MemberID

Read http://support.microsoft.com/?kbid=295250

Your case, however, is more complex since both related columns are primary
keys for their tables. I think Access 2004 would figure out what to do, but
in Access 2002 you have to assign the value for the memberdetails' primary
key; you can do as follows:

1. include memberdetails.memberid in the recordsource
2. fill it in the beforeupdate event.

Vadim
----------------------------------------
Vadim Rapp Consulting
SQL, Access, VB Solutions
847-685-9073
www.vadimrapp.com

Reply to this Message