Compile Error

[Crampton]

Sorry Guys and Girls but im very new to this VBA lark.

Anyway i have a message that comes uo with "Compile Error, variable not found"

I am trying to run a code as follows.

Public Function LoginName()

sUser = DLookup("UserEntered", "usersentries", "[colin]")

End Function

I am using this code to identify users who are logged on so that my Audit trail field can be updated.

Have i made an almighty boob?

Thanks for your help

 

[AncientOne]

You haven't declared sUser and you have Option Explicit set?

 

[bjackson]

if sUser is your variable where have you declared it
your dlookup doesnt appear to be looking anything up

 

[Crampton]

Compile error

Thanks Guys for the tips, ive sorted it a bit more now after reading some more Access Help, but I cant get my head around this Delare business.

How do i delcare my statement?

 

[Mile-O]

Re: Compile error

I cant get my head around this Declare business.

When your code modules are set to Option Explicit this means that you have to declare variables and constants.

If it's set you can run code and if a variable or constant is not declared an error will be generated.

Option Explicit will pick up any typographical errors you've made as it won't recognise the mispelled variable/constant.

So, to make life easier you can declare your variable.

This can be done at differing levels and actually, I've written a post describing different ways to declare variables which you'll find here.

 

[AncientOne]

Example:

Dim sUser as String

would declare your variable sUser as a text string which will be visible only in the current module. Other variables can be declared with a wider scope, such as Public or global.

It's worth mentioning that you set a value for sUser in a dlookup expression but don't do anything with it. Also, your function has no arguments and doesn't return a value. If this is what you want, you should make it a subroutine instead of a function.

 

[Crampton]

Thanks Ancient One and Mile o Phile. I will try what you suggest.

Thanks for your help

 

[Mile-O]

sUser = DLookup("UserEntered", "usersentries", "[colin]")

[Colin] ?

 

[AncientOne]

A fine and noble soubriquet. Think of all the famous men in history, who've had that name! (On second thoughts...)

 

[Mile-O]

I'm just guessing that Mister Crampton doesn't understand the DLookup() either.

I think he's put Colin in as the criteria when he's looking to put something like "[UserEntered] = 'Colin'"

 

[AncientOne]

All my fields have names like "Hector", "Persephone", Dinah", "Wolfgang", etc. It's so much more homely and by referring to them in conversations, I give the impression of having lots of friends, when I am actually just a lonely, friendless old git.

 

[Mile-O]

I give the impression of having lots of friends, when I am actually just a lonely, friendless old git.

I'm not getting the impression at all but you are succeeding in the latter.