variable syntax in rs.Findfirst. (1 Viewer)

C

coasterman

Registered User.

Local time

Today, 08:01

Joined

Oct 1, 2012

Messages

59

• Oct 25, 2012

• #1

Hi I am using the api fosusername() function as the source for my search in the FindFirst method.

Code:Copy to clipboard

Dim Uname As String

    Dim rs As Object
    Uname = fOSUserName
    Set rs = Me.Recordset.Clone

    rs.FindFirst "[UISID]" = Uname '
    If Not rs.EOF Then Me.Bookmark = rs.Bookmark
    DoCmd.Echo True

When I replace the Uname with say "Administrator" it works but no end of rearranging the quotes seems to get me the same results with the variable I am trying to pass as the criteria?

First time with this method so doubtless I am doing something a bit stupid?

Help please!!

Thanks for looking

 

KenHigg

Registered User

Local time

Today, 11:01

Joined

Jun 9, 2004

Messages

13,327

• Oct 25, 2012

• #2

rs.FindFirst "[UISID] = '" & Uname & "'"

??

 

C

coasterman

Registered User.

Local time

Today, 08:01

Joined

Oct 1, 2012

Messages

59

• Oct 25, 2012

• #3

Hi

Thanks for reply but still no joy "Jet database engine does not recognize 'Administrator; as a valid field name or expression".

When I put a watch on Uname the variable is assigned correctly as "administrator" so other than syntax which I assumed was the issue I don't know what I am doing wrong. As I said in original post when I replace the Uname variable with the "[UISID] ='Administrator' " it goes straight to the record I need, puzzling?

 

boblarson

Smeghead

Local time

Today, 08:01

Joined

Jan 12, 2001

Messages

32,059

• Oct 26, 2012

• #4

By the way you should be using the NoMatch piece in there in case there isn't a match.

Code:Copy to clipboard

Dim Uname As String
Dim rs As Object
 
    Uname = fOSUserName
 
    Set rs = Me.Recordset.Clone
 
    rs.FindFirst "[UISID]= " & Chr(34) &  Uname & Chr(34)
 
    If rs.NoMatch Then
     Msgbox "No match was found.  Contact your support person.", vbExclamation, "Error"
    Else
     Me.Bookmark = rs.Bookmark
   End If
 
   rs.Close
   Set rs = Nothing

Also, fOSUserName should be returning the network login ID of the person logged into the computer at this time. You do know that, correct? So, is your network user name really Administrator in this case? Is that what you are logging in under?

 

C

coasterman

Registered User.

Local time

Today, 08:01

Joined

Oct 1, 2012

Messages

59

• Oct 26, 2012

• #5

That did it!

Just so I understand this fully. It it the case that Dim'ing the variable as a string is not the same as a literal string in an expression hence the need to surround in chr(34)?

If the point above is true would the same method be used say in a Dlookup situation?

And again thanks for taking the time to resolve this for me.

 

boblarson

Smeghead

Local time

Today, 08:01

Joined

Jan 12, 2001

Messages

32,059

• Oct 26, 2012

• #6

Yes, you are correct and yes, it does need to be done that way in a DLookup (or other places as well).