Contacts change when I add record

G

Guest

I just discovered a problem with my database, which I thought was in good
shape. Does it ever end? When I add a new contact to one table, the contact
names in another tbale change. I will provide as many details as I can...
please bear with me. When you respond, please keep in mind that I am not a
programmer and it will help considerably if you spell things out in detail
(such as exactly WHERE to enter or change certain code). Thank you in
advance!

My database has three main tables and many forms and reports. The two tables
relative to my problem are "ContactsNew" and "ProjectInfo". The ContactsNew
table has ten fields, the first of which is an autonumber field (field name
is "ID"), which is the primary key. The second field is "Last" (last name),
the third is "First" (first name), and the fourth is "Company" (company name).

The "ProjectInfo" table has fields that allow you to designate specific
contacts for each project. For example, the field "ContactID1" is a combo box
with a row source of:

SELECT [ContactsNew].[ID], [ContactsNew].[Last], [ContactsNew].[First],
[ContactsNew].[Company] FROM ContactsNew ORDER BY [ContactsNew].[Last];

The bound column is "0" (ID field)

All is good up to this point.

On the "ProjectInfo" form, there is a combo box ("CBContactID1") that allows
you to select the contact for that project. The control source of the combo
box is "ContactID1", and the Row Source is:

SELECT [ContactsNew].[ID], [ContactsNew].[Last], [ContactsNew].[First],
[ContactsNew].[Company] FROM ContactsNew ORDER BY [ContactsNew].[Last];

The bound column is "0" (ID field)

(the same as the Table combo box)

Since I wanted to DISPLAY both the last name (Last) and first name (First)
after the user has made a selection, I simply added a text box next to the
combo box to accomplish this. The text box control source is:

=[CBContactID1].Column(2) & " " & [CBContactID1].Column(1)

This works beautifully... UNTIL I add another contact to the ContactsNew
table. When I do that, the displayed names in the ProjectInfo form change!
It's an alphabetic phenomenon, based on the last name. For example, if I add
a contact with a last name of "Jones", all of the contacts in the ProjectInfo
table with last names starting with "J" or later will change, and all those
prior to Jones, alphabetically, will remain the same.

The "ID" associated with each contact does not change, and since these
controls are bound by the ID field, I don't understand why they are changing.

Please help! Thank you.
 
G

Guest

Look in your tables and see if the actual data in the tables is being
duplicated, if not it is your query or queries that is wrong. Run the queries
in query designer to see what is happening.

-Dorian

DiHo said:
I just discovered a problem with my database, which I thought was in good
shape. Does it ever end? When I add a new contact to one table, the contact
names in another tbale change. I will provide as many details as I can...
please bear with me. When you respond, please keep in mind that I am not a
programmer and it will help considerably if you spell things out in detail
(such as exactly WHERE to enter or change certain code). Thank you in
advance!

My database has three main tables and many forms and reports. The two tables
relative to my problem are "ContactsNew" and "ProjectInfo". The ContactsNew
table has ten fields, the first of which is an autonumber field (field name
is "ID"), which is the primary key. The second field is "Last" (last name),
the third is "First" (first name), and the fourth is "Company" (company name).

The "ProjectInfo" table has fields that allow you to designate specific
contacts for each project. For example, the field "ContactID1" is a combo box
with a row source of:

SELECT [ContactsNew].[ID], [ContactsNew].[Last], [ContactsNew].[First],
[ContactsNew].[Company] FROM ContactsNew ORDER BY [ContactsNew].[Last];

The bound column is "0" (ID field)

All is good up to this point.

On the "ProjectInfo" form, there is a combo box ("CBContactID1") that allows
you to select the contact for that project. The control source of the combo
box is "ContactID1", and the Row Source is:

SELECT [ContactsNew].[ID], [ContactsNew].[Last], [ContactsNew].[First],
[ContactsNew].[Company] FROM ContactsNew ORDER BY [ContactsNew].[Last];

The bound column is "0" (ID field)

(the same as the Table combo box)

Since I wanted to DISPLAY both the last name (Last) and first name (First)
after the user has made a selection, I simply added a text box next to the
combo box to accomplish this. The text box control source is:

=[CBContactID1].Column(2) & " " & [CBContactID1].Column(1)

This works beautifully... UNTIL I add another contact to the ContactsNew
table. When I do that, the displayed names in the ProjectInfo form change!
It's an alphabetic phenomenon, based on the last name. For example, if I add
a contact with a last name of "Jones", all of the contacts in the ProjectInfo
table with last names starting with "J" or later will change, and all those
prior to Jones, alphabetically, will remain the same.

The "ID" associated with each contact does not change, and since these
controls are bound by the ID field, I don't understand why they are changing.

Please help! Thank you.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top