Set value based on Environ("UserName") 1

[krets]

When a user accesses a form I have created, what I would like to do is pull their username with Environ("UserName") then compare that to a table and display their initials.

Table = tblPharm
Fields = UserName & RphInit

What I would like is for the initials to appear in a combo box as the default value but make the rowsource equal to RphInit. Occasionally one pharmacist might want to log on via another pharmacist's computer so I want to make sure they can still select their own initials in that case.

Is that clear as mud?

 

[Remou]

So what is the problem?

 

[krets]

I can't figure out the best way to do it. I'm not sure how to go about creating the combo box with a rowsource of all RphInit but with the default value being the initials that correspond to Environ("UserName").

 

[Remou]

You can use DlookUp with username to get the initials and then set the combobox equal to the result in, say, the Current event, for example:

[tt]Me.cboInitials=DlookUp("RphInit","tblNames","UserName='" & Environ(UserName) & "'"[/tt]

You may wish to look at this, too:

http://www.mvps.org/access/api/api0008.htm

 

[krets]

Sweet, that works very well. Thanks.

What would I do to create an If statement to deal with users who access the database but who have not yet been added to tblPharm? For instance, if a manager or new pharmacist accesses the database and their username has not been added to the table yet.

 

[Remou]

You could use an update query to add them, after you have asked them to fill in initials.
Roughly:

If IsNull(DlookUp("RphInit","tblNames","UserName='" & Environ(UserName) & "'") Then
strInit=InputBox ("New person. Please fill in the initial you want to use.")
strSQL="Insert Into tblNames (RphInit, UserName) Values ('" & strInit & "', '" & Environ(UserName) & "'"
CurrentDB.Execute strSQL, dbFailOnError
End If

You will undoubtably want to check that the initial chosen do not exist and so on.

 

[krets]

Yeah, I actually have a form that is used to add a new pharmacist to the database so I was going to use the If to open that form if the Dlookup fails.

Thanks man.

 

[krets]

Instead of an Insert can I do an Update here:

If IsNull(DlookUp("RphInit","tblNames","UserName='" & Environ(UserName) & "'") Then
strInit=InputBox ("New person. Please fill in the initial you want to use.")
strSQL="Update tblPharm Set UserName = Environ("UserName") Where RphInit = strInit"
CurrentDB.Execute strSQL, dbFailOnError
End If

All of the pharmacists will already be listed in tblPharm but some might be missing their user names. Would that function work?

 

[PHV]

strSQL="UPDATE tblPharm SET UserName='" & Environ("UserName") & "' WHERE RphInit='" &  strInit & "'"

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886