Newbie. Cannot understand why I'm getting "Invalid use of null".

[graffiti]

Hi.



The below code returns an error "invalid use of null". I don't see the problem, can you advise please?

 
tblDBUsers
[ID]              autonumber
[UserName]    text    50 characters
 
Dim varUserID as long
 
Watch :   :     varUserName : "Michael" : Variant/String : Form_frmLogIn.btnLogInFormClose_Click
 
'Return the ID number of the matching UserName 
varUserID = DLookup("[ID]", "tblDBUsers", "[UserName] = '" & varUserName & "'")

I know it will be simple. Thanks.

[pbaldy]

Well, the code would error if the DLookup couldn't find a match, as it would return Null, and a Long variable can't accept a Null. Is there a record with that user name, and if so does it have a non-null ID?

[June7]

Handle possibility of null. One way is Nz function.
varUserID = Nz(DLookup("[ID]", "tblDBUsers", "[UserName] = '" & varUserName & "'"),0)

Searches on names is unreliable. What if there is more than one Michael?

[graffiti]

Hi.

Thanks for the replies, I knew it was obvious! I'm trying to teach myself from online snippets etc in the odd 20 minutes here and there, so my knowledge is very patchy. So far.

The user name was *not* on the list, deliberately. I want to be able to catch a non existant entry but I didn't know enough to be able to. I now know more, thanks.

Mike.