Trying to tidy up my form access with groups

[shabbaranks]

Hi All,

Im trying to tidy up the form access as before I had it look up a name on a form. Now Im controlling it by groups. Ive added a yes/no field to a table and the VBA looks to see if they are set to yes or no. If yes they can access the necessary form if no a message box appears.

If ticked it works fine but if I set the value to false (unticked) I get the error invalid use of Null - is null the same as no ticked? If so shouldn't it work?

Private Sub Admin_btn_Click()
Dim sAdminUser As Boolean
sAdminUser = DLookup("AdminUser", "UserNames_tbl", "[sUser] = '" & Me.LoggedInUser & "' AND [AdminUser] = True")
If sAdminUser = True Then
DoCmd.OpenForm "SubmittedTimesheet_frm"
Else
MsgBox "You cannot access this function"
    Exit Sub
End If
End Sub

Thanks

 

[pbaldy]

No, they're not the same. If when you add the field you specify a default value of False (0), it should work. You can set them all to False now too.

UPDATE TableName
SET FieldName = 0
WHERE FieldName Is Null

 

[shabbaranks]

Thanks but that didn't work - its still complaining about invalid use of null.

 

[pbaldy]

Just noticed the second criteria. The DLookup isn't returning a record because of:

AND [AdminUser] = True

I'd take that out, since your code handles it if they aren't.

 

[shabbaranks]

Just noticed the second criteria. The DLookup isn't returning a record because of:

AND [AdminUser] = True

I'd take that out, since your code handles it if they aren't.

ah ha!! Thank you my brain was almost frazzled trying to get to the bottom of it

 

[pbaldy]

No problem. You can also dim the variable as Variant so it can take a Null, or wrap the DLookup() in the Nz() function.