Record Source S/B different when doing an add vs. edit mode

[BugZap13]

I have a form that displays information from a parent table and a 1 to 1 child table. In the forms Record Source I am using a select that pulls from both tables as shown below.

SELECT Members.SSNO, Members.FIRSTNAME, Members.LASTNAME, 
Members.Address2, Members.CITY, Members.STATE, Members.ZIPCODE,
Retirees.* 
FROM Retirees LEFT JOIN Members ON Retirees.SocialSecurityNumber = Members.SSNO;

When in normal edit mode all works as expected, I can change the members fields and the retirees information. When trying to do an add to the retiree table, it is also trying to insert a member record and causing a duplicate key. Now before adding the retiree, the member record must already exist. So basically when doing an add, all I need is the "SELECT * FROM Retirees" and dummy fields for the members information. Is there a way to change the record source when in add mode or maybe to designate that the "Members" fields are not to be sent with the update?

TIA
Mark

 

[dhookom]

I'm not sure I would have two tables since the relationship is 1 to 1. However, have you considered a main form with a record source of Members and a Retirees subform?

Duane
Hook'D on Access
MS Access MVP

 

[BugZap13]

Unfortunately Access will not allow what I am trying to do and you are right in that Access would like a main/subform configuration. I am getting around it by only using the Retirees table in the forms record source. I then made all the Member table controls unbound. In the forms "On Current" I go after the Members table and populate the unbound controls with the Members fields. Then in the forms "After Update" I pick up the Members values from the unbound controls and update them to the Members table. The problem I have now is if the only thing changed is an unbound field, the "After Update" event is not fired. I am now playing with each unbound controls "On Dirty" event to see what needs to be done to make the form think it has to update the Retirees table and thus fire the "After Update" event.

 

[dhookom]

Aren't individuals a member and then become retired? This would suggest your LEFT JOIN might be a RIGHT JOIN.

I think you are making this way to hard but I don't understand your business process.

Duane
Hook'D on Access
MS Access MVP