accessing data from a table other than the 1 bound to the form

[leeweaver]

I an trying to look up data in another table other than the 1 that the form is bound to using VBA. the specific application is:

the user table holds the prefered display name of the user. I pull the user information from fOSUserName() and I need to get that user's prefered display name so i can place it in a text filed in a text box on the curent form.

I only need read access to the secondary table. but can't figure out how to "Open" the 'user' table.

[Mavyak]

Look into the DLookUp() function.

[CreganTur]

Look into the DLookUp() function.

Mayvak's completely correct, but you need to be aware that DLookup can be very fussy if you are using the WHERE parameter. If you're evaluating a String in the WHERE parameter, then you need to wrap it in single quotes.

Evaluating a variable:
DLookup("Status", "tblUsers", "UserID= '" & User & "'")

Evaluating textbox value:
DLookup("Status", "tblUsers", "UserID= '" & Me.txtUser & "'")

HTH:thumb

[leeweaver]

thanks a bunch guys. i'm slowly learning this... maybe by the time i finish this project I may actually know something about VBA

Lee

[FrymanTCU]

Very useful stuff guys. Lee I feel the same way, I wish I could help out like CreganTur... The VB Jedi Master!

[CreganTur]

Jedi Master http://img365.imageshack.us/img365/173/jediux4.gif or Ninja Master http://img293.imageshack.us/img293/9060/ninja3od8.gif?


I wish I could help out like CreganTur... The VB Jedi Master!

I really appreciate the compliment. There are a lot of other members who have more knowledge and experience than I do... I just hang out here way too much:whistle:

Helping out here has been a great way for me to learn- I love helping other people solve their problems. I've also found that getting a really good book or two can help tremendously in understanding Access- just make sure it's a good hands-on book with plenty of examples to work through.

[leeweaver]

Ninja Jedi Master or would that be Ninja Sith lord?

[FrymanTCU]

Any recommended reads then? Most of the stuff at BN is for 2007 and we only have 2000...

[CreganTur]

Any recommended reads then? Most of the stuff at BN is for 2007 and we only have 2000...

Hmmm... I really don't personally know of any good books for Access 2000- I work with 2003 and 2007.

If you want to learn about Access' front end and good design, then I suggest Access 2003 your visual blueprint for creating and maintaining real-world databases (http://www.amazon.com/Access-2003-blueprint-maintaining-real-world/dp/0764540815/ref=sr_1_1?ie=UTF8&s=books&qid=1218740429&sr=8-1)

If you want to delve into VBA- especially the realms of ADO and DAO connections, then I reccomend Access 2003: Programming By Example With VBA, XML, And ASP (http://www.amazon.com/Access-2003-Programming-Example-VBA/dp/1556222238/ref=sr_1_1?ie=UTF8&s=books&qid=1218740534&sr=1-1)