opening form record to operating system user name

[Paul Ponzelli]

I'm trying to get a form to open to a record corresponding to the operating
system User Name.

I'm trying to use the following code:

Private Sub Form_Open(Cancel As Integer)
Dim rs As Object
Set rs = Me.Recordset.Clone
rs.FindFirst "[EmployeeID] = '" & DLookup("EmployeeID", "Employee",
[UserName] = fOSUserName()) & "'"
If Not rs.EOF Then Me.Bookmark = rs.Bookmark
End Sub

I know the problem is not the the function fOSUserName(), because I've
watched its value as I step through the code, and it correctly returns the
user name. The problem is that the DLookup() function is not obtaining the
corresponding value of the EmployeeID. The form just opens to the first
record in the underlying recordset, not to the record that corresponds to
the value in the UserName field.

So I'm thinking the problem is with the syntax in the DLookup statement.

Can anyone tell me what I can do to get the DLookup() function to return the
correct EmployeeID?

Thanks in advance,

Paul

 

[David C. Holley]

It appears that the the where statement of the DLookup(), after the
value from fOSUserName() result is calculated, becomes [UserName] =
David C. Holley. The David C. Holley should be in apostrophes as in
[UserName] = 'David C. Holley'
The underlying problem is complexity of imbedding the DLookup() in the
..FindFirst statement. I would have tried to add the single apostrophes
appropriately, but the statement is too complicated to figure out where
they would be. Secondary to that, why no just use DoCmd.OpenForm with a
wherestatement specified or apply a filter (see below)

strWhereStatement = "[EmployeeId] = " & DLookup("EmployeeId",
"Employee", "[UserName] = '" & fOSUserName() & "'"")
DoCmd.OpenForm "frmCodedRemarksInvoices", acNormal, , strWhereStatement

Me.FilterOn = False
Me.Filter = "lngTransportId = " & DLookup("EmployeeId", "Employee",
"[UserName] = '" & fOSUserName() & "'"")
Me.FilterOn = True

At any rate, take the DLookup() out of the .FindFirst statement and
store the value in a variable. It'll reduce the complexity and make it
easier to confirm that the syntax is correct.

K(eep) I(t) S(imple)

David H

 

[Paul Ponzelli]

Thanks, David. Your suggestions gave me some ideas about how to modify the
syntax, and I finally got it to work with the following code:

rs.FindFirst "[EmployeeID] = '" & DLookup("EmployeeID", "Employee",
"[UserName] = fOSUserName()") & "'"

So thanks for taking the time to write those examples.

Paul