Control can't be edited error

[Mike]

Hi,

I'm working with my first database and can't figure out something. I
downloaded the "Contact Management" database template from MS. For the most
part, it will do what I need for the short term. I am trying to make some
minor changes and am stuck on something.
I created an additional table called "Counselors". In that table are two
fields, a CounselorID primary key and a Counselors field. I have six
counselors currently and each one has an autonumber associated with them.
I created a field in the "Contacts" table called "CounselorID", and created
a relationship between the "Contacts" table and "Counselors" table with the
common field "CounselorID".
When I go into design mode on the "Contacts" table to add the "Counselors"
field, so that I can select the counselors from the combo box dropdown, I get
an error : Control can't be edited; it's bound to the expression
'Counselors!Counselors'.
I can't figure out what I am doing wrong. I don't want to "edit" this, but
just pick a counselor from the dropdown to go in the field. The list of
counselors show up, but when I try to select one, I get the error.

Any advice? Thanks!

Mike

 

[Evi]

Are you assigning a counselor to a contact? Or are you saying that the
Contact is a counselor?

Remember, your combo box has to based on the Counselor table, not on the
Contact table.
It should have 2 fields when you create it, CouselorID and Contact.
The Contact Table should not have CounselorID as its Primary Key.
Your form should be based on the Contact Table. If it's based on a query, it
should not contain CounselorID from the Counselor table, it should contain
CounselorID from the ContactTable.

Just check and see if this is what you have. If not, let us know. I can
remember doing something like this when I started out and finding it really
infuriating.
Evi

 

[Mike]

Evi,

Thanks for the response. I have a Counselor's table with a counselor ID
(Primary Key) and a counselor field. My goal would be for each counselor,
when they meet with a customer, could indicate that by selecting their name.
Not necessarily that they are assigned to that client, as more than one
counselor could work with a client.
Other comments inline below.

Are you assigning a counselor to a contact? Or are you saying that the
Contact is a counselor?

Remember, your combo box has to based on the Counselor table, not on the
Contact table.
It should have 2 fields when you create it, CouselorID and Contact.
The Contact Table should not have CounselorID as its Primary Key.
Your form should be based on the Contact Table. If it's based on a query, it
should not contain CounselorID from the Counselor table, it should contain
CounselorID from the ContactTable.

The primary key for the contacts table is ContactID. I set up CounselorID
as a field in the contacts table, to establish a relationship. Is that
wrong?

I went into the contacts form and added a combo box and in the properties of
that, selected my counselors field from the counselors table as the control
source. It lets me see the dropdown list of counselors, but when I select
one, I get the error. I just can't figure out how to set up the field so
that I can select from the dropdown a counselor and have it populate that
field. Does that make sense?

 

[Evi]

Then you definitely need a third table with a Primary Key field then
CounselorID linking to the Counselors table and CustomerID linking to the
CustomerID table. It would also contain eg the date of the meeting and any
other info that concerned only that conselor with that customer.
In your form, based on this third table, use a combo boxe based on the
Counselor table and one based on the customer table to input the meeting.

Evi

 

[Mike]

Thanks Evi, I will try it.

Mike

Then you definitely need a third table with a Primary Key field then
CounselorID linking to the Counselors table and CustomerID linking to the
CustomerID table. It would also contain eg the date of the meeting and any
other info that concerned only that conselor with that customer.
In your form, based on this third table, use a combo boxe based on the
Counselor table and one based on the customer table to input the meeting.

Evi