using dlookup

[p4nny]

Hi,

I have a value within a textbox returning "lanID" with some VBA behind this..

I would like to lookup this value in the textbox (textbox is called userID) and return "where "lanID" = "User Name" in the "staff" table..

hope that makes sense

Many thanks for the help

 

[p4nny]

Hi - please see code below

The code builder doesn't like [ ] around the UserList (domain) (text goes red in the code builder)

The code is within still as I'm unsure how to reference it when in a standard module. In this project I think it should be ok to keep it here?

also it errors with the & '") after svLanID.

At the moment, the code doesn't return an error, it just leaves a blank text box

much appreciated


Private Sub tb_fullname_load()
Dim svUserName As String, svLanID As String
svLanID = fOSUserName 'can't remember if you need the () at the end
svUserName = DLookup("[Analyst Name]", "UserList", "[Lan ID]='" & svLanID)
Me.tbFullName = svUserName
End Sub

 

[p4nny]

Now resolved..

i've used the =fOSusername on a text box.. Hidden it.. and then done a dlookup in the expression builder.

Thanks for your advice and guidance on the way!

 

[Micron]

You're welcome - glad you got it solved. I'm betting you did not check out DLookup as suggested, otherwise I think you would have caught my mistake! I forgot the closing quote. & svLanID) should have been & svLanID & ') ". This could cause anyone to believe the brackets were the problem as the errors are often flagged as close to the real problem as possible, but it's not always pinpoint accuracy. If they are around a domain or field name, should not be a problem. You probably rectified that by using the expression builder. My excuse is that I was tired. What's yours?

The code is within still as I'm unsure how to reference it when in a standard module. In this project I think it should be ok to keep it here?

You call it the same way I did (svLanID = fOSUserName). The difference is that being in a standard module exposes it to the entire project. You should consider making it work this way. Down the road, you might find this useful in several ways in this project. For example, you can pass the user name to a query if you want to capture who updates the database, retrieve records related to the user, etc. If you do not, I'd comment out the code to avoid the possibility of getting the error "Ambiguous name detected" but keep it for future reference.