By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,846 Members | 2,107 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,846 IT Pros & Developers. It's quick & easy.

Foreign Key not updating

P: n/a
we use 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 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??

Feb 2 '06 #1
Share this Question
Share on Google+
1 Reply


P: n/a
the same subform works on a different form? Updates FK just fine. The
subform that works uses an outer join from main form. THe one that
does not work uses the non-outer side for main form. Subforms are
setup the same.

Feb 2 '06 #2

This discussion thread is closed

Replies have been disabled for this discussion.