One additional discovery relating to this issue.

[Sue]

One additional bit of info - on advice of my mentor, I ditched the lookups
in the table - he indicated that might be an issue. I then made lookups in
the form itself, and I can see the lists - no problem there. But I still
can't add a record (same error messages as below) - UNTIL I uncheck "enforce
referential integrity" on the relationship line between tblBusiness &
tblContact.

I wish I wish I wish I could enforce referential integrity.

Ideas?

 

[tina]

yes, you made a very wise move in removing the Lookup from tblContacts (if
you've any remaining doubt, see
http://www.mvps.org/access/lookupfields.htm), though that was not a
causative factor in the issue you're facing.

and yes, you *do* want to enforce referential integrity. quite simply, when
you want to enter a *new* business in a Contacts record, you need to first
enter the business name in tblBusinessName; that is, of course, the purpose
of enforcing referential integrity - to prevent entering a value in a
"child" table that does not exist in the "parent" table. since
tblBusinessName is very simple, it's easy enough to do this via VBA code,
from within the Contacts data entry form, and on-the-fly. try adding the
following code to the combo box control's NotInList event procedure, as

If Msgbox("Add " & """" & NewData & """" _
& " to the droplist?", _
vbYesNo + vbDefaultButton2) = vbYes Then
CurrentDb.Execute "INSERT INTO " _
& "tblBusinessName ( BusinessName ) " _
& "SELECT '" & NewData & "'", _
vbFailOnError
Response = acDataErrAdded
Else
Me!ComboboxControlName.Undo
Me!ComboboxControlName.Dropdown
Response = acDataErrContinue
End If

replace "ComboboxControlName" with the correct name of the combo box
control, of course. if you don't know how to create an event procedure, go
to http://home.att.net/~california.db/instructions.html and click on the
CreateEventProcedure link.

hth

 

[Sue]

My mentor helped me figure this out - I had neglected to delete the "0"
default value in one of the foreign key in tblContacts.
Microsoft - what WERE you thinking?

Thanks, all.