Dlookup Not working Properly

[BB69]

Hello,

I am trying to test upgrading our 2003 database to 2007. When I start the main form, it goes to the on error portion below. I know it has to do with the dlookup criteria because if I leave it blank, it will take the first record in the table I am searching. I insert a breakpoint at the docmd.maximize and then step into then next line. When it executes the first dlookup, the value is null. I have not had this problem in Access 2003.

Here is the code.

'assignment is located in the Open_form event procedure
Private Sub Form_Open(Cancel As Integer)
'**********************************************
' initializes all the variables
'**********************************************
Dim intDepartement As Integer
Dim Test As Variant
Dim inta As String
On Error GoTo Err_Form_Open

User = LCase(CurrentUser())

DoCmd.Maximize
'inits current user
'looks up the user's personal info in the Employees DB

Me.txtLastName = DLookup("[strLastName]", "[tblEmployees]", "LCase([tblEmployees]![strUserName]) = User")
'Me.txtLastName = DLookup("[strLastName]", "[tblEmployees]", "LCase([tblEmployees]![strUserName]) = User")
Me.txtFirstName = DLookup("[strFirstName]", "[tblEmployees]", "LCase([tblEmployees]![strUserName]) = User")
Me.EmployeeID = DLookup("[EmployeeID]", "[tblEmployees]", "LCase([tblEmployees]![strUserName]) = User")
inta = ("[DepartmentID]")

intDepartement = DLookup("[DepartmentID]", "[tblEmployees]", "LCase([tblEmployees]![strUserName]) = User")
Me.txtintDepartement = intDepartement
Me.txtDepartement = DLookup("[strDepartement]", "tblDepartment", "[DepartmentID] =" & intDepartement)
Me.Caption = "Time Sheet / Feuille de Temps de " & Me.txtFirstName & " " & Me.txtLastName

Exit_Form_Open:
Exit Sub
Err_Form_Open:
MsgBox "There is an error on the timesheet. Please advise MIS of the following error" & Err.Description, vbExclamation, "Error!"
' Application.Quit acPrompt

End Sub

Thanks
BB

 

[MajP]

LCase([tblEmployees]![strUserName]) = '" & User "'

 

[MajP]

sorry
"LCase([tblEmployees]![strUserName]) = '" & User & "'

 

[PHV]

1) when playing with controls the Load event procedure is better suited than the Open.

2) replace this:
Me.txtLastName = DLookup("[strLastName]", "[tblEmployees]", "LCase([tblEmployees]![strUserName]) = User")
with this:
Me!txtLastName = DLookup("strLastName", "tblEmployees", "strUserName='" & User & "'")

and so forth for the other DLookUp

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[BB69]

Thanks everyone. That worked.

BB