Combo box won't update many side subform records

Hi Folks

I have a strange problem that manifested yesterday in a database that has
been working well for 3 years. You cannot add a new record to the sub form
without getting a a current key must match the join key error messge.

There are 3 tables
PropsTBL PK PropID
OptionsTBL Bridging table with PropID and OwnerID as Foreign PKs
OwnerTBL OwnerID

The main form is based on PropsTBL
the subform is based on a query containing OwnerTBL and OptionsTBL

The ownerID is updated on the subform via a combo box - users either select
an existing owner or through the notinlist event adds new owner details.

The combo box is based on a query using OwnerTBL and hides the OwnerID, but
shows the owners name and address so that the user can make a selection from
the list and update the ownerID on the subform which in turn has been
updating the OptionsTBL ownerID field

What happens now is that when a new property record is added on the main form
and you try to add ownership details on the subform I have noticed the
ownerID field seems to be the ownerID field on the ownerTBL and not the
Foreign OwnerID PK field on the optionsTBL. and so is essentially adding a
new record to the ownerTBL and so the 2 ownerID fields never match and in
turn seems to generate the error.

However if you recreate a basic version of the same form using the wizard for
instance, and update the ownership fields you can add as many records against
the property as you need to and this is how the form was working before.

I have posted the sql for the query on the subform

SELECT TBLOwnerOptions.OwnerID, TBLOwnerOptions.[PropID], TBLOwnerOptions.
CurrentOwner, TBLOwnerOptions.DateNewOwner, TBLOwnerOptions.Agent,
TBLOwnerOptions.OwnershipDate, TBLOwnersDetails.Title, TBLOwnersDetails.
FirstName, TBLOwnersDetails.MiddleName, TBLOwnersDetails.Surname,
TBLOwnersDetails.CompanyContact, TBLOwnersDetails.salutation,
TBLOwnersDetails.AddressOwner, TBLOwnersDetails.[Post Code- Owner],
TBLOwnersDetails.Country, TBLOwnersDetails.Correspondence, TBLOwnersDetails.
DayTel, TBLOwnersDetails.HomeTel, TBLOwnersDetails.FaxNos, TBLOwnersDetails.
Mobile, TBLOwnersDetails.email, TBLOwnersDetails.OwnerStatus
FROM TBLOwnersDetails INNER JOIN TBLOwnerOptions ON TBLOwnersDetails.OwnerID
= TBLOwnerOptions.OwnerID;

Here is the sql from the combo box the bound column is OwnerID
SELECT [TBLOwnersDetails].[OwnerID], [TBLOwnersDetails].[Surname],
[TBLOwnersDetails].[CompanyContact], [TBLOwnersDetails].[AddressOwner]
FROM TBLOwnersDetails;

I cannot understand why this should suddenly stop working on this form but
work on the wizard created test form. I have compacted and repaired away but
this has not had any affect I have taken out all the requery vba I had to see
if that made a difference but nothing does.

Any ideas anyone. Hopefully you are all not too confused by my scenario.
This forums help is always appreciated.
Cheers
Ceebaby

--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1

Hi Folks

I have found out what was wrong, I had a default value set on a field within
the subform. Got rid of it and everything works properly now.
Cheers
Ceebaby

Ceebaby wrote:
Hi Folks

I have a strange problem that manifested yesterday in a database that has
been working well for 3 years. You cannot add a new record to the sub form
without getting a a current key must match the join key error messge.

There are 3 tables
PropsTBL PK PropID
OptionsTBL Bridging table with PropID and OwnerID as Foreign PKs
OwnerTBL OwnerID

The main form is based on PropsTBL
the subform is based on a query containing OwnerTBL and OptionsTBL

The ownerID is updated on the subform via a combo box - users either select
an existing owner or through the notinlist event adds new owner details.

The combo box is based on a query using OwnerTBL and hides the OwnerID, but
shows the owners name and address so that the user can make a selection from
the list and update the ownerID on the subform which in turn has been
updating the OptionsTBL ownerID field

What happens now is that when a new property record is added on the main form
and you try to add ownership details on the subform I have noticed the
ownerID field seems to be the ownerID field on the ownerTBL and not the
Foreign OwnerID PK field on the optionsTBL. and so is essentially adding a
new record to the ownerTBL and so the 2 ownerID fields never match and in
turn seems to generate the error.

However if you recreate a basic version of the same form using the wizard for
instance, and update the ownership fields you can add as many records against
the property as you need to and this is how the form was working before.

I have posted the sql for the query on the subform

SELECT TBLOwnerOptions.OwnerID, TBLOwnerOptions.[PropID], TBLOwnerOptions.
CurrentOwner, TBLOwnerOptions.DateNewOwner, TBLOwnerOptions.Agent,
TBLOwnerOptions.OwnershipDate, TBLOwnersDetails.Title, TBLOwnersDetails.
FirstName, TBLOwnersDetails.MiddleName, TBLOwnersDetails.Surname,
TBLOwnersDetails.CompanyContact, TBLOwnersDetails.salutation,
TBLOwnersDetails.AddressOwner, TBLOwnersDetails.[Post Code- Owner],
TBLOwnersDetails.Country, TBLOwnersDetails.Correspondence, TBLOwnersDetails.
DayTel, TBLOwnersDetails.HomeTel, TBLOwnersDetails.FaxNos, TBLOwnersDetails.
Mobile, TBLOwnersDetails.email, TBLOwnersDetails.OwnerStatus
FROM TBLOwnersDetails INNER JOIN TBLOwnerOptions ON TBLOwnersDetails.OwnerID
= TBLOwnerOptions.OwnerID;

Here is the sql from the combo box the bound column is OwnerID
SELECT [TBLOwnersDetails].[OwnerID], [TBLOwnersDetails].[Surname],
[TBLOwnersDetails].[CompanyContact], [TBLOwnersDetails].[AddressOwner]
FROM TBLOwnersDetails;

I cannot understand why this should suddenly stop working on this form but
work on the wizard created test form. I have compacted and repaired away but
this has not had any affect I have taken out all the requery vba I had to see
if that made a difference but nothing does.

Any ideas anyone. Hopefully you are all not too confused by my scenario.
This forums help is always appreciated.
Cheers
Ceebaby


--
Ceebaby

Trying to be great at Access

Message posted via AccessMonster.com
http://www.accessmonster.com/Uwe/For...forms/200808/1