Dlookup in RecordSource

Question

I have a single view form who's data source is derived from Me.Recordsource (see below).  I would like to add a text box (Me.JobDescr) to this form who's data is derived from a dlookup command within VBA.  When I ran the updated form I noticed that Me.JobDescr in all of the records does not match up to the appropriate data but instead is/are a copy of the first record.  So the solution (I think) is to add the dlookup inside my recordsource.  However, VBA is erroring this out as bad syntax.  Can someone look at my syntax and comment on this approach?

Private Sub Form_Open(Cancel As Integer)

Dim strLastname As String

Dim strFirstname As String

Dim strFullName As String

Dim strMgrID As String

strLastname = DLookup("[Lname]", "tblUser", "[UserID] = " & Forms!frmLogin!cboUser)

strFirstname = DLookup("[Fname]", "tblUser", "[UserID] = " & Forms!frmLogin!cboUser)

strFullName = strLastname & ", " & strFirstname

strMgrID = Forms!frmLogin!cboUser

Select Case Forms!frmMainMenu.FrameEE

Case 1

    Me.RecordSource = "SELECT tblEmployee.EmplID, tblEmployee.EmplName, tblEmployee.JobCode," & vbCrLf & _

    "DLookup("Descr", "tblJobCostCtrCode", "[Code]=" & Me.jobcode) as JobDescr" &vbCrLf & _                                    'this is the added line

    "tblEmployee.CostCtr, tblEmployee.DOH, tblEmployee.DOT, tblEmployee.MgrID, tblEmployee.DirID " & vbCrLf & _

    "FROM tblEmployee WHERE (((tblEmployee.DOT) is null) and ((tblEmployee.MgrID)=" & strMgrID & ")) ORDER BY tblEmployee.EmplName ;"

...

Me.JobDescr.ControlSource= "JobDescr"

Answer 1

If the line is displayed in red, then the VBA statement is syntactically incorrect.  I thought it was the resulting SQL statement that was in error.

Because of line wrapping, I can't tell exactly what your code looks like, but maybe the problem is a missing _ at the end of the line with DLookup??

Answer 2

The code is located in the OnOpen EP on the form.  When I run debug compile the whole me.recordsource is in red font and the term Descr  in my second row is highlighted.  It appears that the new Dlookup line is causing the problem.  Not sure how helpful this is but at one point I had this line:

Me.JobDescr = DLookup("Descr", "tblJobCostCtrCode", "[Code]=" & Me.jobcode)

at the end of my code (before I tried to insert into Me.Recordsource) and it worked fine. 

At this point, debug is not available.  Your thoughts?

Answer 3

Is the Code field a Text field?

Did you use Debug - Compike menu to compike your code?  If you did, did the cinoiker geve any indication of where the error is located?

A good way to debug this kind of thing is to use Debug.Print

Dim strSQL As String

strSQL = "SELECT tblEmployee.EmplID, ...

Debug.Print strSQL

Me.RecordSource = strSQL

and look at the result in the Immediate window

Answer 4

Marshall,

I inserted your suggestion into my code but still getting a syntax error.  Your thoughts?

    Me.RecordSource = "SELECT tblEmployee.EmplID, tblEmployee.EmplName, tblEmployee.JobCode," & vbCrLf & _

    "DLookup(""Descr"", ""tblJobCostCtrCode"", ""[Code]=" & Me.jobcode & ") as JobDescr," & vbCrLf &

    "tblEmployee.CostCtr, tblEmployee.DOH, tblEmployee.DOT, tblEmployee.MgrID, tblEmployee.DirID " & vbCrLf & _

    "FROM tblEmployee WHERE (((tblEmployee.DOT) is null) and ((tblEmployee.MgrID)=" & strMgrID & ")) ORDER BY tblEmployee.EmplName ;"

Answer 5

All the quotes inside quotes have to be doubled up:

"DLookup(""Descr"", ""tblJobCostCtrCode"", ""[Code]=" & Me.jobcode & ") as JobDescr" &vbCrLf &