subform duplication records

[Betsy]

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?

 

[Steve Schapel]

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

 

[John W. Vinson]

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.

 

[Betsy]

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.

 

[John W. Vinson]

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.

 

[Betsy]

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!