DLookup function with no action or errors

[rich1838]

I have a database to manage a company with 4 divisions. A table [employee] has a related form with the employees title. In order to break down employees by division, I created a table [titleconversion] where fields have the [Title] and [Division] and [EmploymentStatu s]

In the on the form [employees]![Title] i am using an afterupdate DLookup code to place [Division] and [EmploymentStatu s] based on the entry. My problem is NOTHING is happening. No errors, debugging issues, or results populating the fields. I have used DLookup many times but it seems I am missing something. I have used the code both ways listed below with same non-result. Any ideas would be appreciated.

division = DLookup("[Division]", "[titleconversion]", "Id= " & Forms!employee!Title)
EmployeeStatus = DLookup("[EmploymentStatus]", "[titleconverson]", "Id=" & Forms!employee!Title) 

division = DLookup("Division", "titleconversion", "Id= " & Forms!employee!Title)
EmployeeStatus = DLookup("EmploymentStatus", "titleconverson", "Id=" & Forms!employee!Title)

[zmbd]

You've stumbled upon one of my pet peeves by building the criteria string within the command - and it's not your fault because that's how a majority of examples show how to use the command.

Instead I suggest that you build the string first and then use the string in the command. Why you might ask, because you can then check how the string is actually resolving; thus, making troubleshooting the code so much easier as most of the time the issue is with something missing or not resolving properly/as expected within your string.

So to use part of your code:

DIM strSQL as string
strSQL = "Id= '" & Forms!employee!Title & "'"
'>>Note that I’ve already made a change here as I suspect that the field is a text value
'>>text values need to have a single quote around the string.
'>> Also, if field/control [Title] is local to the this form then one can use Me!Title instead of the forms method
'
'now you can insert a debug print here for troubleshooting
' - press <ctrl><g> to open the immediate window
' - you can now cut and paste this information for review!
'
debug.print "Your criteria = " & strSQL
'
'now use the string in your code:
division = DLookup("[Division]", "[titleconversion]", strSQL)

If you will make these little changes to all of your code and post back the resolved strings we can help you tweak the code.