Never could understand LOOKUPS

[jerseyboy]

I have read and read, but I cannot understand the simple DLOOKUP function. I think it will answer the need that I have right now, so I am going to ask that someone put it in laymans terms.

Here is an example of what I am looking for:

tblUsername

User LogOnName
------------------------------------
John Doe jdoe
Jane Smith jsmith
Joe Smith jsmith1


I have a downloaded module (thanks to that poster) that returns the person that is logged on to the machine (ie: jdoe, jsmith, jsmith1).

In the form's BEFORE UPDATE event I envision this:

strUserName as String
strUserName = UserName 'module function name that returns the currently logged on user

strUserName = 'convert strUserName to John Smith from jsmith somehow

cboUpdatedBy.setfocus
cboUpdatedBy.Text = strUsername

What I want to do is use that return to populate the "updated by" combo box with the User Name. I thought about hard coding the response in VB but even though they swear all of the users are accounted for you know that they are going to change next week. lol


Is the lookup function the way to go? I think so, but again I just can't seem to get it. Even if there is a better way can someone still explain it?

Thanks for you help, this is the best place anywhere for help!,
JerseyBoy

 

[papparuff]

Since you already have a function, UserName, why not pull the "User from the function" instead of the "LogOnName"? John Ruff - The Eternal Optimist

 

[WildHare]

Here's the way a DLOOKUP works: You feed the function the field you want, the table it's in, and the KEY or other match value that you want it to use to look up the NON key value you're searching for.

Think of trying to find a phone number:
DLOOKUP("PHONENUMBER", "BIGYELLOWBOOK", "BigYellowBookKeyField = 'Jim Hare'&quot

Basically , you use a domain function like DLOOKUP to find any NON-key field from a table, where you KNOW the key and just want to position there.

You can use a non-key or non-index value, but it takes a lot longer:

DLookup("Something", "SomeTable", "SomeOtherField = '123'&quot

The problem with using a NON key criteria is that the DLOOKUP will return the FIRST match it finds - and there may be dozens of records with '123' in SomeOtherField, since it's not the key.

Get the idea?





Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com

 

[jerseyboy]

Cool! After 2 years of people trying to explain this you have done it! I needed to have it explained on something other then quantum physics. Your phone book comparison was just the thing.

Let me make sure I understand by putting it in writing:

In the form's BEFORE UPDATE:

strUserName as String
strUserName = UserName 'module function name that returns the currently logged on user

strUserName = Dlookup("user","tblUserName","strUserName = 'logonname' &quot

Is this correct?

cboUpdatedBy.setfocus
cboUpdatedBy.Text = strUsername



papparuff,
how do you get the username from the windows log on? I don't log on by my name? Sounds easy enough, but I don't understand??

Thanks again for the help,
Jerseyboy

 

[TudorSmith]

One final thing...you got the idea, but you have to make sure you are comparing against the correct data type!

so, if you are selecting the user from tbluser based on the ID, then your code would look like this:

strUserName = Dlookup("user","tblUserName","UserID = " & UserID)

Notice how I terminate the lookup condition with a single double quote, because I am comparing UserID against an integer.

Now in your instance, you want to compare your strUserName against the string value which is your logonname. Therefore, you need an extra quote and a new Ampersand. It shoul dtherefore look like this:

strUserName = Dlookup("user","tblUserName","strUserName = '" & logonname & "'")

I am assuming your logonname variable is string, and if it is, you need the string quotes in the comparison. This vexed me for such a long time until I realised I needed to understand the comparison against data types!

birklea


birklea ~©¿©~ <><
I know what I know...don't presume that I know what I don't! Smithism!!!

 

[LittleSmudge]

<i>The problem with using a NON key criteria is that the DLOOKUP will return the FIRST match it finds - and there may be dozens of records with '123' in SomeOtherField, since it's not the key.</i>

In principle YES

BUT Access does not GUARANTEE to return the FIRST occurance if the match. If the nonKey field is indexed it is very likely to be the first - but it ain't guaranteed.

It little bug can take a AGE to track down if you're not aware of it !!

G LS

 

[WildHare]

If the nonKey field is indexed it is very likely to be the first - but it ain't guaranteed.

This is true - I can't remember ever using DLOOKUP with a NON-key comparator, but given the oftimes odd way JET does paging, I wouldn't be surprised at anything..

----------------------
Jerseyboy:
To quote,
strUserName as String
strUserName = UserName
strUserName = Dlookup(&quot;user&quot;,&quot;tblUserName&quot;,&quot;strUserName = 'logonname' &quot

Is this correct?

cboUpdatedBy.setfocus
cboUpdatedBy.Text = strUsername


I believe what you want to do can be done in a few less steps. First off, remember that the third DLOOKUP parameter is the Field in the TABLE that is the KEY field - it looks like you're using your variable name, &quot;strUserName&quot; instead.

I think something like this might be all you need:

cboUpdatedBy.Text = Dlookup(&quot;User&quot;, &quot;TblUserName&quot;, &quot;Userid = '&quot; & Username & &quot;'&quot

This is assuming your tblUserName is constructed along the lines of:

Userid(Key) userName
---------------------
jsmith John Smith
tcodd Ted Codd
cdate Chris Date
jboy Jersey Boy
etc etc




Remember, you're unique - just like everyone else
You're invited to visit another free Access forum:

http://www.forumco.com/jmhare

or my site,

http://www.jmhare.com