Removing a foreign key value ??

[ksor]

I have a table with a foreign key field.
The field is related to a key-field with automatic numbering.
I can add records with NO VALUE in that foreig key field - and that's OK, because I don't know the value yet.

BUT when tha value is first added, how can I then remove it again ?

I can't set it equal to 0, Null, Nothing - what can I do to "go back" to the situation where "the value is not yet know" ?????

[davegri]

You should be able to assign it a number that exists in the foreign table, and ONLY a number that exists in the foreign table if referential integrity is enforced. If you want to do otherwise, you have a design problem.

[June7]

davegri is right,

In other words, if you can create the record with no value in that 'foreign key' field that means you are not enforcing referential integrity and then you should be able to delete any value that is entered and "go back" which means the field is Null. A field will have a value or an empty string or be Null. What else is there?

[ksor]

1) I have checked that "Enforcing ref. integrity" and it IS chackmarked !
2) I have just added a record with NO VALUE in that foreign key field !

That's just by the book I think and it is NO problem for the user that it is working like that !

If I remove the value directly in the table - it works fine - what value is then inserted in the field - that's the one I need ;-))

I think the problem is when "removing" the value in the foreign key field - HStedID - by code, how should I do it ?

HStedID = ""
HStedID = Null
HStedID = Nothing

NON of these are allowed - a error message is shown for all of them !

I can't use3 HStedID = 0 because then it will check for ref. integrity and I have no key equal to 0.

[June7]

Okay, stand corrected. Just did some testing. Referential Integrity alone does not prevent a record entry in child table without parent ID foreign key. What referential integrity assures is that a value cannot be entered into foreign key field that is not in the parent table (or a 'lookup' table). Can't enter a value in the foreign key field unless there is already corresponding record in the parent table. So, unfortunately, I do have an orphan in the child table.

I have no problem setting the field back to Null with code, like:

Me.fieldname = Null

Forms!formname.fieldname = Null

[ksor]

Yeah, but my KEY is a AUTONUMBERING field so the foreign key HAS TO BE a "long" !

I beleave you can do what you do because your KEY-field is a TEXT and then the foreign key field can be set equal to "" or Null - right ?

[June7]

No, PK is autonumber and FK is long. I had no problem setting FK back to Null.

If you want to provide db for analysis, follow instructions at bottom of my post.

[ksor]

I have found out now - combination of combobox setup fram earlier tests and wrong JOIN-types !

I got it working now !