Someone make LOOKUP easy

[lawdie]

Hi All,

I've been trying and trying, I've RTM'ed, I've even tried help. But I cant' understand how and where to write LOOKUP statements (or am I using the right approach).

I'm trying to write a access database for a college project based on an IT helpdesk.

What I want is;

If you select one on the UsrIDs from the list, it lookups the table (tblUser) obivously finds the user ID and pulls up the User asset number.

Could some in simple, explain or demonstrate a access LOOKUP, where it goes and what it can can't relate to.

If some person wishes to direct me to a site that would help me understand I'd be happy to do the research (that goes for other commands used in access).

Thanks all for any help,

Regards, Lawdie

 

[Tanis]

Are usrID and user asset no in the same table? If so, you don't need lookup as such. It can be achieved by filter or bookmarks.

 

[Dugantrain]

Lawdie, this shouldn't be too difficult. If these are the only two fields that you need and the data is stored in the same table, then you can create a form based directly off of that table. Go to Forms, use the Forms wizard and it will allow you to pick which fields from which tables you'd like on your form. Select the two fields that you need. By default, both fields will be in the form of text boxes. Go to design view, delete the UserID text box and drag and drop a combo box from your toolbar onto your form. You'll get another wizard which will prompt you on where this data comes from; select the third option "Find A Record...". Select UserID from your available fields. Your User Asset Number field should then autopopulate based on your UserID selection. For extensive help on all things Access, check out:

http://www.access-programmers.co.uk/

This is the single best online reference that I've found for Access. However, I advise that you search their forums for an answer to your question before posting new questions.

 

[MsT]

If you want to use DLOOKUP, then this page is the best simple explanation I've found

http://users.bigpond.net.au/abrowne1/casu-07.html

 

[bill]

Sure I'll give it a try.

Bear in mind that using a lookup involves data where it is and where you want to get it.

Where it is meaning in the query or VBA it's being used in and what you want being another query or table.

All criteria for a lookup is quote delimited.

Assumptions:
VBA user id
dim strUser as string
VBA Asset id
dim as strAsset as string

Records table tblUser
User id in tblUser = strUser
Asset id in tblUser = strAsset

Intentionally, I used strUser and strAsset for both your VBA and as a reference against the user table...Will explain soon enough

VBA user variable strUser populated by the form in some way.

if in table strAsset strUser is text, use this:
strAsset = DLOOKUP("strAsset", "tblUser", "strUser = " & QUOTE & strUser & QUOTE)

if in table strAsset strUser is Numeric, use this:
strAsset = DLOOKUP("strAsset", "tblUser", "strUser = " & strUser )


Confusing?

Remember you are passing stuff to the function DLOOKUP which in turn queries a datasource for the specified result.

Also remember that something in quotes is NOT the same as a variable.

"strAsset" is NOT the same as strAsset !!!!!
"strUser" is NOT the same as strUser !!!!!


So assume the user selected userID of 1234
After concatenating the filter criteria fo DLOOKUP, the string looks like this:


strUser = "1234" <------- for strUser in table strUser as Text column
strUser = 1234 <------- for strUser in table strUser as Numeic column

Remember again that this "filter" is used against the table specified.
Also bear in mind that the table and filed I call in the DLOOKUP are literals and not actual variables used in the VBA code.

For me, I use DLOOKUP when I want a simple value returned especially when used in a QUERY.