You can programmatically set the foreign key field to Null, so there must be
another factor affecting your issue. Were you attempting to do that in a
multi-table recordset, rather than directly on the table?
This example sets the foreign key to null in the first record:
Function Set2Null()
Dim rs As DAO.Recordset
Set rs = DBEngine(0)(0).OpenRecordset("SELECT * FROM Table2;")
rs.Edit
rs!FieldMyID = Null
rs.Update
rs.Close
Set rs = Nothing
End Function
If you want to set the foreign key field to Null when the main table record
is deleted, JET 4 supports a little known feature called "Cascade to Null".
It is not shown in the interface, and can be created with ADOX only.
Example:
Sub CreateKeyAdox()
Dim cat As New ADOX.Catalog
Dim tbl As ADOX.Table
Dim ky As New ADOX.Key
Set cat.ActiveConnection = CurrentProject.Connection
Set tbl = cat.Tables("tblAdoxBooking")
'Create as foreign key to tblAdoxContractor.ContractorID
With ky
.Type = adKeyForeign
.Name = "tblAdoxContractortblAdoxBooking"
.RelatedTable = "tblAdoxContractor"
.Columns.Append "ContractorID" 'Just one field.
.Columns("ContractorID").RelatedColumn = "ContractorID"
.DeleteRule = adRISetNull 'Cascade to Null on delete.
End With
tbl.Keys.Append ky
Set ky = Nothing
Set tbl = Nothing
Set cat = Nothing
Debug.Print "Key created."
End Sub
Of course, in the vast majority of relationships, the foreign key's Required
field should be set to Yes so you do not get orphaned records. Sometimes
they are helpful though.
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users -
http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.
"GGerard" <gg*****@nbnet.nb.ca> wrote in message
news:%l*********************@ursa-nb00s0.nbnet.nb.ca...
Hello
I am working with Access 2000
I have two tables joined on a one to many relationship between two fields:
Table1:FieldID (one) is joined to Table2:FieldMyID (many)
Field Properties setting:
FieldID : Field Size - Long Integer
New Values - Increment
Indexed - Yes (No Duplicates)
Data Type - AutoNumber
FieldMyID : Field Size - Long Integer
Default Value -
Required - No
Indexed - Yes (Duplicates OK)
Data Type - Number
When I add a record to Table2, FieldMyID must have a related record in
FieldID of Table1
However, Access 2000 will allow me to add a new record to Table2 without
having a related record in FieldID
of Table1 if I don't enter any data in FieldMyID (leave the FieldMyID
Null............. I think) and will allow me to open
Table2 and erase the content of FieldMyID manually.
Sometimes I need to erase the content of FieldMyID through codes and leave
the field blank but when I do that
I get an error message saying that a related record is required in Table1.
I have tried to set FieldMyID to Null, zero length string "" , and zero
but
nothing seems to work.
Why is Access 2000 allowing me to leave FieldMyID empty on a new record or
erase the content of FieldMyID manually but not allowing me to erase the
content of FieldMyID through codes?
Could it be that when the content of a field is erased manually it is set
to
something other than Null?
