How to get User ID

[PNGBill]

Hi Forum,

I have created a form with code etc that will produce letters and update tables accordingly.

One pce of data I can not get correct is the User ID.

When we log on we use our password against a login script - see jpg below.

TBLTeamMembers has fields TeamID (Initials per logon script), firstname, lastname etc.

I want to get the Intials (TeamID) to be used in one of the Append tasks being performed.

What ever I try, it always sends an error message where I can type in the initials and all is fine.
But I don't want to type my initials 700 times

Here is the sql that does the appending

 sqlString = "INSERT INTO tblLoanComm ( LoanID, CommNotes, Operator, CommDate, CommTime ) " & _
        "SELECT TBLLOAN.LDPK, ""Sent "" & [TblLetterOptions].[LetterType] & "" Letter"" AS CommNotes, TBLTeamMembers.TeamID, Date() AS CommDate, Time() AS CommTime " & _
        "FROM TBLLOAN, TblLetterOptions, TBLTeamMembers " & _
        "WHERE (((TBLLOAN.LDPK)=" & LDPK & ") AND ((TBLTeamMembers.TeamID)=" & strUserID & ") AND ((TblLetterOptions.LetterID)=" & LetterID & "));"

The error message appears and I type my Initials and all works fine with my initilas UpperCase as per the Dim here

 Dim sqlString, strUserID As String
    Dim ADPK, LDPK, LetterID As Integer
    
    ADPK = Me.ADPK
    LDPK = Me.LDPK
    LetterID = Me.LetterID
    strUserID = UCase(CurrentUser)

This means the system picks up that strUserID should be Uppercase but it doesn't pickup that it should be CurrentUser.

Any suggestions of how to resolve this??
Thanks

 

[PNGBill]

jpg attached

 

[PNGBill]

This code is the Row Source in a Form Combo Box.

TeamID is what I am looking for.

SELECT T.TeamID, T.TeamFirstName & '  ' & T.TeamLastName As Operator FROM TBLTeamMembers AS T;

This is part of an On Current event code for the form.

 'Get current operator
        Me.cboOperatorID = UCase(CurrentUser)

This form works and the Initials are appended correctly although the form displays the full name.

Why then does my code not work?

 

[PNGBill]

Resoled issue by making CurrentUser() AS strUserID a field in the Record Source sql and then it was easy to Me.strUserID in the vba code to get the user initials.

Can't understand why the query can use CurrentUser() but my vba cannot yet earlier vba done by others can??

It works anyway.

 

[JANR]

I haven't got an answer to your question just a FYI on this code:

[COLOR=red]Dim sqlString, strUserID As String[/COLOR]
    [COLOR=red]Dim ADPK, LDPK, LetterID As Integer[/COLOR]
    
    ADPK = Me.ADPK
    LDPK = Me.LDPK
    LetterID = Me.LetterID
    strUserID = UCase(CurrentUser)

Here your sqlSting, ADPK and LDPK are declared as VARIANT. If you don't typecast it explicitly then all variables gets typecast as Variant.

Dim sqlString As String, strUserID As String
    Dim ADPK As Integer, LDPK As Integer, LetterID As Integer
    
    ADPK = Me.ADPK
    LDPK = Me.LDPK
    LetterID = Me.LetterID
    strUserID = UCase(CurrentUser)

Is much better
JR

 

[PNGBill]

Thanks for that Heads Up, I was did mean for them to be Integer so will use better method in future.

 

[tyronedjackson]

The syntax for the CurrentUser function is CurrentUser() with the parenthesis. I didn't see the parenthesis in your code.

 

[gemma-the-husky]

currentuser may not help. currentuser is the user active in access - if you dont use access group membership, then EVERYONE is ADMIN.

one thing you could use maybe is ENViRON

try msgbox(environ(x)) - where x can be 1 to 30, to see the values

then, eg, msgbox("username") returns the same information without the identifier.

try it and you will see what i mean