Solved Clear a numeric field to Null

[Babycat]

Hi all,

I want to clear CurrentID (long) to null (nothing) in table current logged User
I thought it was simple SQL.
UPDATE L_TBLUSER_CURRENT SET L_TBLUSER_CURRENT.CurrentID = Null;

But I got error: "You tried to assign the NULL value to a variable that is not a Variant data type"

My purpose is when I read back Dlookup("CurrentID", "L_TBLUSER_CURRENT")& "" = "" then msgbox "No current UserID"

Any help please?

 

[tvanstiphout]

The field is currently Required.
Design the table, select the field, and set Required to false.

 

[Babycat]

The field is currently Required.
Design the table, select the field, and set Required to false.

Yeah, it's exactly the point. My careless

 

[Babycat]

If those are the only three fields in the table, it makes no sense for CurrentID to be null. Therefore, it should remain as required but you should rethink your logic. Maybe you need to delete the row rather than setting the FK to null. If you change the CurrentID to be not required as was suggested, you essentially create an orphan record that will hang around forever not attached to any parent record.

Not all foreign keys should be required although most probably should. For example for a vehicle pool inventory, the vehicle record will have a FK to the employee table so you know who is currently responsible for the vehicle. Since a vehicle might just be in inventory, the FK to employee should not be marked as required. It's default should be null (as it should be for ALL FKs) though. Then when you remove the employeeID and reset the field to null, the vehicle is returned to inventory. That doesn't appear to be your situation given the above example.

Thank Pat,

Appreciated your suggestions. Yes, I understand about orphan record, and I have been trying to manage them like your guideline. However, in this case there is only one row in this table (this table locates in FE) to tell current logged-in user in this machine.

 

[Babycat]

Hi Pat

Yes, that is more reasonable. I forgot about using Dcount...

Code to set current active User

Public Sub User_SetCurrentActiveUserID(loggedID As Long, Remember As Boolean)
On Error GoTo ErrorHandler
Dim rs As DAO.Recordset
Dim DeleteSQL As String
   
    'Just clear table first
    DeleteSQL = "DELETE * FROM L_TBLUSER_CURRENT"
    DoCmd.SetWarnings False
    DoCmd.RunSQL DeleteSQL
    DoCmd.SetWarnings True

    Set rs = CurrentDb.OpenRecordset("L_TBLUSER_CURRENT")
        rs.AddNew
        rs!CurrentID = loggedID            'Save active user ID to table L_TBLUSER_CURRENT
        rs!Remember = Remember
        rs.Update
    rs.Close
    Set rs = Nothing
       
UserExitSF:
    Exit Sub
ErrorHandler:
    MsgBox "There is an error occurred: " & Err.Number & ": " & Err.description, vbOKOnly + vbCritical
    Resume Next
End Sub

Code to check if any user logged in

    If DCount("*", "L_TBLUSER_CURRENT") > 0 Then
        ......
    End If

 

[Babycat]

Hi Pat

Current user log (on FE) is only be cleared when user click on "Logout" button.
Without logging out, next time they open the app, it will bypass login window (assume Remember me is ticked).
So when user starting application, on AutoExec code, I do check:

    If DCount("*", "L_TBLUSER_CURRENT") > 0 Then 'Not logged out from last session
        if Remember then
            'Open Mainform
        else
            'LoginForm
        end if
    else
        'LoginForm
    End If

Is that OK? I do few simple tests and it seems nothing wrong...

PS: Each user has their own FE on their machine, BE is shared on LAN

 

[Babycat]

What you are doing makes no sense to me. Is this table in the FE or the BE? Do you have a separate log in table that records each login/logout? Is that in the FE or the BE?

"L_TBLUSER_CURRENT" is on FE, and I also have a separate log in table that records users's login/logout on BE. "Remember me" is feature required by customer that I can avoid. Since each user has their own computer that dont share to others, they said they want "Remember me".
Anyway, thank you very much for your suggestion.