INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

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

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

(OP)
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.

CODE -->

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

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

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

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

(OP)
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.

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

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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close