Solved Clear a numeric field to Null (1 Viewer)

[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.

 

[Pat Hartman]

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.

 

[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.

 

[Pat Hartman]

I would still delete the record rather than making the FK null. It is logically more understandable. So, instead of checking for null, can use a dCount to see if anyone else is logged in.

To make the delete process work, you should change the autonumber to long and just use a value of 1 for the PK. You can even set rules on the table to not allow a value other than 1 for the PK to prevent other records from being added.

 

[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

 

[Pat Hartman]

If you are forcing the login by calling the above sub in the first form that opens, then there is always someone logged in so having to check for a logged in user seems moot. Unless there is something going on that we are not aware of such as - the table is in the BE rather than the FE and you are trying to restrict use to a single user. In that case, you must also force a logout. That is easiest if instead of closing your login form, you just set its visible property to no. That way, it is the first form to open and since it remains open but hidden, the last form to close. So the unload event can delete the log record.

Not sure why you would want to restrict to a single user. As long as the app is split and every user has his own personal copy of the FE, sharing should not be a problem. You can never share using DropBox or ANY OTHER cloud folder. PERIOD. This is way too dangerous and there is no way to get around it.

 

[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

 

[Pat Hartman]

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?

The code you are posting is out of context so I can't tell what form or what event is running it so there is no way for me to evaluate its efficacy.

Personally, I would never use "remember me" on any application where I cared about security. Just because most web apps do this doesn't make it "cool" or rational. If the user forgets to press the log out button, the database is essentially unsecured using your logic. That's why you should force the logout when the database closes. You could just as easily skip the login entirely and use the users Windows credentials (Environ("UserName")) for internal purposes. Then whoever opens the FE is assumed to be the person logged into this computer.

Do you have a login form because someone said you should but never gave you any meaningful specs or because you actually want to prevent non-authorized users from using the app? If it's the latter, Access is a bad choice for the FE. And Access is a worse choice for the BE

 

[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.

 

[Pat Hartman]

I presume that means that you are switching to using the Environ("UserName") function to populate the current user instead of requiring a log in.

Again, this will track logins from a particular PC but isn't really security. I hope the users are religious about locking their computers when they step away from them even for even a moment.

I have a lot of apps with no security because they don't include any sensitive data so I'm not pushing security per se. I'm just trying to make sure that you AND YOUR USERS who requested this feature, are not using a method that would provide any actual security. If you bother to have a login and something that looks like security, it should actually provide some security.