Foreign Key not updating

  • Thread starter Thread starter abefuzzleduser2
  • Start date Start date
A

abefuzzleduser2

Access 2000 with linked tables in SQL Server 2000. I have a form and a
datasheet subform based on a table. This was not related to the parent
table before and I was changing nvarchars to varchars and removed
nulls. I noticed some Foreign keys were NULL? I copied data to new
table and deleted the null FK. I then changed POID to not null. POID
is PK in tblPO and FK in items. I have verified the SUBFORM is using
POID for parent and child. I see the new row asterick on subform. The
subform only has Item, Description, Qty, Code (DROPdown list) and
resolved checkbox. The parent data already has been entered but every
time I try to add a new data in subform I get error "ODBC-- call
failed. the cannot insert NULL into column POID, column does not allow
nulls. insert fails"? one other strange problem I started getting error
"The value you entered isn't valid for this field" when I start typing
in the subform?? Maybe that is part of the problem???
thanks

CREATE TABLE [dbo].[tblPODiscrepancyItems] (
[PODiscID] [int] NOT NULL ,
[POID] [int] NOT NULL ,
[Item] [nvarchar] (50) NULL ,
[Description] [nvarchar] (50) NULL ,
[Qty] [int] NOT NULL ,
Code: 
 [nvarchar] (50)  NULL ,
[Resolved] [bit] NOT NULL ,
]
GO

CREATE TABLE [dbo].[tblPurchaseOrder] (
[POID] [int] IDENTITY (1, 1) NOT NULL ,
[Date] [datetime] NULL ,
[DocNumber] [nvarchar] (20)  NULL ,
[CompanyName] [nvarchar] (50)  NULL ,
....
 
I did not change anything in access mdb and I dont have any default
values, validation etc on any fields. I did make some changes in
backend.

I recreated the form with plain text boxes but I get the same errors.
 
I did not change anything in access mdb and I dont have any default
values, validation etc on any fields. I dont use any validation,
defaults etc. I did make some changes in backend which I have tried to
reset. I changed all the Items table to nulls and removed the default
value (back to what it was). I was able to add add data directly to
Items table

I created a new sub form based on a query with 5 plain text boxes but I
get the same errors. It is linked to main form like others on POID.
Should not have any defaults or validation? Item, Description, Qty,
Code and resolved checkbox.

Originally used
Item, Description, Qty, Code (static DROPdown list) and resolved
(checkbox)

helP I am stuck and out of ideas??
 
I have verified the SUBFORM is using
POID for parent and child. I see the new row asterick on subform. The
subform only has Item, Description, Qty, Code (DROPdown list) and
resolved checkbox. The parent data already has been entered but every
time I try to add a new data in subform I get error "ODBC-- call
failed. the cannot insert NULL into column POID, column does not allow
nulls. insert fails"?
Click to expand...

One possible problem might be that the Forms don't "realize" that
you've changed the properties of the master/child link fields. Try
opening each form's Recordsource; verify that it's using the correct
field; remove the master/child link field properties and re-add them.

Compact the database after you're done and see if this helps!

John W. Vinson[MVP]
 
Back
Top