Get User

[bartmanekul]

Hi all,

I've been using: GetUserName() in the criteria of a query, with a report based off of it.

In the field it's a criteria for, it's only populated by system usernames, i.e. firstname.secondname

It works fine on my computer, only showing the records which has my name in. But on other people's, it seems to be using my name too - even though they are logged in under their name.

Is this code wrong?

 

[Minty]

What is the code for the function?

 

[bartmanekul]

This, I got it from the net, and it seemed to be working fine, until today (I cannot code for various reasons)

Public Function GetUserName() As String
    ' GetUserName = Environ("USERNAME")
    ' Better method, see comment by HansUp
    GetUserName = CreateObject("WScript.Network").Username
End Function

 

[theDBguy]

This, I got it from the net, and it seemed to be working fine, until today (I cannot code for various reasons)

Public Function GetUserName() As String
    ' GetUserName = Environ("USERNAME")
    ' Better method, see comment by HansUp
    GetUserName = CreateObject("WScript.Network").Username
End Function

Hi. I use that code too, and I haven't run into any issues with it. Do you know if there were any recent updates installed on their machines?

To double check if it's still working, add a calculated column in your query using GetUsername() and check the result.

 

[bartmanekul]

Hi. I use that code too, and I haven't run into any issues with it. Do you know if there were any recent updates installed on their machines?

To double check if it's still working, add a calculated column in your query using GetUsername() and check the result.

Not sure how to code that - but interestingly, my results on the report are now showing other people's records, when they should just be mine.

I don't think there's been any updates, and we are using office 365.

When I was testing on my machine (under my logon), it only showed mine, even though there were other records. But people are actually entering via the form, the query shows all recent additions.

I've checked - it displays their logon username correctly (so code must be working on a textbox using that code to display the user). They are selecting the right names when adding a record. But for some reason, the query seems to be ignoring the criteria now. It still excludes some older records.

The 'Person' field it uses, has a set look up which I created lookups for based on their log on names. Set to shorttext.

 

[theDBguy]

The 'Person' field it uses, has a set look up which I created lookups for based on their log on names. Set to shorttext.

If you're saying it's a lookup field at the table level, that's considered bad practice. To help you troubleshoot this, can you post a sample db with test data?

 

[bartmanekul]

If you're saying it's a lookup field at the table level, that's considered bad practice. To help you troubleshoot this, can you post a sample db with test data?

It does, yes. It was the easiest way I can do it without knowing coding/expressions. I've desensitised the data (hopefully there's enough in there to show). The Query in question is 'Outstanding personal'. Many thanks for your time.

 

[Gasman]

PMFJI,
Your sql for personal is

SELECT Table1.[Quote Number], Table1.Person, Table1.[Date Sent], Table1.[Sage Quote no], Table1.[Account Code], Table1.Email, Table1.Area, Table1.Items, Table1.[Buying Group], Table1.Notes, Table1.[Quote Value], Table1.[Date of Last Chase], Table1.[Won/Lost], Table1.[Confirmation on Price], getusername() AS Expr1
FROM Table1
WHERE (((Table1.Person)=GetUserName()) AND ((Table1.[Won/Lost]) Is Null) AND ((Table1.[Confirmation on Price]) Is Null)) OR (((Table1.[Confirmation on Price])="No"));

and my name comes up with GetUser(), but all the Confirmation on Price are No ?

 

[bartmanekul]

PMFJI,
Your sql for personal is

SELECT Table1.[Quote Number], Table1.Person, Table1.[Date Sent], Table1.[Sage Quote no], Table1.[Account Code], Table1.Email, Table1.Area, Table1.Items, Table1.[Buying Group], Table1.Notes, Table1.[Quote Value], Table1.[Date of Last Chase], Table1.[Won/Lost], Table1.[Confirmation on Price], getusername() AS Expr1
FROM Table1
WHERE (((Table1.Person)=GetUserName()) AND ((Table1.[Won/Lost]) Is Null) AND ((Table1.[Confirmation on Price]) Is Null)) OR (((Table1.[Confirmation on Price])="No"));

and my name comes up with GetUser(), but all the Confirmation on Price are No ?

That looks different from what's in there (I see there's 'AS Expr1' added). I did copy and paste the code in, but still gives the same results - i.e. not just my records, other peoples.

As for the other criteria on Confirmation of price, it needs to show those which are either 'No' or have no entry (Null, obviously). And probably obvious, any which doesn't have a won or lost date. These all seem to work fine, but it's the Person which is causing the issue.

 

[theDBguy]

I've desensitised the data (hopefully there's enough in there to show). The Query in question is 'Outstanding personal'. Many thanks for your time.

Will this work?

 

[Gasman]

I added the Expr1 to see what the function produced.
You sql will show any records that have Confirmation of price, = NO as you have OR not AND ?

This will do what you want I believe?

SELECT Table1.[Quote Number], Table1.Person, Table1.[Date Sent], Table1.[Sage Quote no], Table1.[Account Code], Table1.Email, Table1.Area, Table1.Items, Table1.[Buying Group], Table1.Notes, Table1.[Quote Value], Table1.[Date of Last Chase], Table1.[Won/Lost], Table1.[Confirmation on Price], Table1.[Confirmation on Price]
FROM Table1
WHERE (((Table1.Person)=GetUserName()) AND ((Table1.[Won/Lost]) Is Null) AND ((Table1.[Confirmation on Price]) Is Null Or (Table1.[Confirmation on Price])="No"));

 

[bartmanekul]

I added the Expr1 to see what the function produced.
You sql will show any records that have Confirmation of price, = NO as you have OR not AND ?

This will do what you want I believe?

SELECT Table1.[Quote Number], Table1.Person, Table1.[Date Sent], Table1.[Sage Quote no], Table1.[Account Code], Table1.Email, Table1.Area, Table1.Items, Table1.[Buying Group], Table1.Notes, Table1.[Quote Value], Table1.[Date of Last Chase], Table1.[Won/Lost], Table1.[Confirmation on Price], Table1.[Confirmation on Price]
FROM Table1
WHERE (((Table1.Person)=GetUserName()) AND ((Table1.[Won/Lost]) Is Null) AND ((Table1.[Confirmation on Price]) Is Null Or (Table1.[Confirmation on Price])="No"));

It works!

Thank you very much. Looking at what you've changed, was it a simple logic error? I didn't code it, just used expression builder.

@theDBguy , thanks for the DB adjustments also!

Greatly appreciate the help given. Looking at the solutions, it's not something I would have gotten.

 

[theDBguy]

@theDBguy , thanks for the DB adjustments also!

I hope it worked too. Cheers!

 

[Gasman]

It works!

Thank you very much. Looking at what you've changed, was it a simple logic error? I didn't code it, just used expression builder.

@theDBguy , thanks for the DB adjustments also!

Greatly appreciate the help given. Looking at the solutions, it's not something I would have gotten.

For the price field I literally made it 'Is Null or "No" ' all in the same field in the Design GUI.
Access added the fieldname again.