If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
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 |
#2
|
|||
|
|||
Combo box won't update many side subform records
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 |
Thread Tools | |
Display Modes | |
|
|