Which is a better way of coding?

[yhgtbfk]

Is there any difference in doing:

(Preferred method)

If IsNull(DLookup("username", "usercolours", "username = '" & username & "'")) = False Then

OR

Dim checkuser As string
checkuser = DLookup("username", "usercolours", "username = '" & username & "'")
If isnull(checkuser) = False then

 

[WayneRyan]

y,

The second one will generate an error. You would have to declare checkuser
as a Variant to make it work.

I would prefer the first method.

You can also use:

checkuser = Nz(DLookUp(..), "")

That will assign an empty string to checkuser (as opposed to a Null string).

Wayne

 

[yhgtbfk]

Thanks Wayne.

On a different note, but still talking about declaring variables

With the following line

Set rst = CurrentDb.OpenRecordset("SELECT * FROM userColours WHERE username = '" & username & "';")

What do I declare rst as? I tried

Dim rst As recordset

But I got an error when doing

With rst
.Edit 'Error here

 

[WayneRyan]

y,

You have a choice here; ADO or DAO

I generally use DAO.

Dim rst AS DAO.Recordset

Get your code in Design View, then "Tools --> References" and make sure
that Microsoft DAO is checked (and promoted higher that ADO).

I also generally.

Dim dbs As DAO.Database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset ...

Wayne

 

[yhgtbfk]

I also generally.

Dim dbs As DAO.Database
Set dbs = CurrentDb
Set rst = dbs.OpenRecordset ...

Wayne

Thanks, that worked perfectly.

 

[The_Doc_Man]

For your DLookup, you could also do a DCount of users matching your criteria. If the answer is zero, you are done. I believe DCount doesn't have (as much) trouble with nulls, 'cause it just skips them.

 

[KenHigg]

y, You've probably seen this in code that the wizards build, where every thing gets declared as a variable. My guess is that it's easier for the wizard to build stuff this way. Also, if you embed functions, it can make make debugging more difficult because the debug will only indicate which line the error occurred on and not which function error'd. Finally, some coders will do it for readability making it easier for them to follow what the code is doing...

Having said all that, I still do it like your second example when I can...

kh