I’ve got a form in a database I inherited, and when users enter data, their network login is added to the record through a function called getloginname().
When I build a query off that same table, and I use login=getloginname() as the criteria, I get nothing back.
I did some testing and found that when I have the user enter data into a local table in the same access database, the problem goes away. Currently the data however gets written to a table on a SQL server, and it seems like when that happens something is happening to the login value being created by that function.
The function that returns the login name looks like this:
Function GetLoginName() As String ’ Returns the network login name
On Error Resume Next
Dim lngLen As Long, lngX As Long
Dim strLoginName As String
strLoginName = String$(254, 0)
lngLen = 255
lngX = apiGetUserName(strLoginName, lngLen)
If Environ$(“UserID”) <> “” Then
GetLoginName = Environ$(“UserID”)
ElseIf lngX <> 0 Then
GetLoginName = Left$(strLoginName, 10)
End If
End Function
I didn’t actually write any of it, but I’ve had it dumped in my lap to get it fixed and I’m not getting anywhere.
The query is comparing the current value of getloginname() at the time the query runs to the value in the table field. The purpose of the query is so that whoever runs it only sees the records matching their own login so they don’t have to sift through a ton of other stuff.
I can do that, and it works…the problem is that I already know what the function is returning; the same function that populates the initial table is being used in the query, so whatever value it returns should be the same each time I run it. And yet somehow they don’t match despite the fact that, to the naked eye, they do in fact match.
Echoing JerrySTL, try trimming the data. If you are storing this in a CHAR field, the table values will have trailing spaces.
And can I also complain about “On Error Resume Next” with no error checking? Using On Error Resume Next is a fine approach if you want to check for specific errors and handle them in specific ways. With no checking, it’s a way for bad developers to write crap code and say “look, my code runs with no errors!” (I know this was code you inherited, not written by you, so I feel OK being rude about it.)
Another thing to try - I often use SQL Profiler to see the exact TSQL an app is sending to the database. This might quickly point you toward the problem.
I actually tried trimming the data first, with no luck.
The field in the table I’m looking at is called ‘login’—I was able to get this to work:
left(login,6)=left(getloginname(),6)
Of course, if two logins with the same first six characters come along it won’t work correctly.
It’s like there’s some invisible character (not a space though) at the end of the field. The thing that doesn’t make sense is that the ‘login’ field is intially populated by the getloginname() function, but it’s like it’s doing something to the data when it inserts the value into the table.
Select asc(right([login],1)) AS “TheAsciiValue”,
right([login],1) AS “TheLastCharacter”
From YourTable
Order By 1;
ASCII values between 32 and 126 are OK. Anything else, especially from 1-33 and 127, could be a problem. For example 13 would be a carriage return.
If you get a number in the first column but can’t see the character in the second, that could be a non-printing ASCII character. It would still break your comparison between the login and what’s in the table.
If you aren’t able to figure out what the trailing character is and eliminate it, you can probably make it work with the Left function and avoid the problem of the first characters matching.
Left(login, Len(getloginname()) = getloginname()
It’s a hack workaround, so I don’t recommend it.
ETA: on review, it’s actually worse than a hack because it doesn’t work. Records with both TroutMan and Trout would be returned for a login name of Trout, for example.
That’s what I already did as a test, and I can get by with it for now since it’s a small group of users with dissimilar logins. But there’s no guarantee it’ll always work, and I’m also dying to know exactly why it isn’t working in the first place.
If you upload the database to http://www.utteraccess.com/ there’s a good chance that I’ll see it there and could help on it. If not me, someone else could help.
I’ve actually got a similar thread going at utteraccess as well; I can’t actually upload the database since it’s for my job and they’d probably frown on it, even without the actual real data in it.
Someone over there recommended changing this:
GetLoginName = Left$(strLoginName, 10)
to this:
GetLoginName = Left$(strLoginName, lnglen -1)
I’m not sure what the difference is between the two in terms of what it’s actually doing to the value in that field, but I haven’t had a chance to test it yet. I’m hoping to this afternoon.
Am I supposed to run this in SQL management studio? I get a syntax error when I try it
(Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword ‘asc’.)
ETA: --I changed ‘asc’ to ‘ascii’ and it ran. Most of the values came out 0,2 or 3.