HELP! My form is busted.

[Sue]

I have several related tables within a database I'm using to track contact
information.

Initially I had a text field for "business name", but I noted that this left
room for data entry errors. (I found I'd misspelled in one instance, and in
a second I'd abbreaviated the company name @ one location and spelled it out
in full in another.) Therefore I decided to develop a second table,
(tblBusiness) with 2 fields - an autonumber for the primary key and a text
field for the name of the business. I then added a new field to my
tblContacts - BusinessName. I set the data type for this field to "number"
so that it could be used as the foreign key, went to "lookup", and made a
combo box -

Display Control - combo box
Row Source Type - table/query
Row Source - SELECT tblBusinessName.Business_ID,
tblBusinessName.BusinessName FROM tblBusinessName ORDER BY
tblBusinessName.BusinessName;
Bound Column - 1
Column Count - 2
Column Widths - 0";2"
Limit to List - Yes

I then set the relationships between the 2 tables - tblBusinessName's
primary key -> tblContacts's foreign key - enforcing referential integrity.
I went back to the form I'd been using with success for data entry, deleted
the initial text field that I'd been using for data entry, and put in a
combo box (using the wizard) to enable me to select the business name. I can
click on the drop-down arrow and view business names in the list.

The combo box works in the table and in the form (i.e. when I drop down the
list, I am able to see the business names and, while in the table, can add a
new contact). But the bizarre thing is that I can no longer add a contact
while in my form. I get the following error message:
"You cannot add or change a record because a related record is required in
table 'tblBusinessName'." I got a bit miffed and decided to blow up and
recreate my form, figuring Access might need to be reeducated about the
source... No help.

My husband suspects that the problem is that I enforced referential
integrity. Seems to me that there may be another answer and that I really DO
want referential integrity enforced.

Ideas, please? Hari kari isn't an attractive option for me.

Thanks so much.

 

[tina]

note: when you want to add additional comments to your post, recommend you
"reply" to the post rather than starting a new thread as you did here.
multiple threads are harder for you to keep track of, and also make more
work for anyone who responds - they may not see both threads and so are
missing part of the information you provide, and may spend time working on
possible solutions that have already been explored in the post they *didn't*
see.

hth

 

[Sue]

Thanks - I appreciate the tip.

note: when you want to add additional comments to your post, recommend
you
"reply" to the post rather than starting a new thread as you did here.
multiple threads are harder for you to keep track of, and also make more
work for anyone who responds - they may not see both threads and so are
missing part of the information you provide, and may spend time working on
possible solutions that have already been explored in the post they
*didn't*
see.

hth