username() query

[gmazza76]

Good afternoon

I have a userform that I have managed to get to show a persons ID when they open a userform, but is there anyway I can get the userform to show the persons name as well?
I have a table that has the agentID, AgentName and Department, but how do I get this to load in the "Form_Open" below

The source table has the above headings and I would need the AgentName to show in Text23

Private Sub Form_Open(Cancel As Integer)
Dim Username As String
Username = Environ("USERNAME")
tb_racf.Value = Username

thanks in advance
Gavin

 

[welshgasman]

Use DlookUp()
Try and give your controls meaningful names. Text23 is not going to mean anything to anyone 6 months down the line.

 

[Micron]

If persons ID and AgentID are the same thing then sure. If not, I see nothing that relates the field names to what's being asked for. Maybe the DLookup is on the Username value?
Probably better to use Load event when populating calculated controls.

 

[gmazza76]

thanks @welshgasman,

I am going to rename the "Text23" to a meaning, but would I use the DLookup in the "Control Source" or in the userform open in VBA?

thanks

 

[welshgasman]

You could do either TBH.
It is a matter of preference. If you do put it as control source, then you would not be able to modify that control (should you ever want to)

I would probably set it in the Form Load event, as that is the normal place controls can first be seen.

 

[gmazza76]

thanks @welshgasman

I have tried =DLookUp("[Sort_Name]","DataAll","[AID] = " & [Me].[tb_racf]) in the source control and get "#NAME ?"

and

Dim strX As String

strX = DLookup("[Sort_Name]", "DataAll", "[AID] = " & Me.tb_racf)
Me.AgentName = strX

but I get an error "Run-time error '2471'- The expression you entered as a query parameter produced this error" 'mazzag' which is my AID

Any suggestions as I am unsure what I have done wrong as the table I am looking at is "DataAll" and the column to return the value from is "[Sort_Name]"

thanks

 

[welshgasman]

I tend to put the criteria into a string variable for domain functions if not a single simple criteria.
Then I can debug.print them to see if I have the syntax correct.
Then when correct, I can use them in the function.
Plus, if you cannot see the error, you can copy and paste here or eslewhere, so someone can point out the error.?

Strings need to be surrounded by single quotes ' unless it contains a single quote, then triple double quotes works, I think?
Date literals with # and in mm/dd/yyyy or yyyy-mm-dd format
Numbers do not need anything.