 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Forms / March 2008subform duplication records
I am creating a new database and my subform is acting strange. I have three (3) tables Meetings MeetingID PurposeID TypeID Meetingtime MeetingDate MeetingNotes Contacts ContactID ContLastName ContFirstName contDept meetingLink LinkID MeetingID ContactID There can be many contacts at a meeting. So i made a form; frmMeetings from the meeting table AND a subform; sbfmMeetinglinks LinkID MeetingID ContactID (unbound combo box linked to tblcontacts) unbound (contlast) Contact last Name unbound (contfirst) Contact first Name unbound (contdept) contact Department The subform has an unbound combobox to look up the contacts and move their information into other unbound fields. Other than the combo box these unbound fields are only for the comfort of the person entering the information so that they can see the names, but the information is not stored anywhere as all I really need is the contactID. However, When the AfterUpdate pushes the info into the unbound boxes it also changes the unbound boxes of all the other records on the subform, so in datasheet view, except for the ContactID, it looks like you have the same person registered over and over. Is there any way I can get around this? Betsy, First of all, the ContactID combobox should not be unbound. It needs to be bound to the ContactID field from the MeetingLink table. Secondly, I'm not sure what you're doing with "the AfterUpdate pushes the info into the unbound boxes", but whatever it is, it doesn't sound like a good idea. You can achieve your purpose by either: - Make the subform bound to a query that includes both the MeetingLink table plus the Contacts table. - Set the Control Source of the unbound textboxes to use the Column(x) property of the ContactID combobox. Either of these approaches will work fine. For a discussion of these ideas, this article may be of interest: http://accesstips.datamanagementsolutions.biz/lookup.htm  SignatureSteve Schapel, Microsoft Access MVP > I am creating a new database and my subform is acting strange. > [quoted text clipped - 43 lines] > > Is there any way I can get around this? >However, When the AfterUpdate pushes the info into the unbound boxes it >also changes the unbound boxes of all the other records on the subform, so in >datasheet view, except for the ContactID, it looks like you have the same >person registered over and over. That's just the way unbound controls on a continuous form are designed to work! There APPEAR to be as many textboxes as there are rows, but in reality there is only one, displayed repeatedly; if you change its properties, all the repeats show the change. What you can do instead is base the subform on a Query left-joining the link table to the contacts table. Make the combo box *BOUND* to the ContactID in the link table (otherwise the user can't store anything!), and set the controls bound to the Contact table fields to Locked=Yes, Enabled=No so the user can see them for information but not edit them on this subform. SignatureJohn W. Vinson [MVP] Thank you both for your input. the reason I used only the meeting link table for my subform is that when I tried to use a query with Meeting links and Contacts, no matter how I joined it, it won't let me update or add records. I did put all of the fields in as well. No good. > >However, When the AfterUpdate pushes the info into the unbound boxes it > >also changes the unbound boxes of all the other records on the subform, so in [quoted text clipped - 11 lines] > controls bound to the Contact table fields to Locked=Yes, Enabled=No so the > user can see them for information but not edit them on this subform. >Thank you both for your input. the reason I used only the meeting link table >for my subform is that when I tried to use a query with Meeting links and >Contacts, no matter how I joined it, it won't let me update or add records. >I did put all of the fields in as well. No good. Do you have a Relationship defined between the Meeting and Contacts tables? Unless the linking field is from the Primary Key of Contacts to an indexed foreign key in Meeting links, it won't be updateable. You will also need to use a LEFT JOIN (show all records in Meetings and matching records in Contacts), and be sure to include BOTH ContactID fields (from the two tables) in the query. SignatureJohn W. Vinson [MVP] I just figured out my problem! I neglected to set a primary key in the contacts table! I really feel stupid now! I must have looked at that table design a hundred times in two days! > >Thank you both for your input. the reason I used only the meeting link table > >for my subform is that when I tried to use a query with Meeting links and [quoted text clipped - 7 lines] > Contacts), and be sure to include BOTH ContactID fields (from the two tables) > in the query. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.