How can I assign a Null value to a Foreign Key field?

[MatthewGrace]

Hello all, thank you for taking your time to consider helping me.

I have an Orders table. It contains a number field which is a foreign key called ShipmentFK.
When Orders table gets a new record, this foreign key starts out blank. It's "Default Value" is actually listed as Null in the table Design View.



In a couple day or a couple weeks, we'll associate the order with a ShipmentFK. No problems so far.

Let's say we want to remove that order from a shipment. I want ShipmentFK to go back to being blank. Back to being Null. I tried to erase the foreign key by pressing the backspace key in the datasheet subform. Access complains:


The gist of it is: how in blazes to do I get that field back to Null? I used to do it programmatically, but Microsoft broke my SQL Update command and the impression I get is that it won't be fixed until Sunday (for Office 365). Is there an easy workaround?
Thank you so much for any help,
matt

[ssanfu]

You didn't post your code, but what the error is saying is that you are trying to assign a NULL to a number type field which cannot hold a NULL. Only a Variant type or a String type can be NULL.

One way is to set the number type field to EMPTY.
("AccidenteID_FK" is a Long Integer)

Public Sub SetNumberToEmpty()
    Dim d As DAO.Database
    Dim r As DAO.Recordset
    Dim sSQL As String

    'open a record set
    sSQL = "SELECT tblVehicles.AccidenteID_FK"
    sSQL = sSQL & " FROM tblVehicles"
    sSQL = sSQL & " WHERE tblVehicles.VehicleID_PK = 1;"
    Set d = CurrentDb

    Set r = d.OpenRecordset(sSQL)
    If Not r.BOF And Not r.EOF Then   '<<-- test for records
        r.Edit
        r("AccidenteID_FK") = Empty   '<<--set field to empty
        r.Update
    End If
    
    'clean up
    r.Close
    Set r = Nothing
    Set d = Nothing

End Sub

[MatthewGrace]

Ideally, I wouldn't use code. As soon as MS releases the 11/24/19 patch, everything should start working again. Fingers crossed.

What I demonstrated above is what happens if you try to eliminate the number in a Foreign Key field (Number datatype) by pressing the backspace key in a cell. No code, just deleting text with the keyboard.

And what's so funny about this is that these same foreign keys actually start Null by default! They can sit Null forever, but if one day you should ever decide to populate the foreign key: it's there for life. I cannot highlight the number and delete it. It complains saying it cannot be Null. Now, programmatically I can make it Null again, but I'm speaking about foregoing programming and just deleting the contents of the cell.

Ssanfu: Thank you for your code. I can see why this should work. Update queries right now are broken from the November 12th MS Office security patch. Your code works differently. I will copy/paste it and try.

Thank you!
matt

[pbaldy]

Regarding

Update queries right now are broken from the November 12th MS Office security patch.

there are fixes available for some versions. This page is updated as they become available. You can also uninstall the offending update, which differs depending on version:

https://support.office.com/en-us/art...3-f21636caedec