Syntax Error 1

[LadyDev]

Can someone look at this and see why I keep getting a syntax error on my code. Thanks!

This is the error:

(Run-time error '3075': Syntax error in string in query expression 'MenuName="Details1'.

This is the sub it keeps blowing on:

Private Sub lstSubDirectory_Click()

DoCmd.OpenForm "xfrmGridWithAvailableFiles2", acNormal, , , , , _
DLookup("DirectoryName", "tblMenuItems", _
"MenuName = " & Chr$(34) & Me.lstSubDirectory) & "\"

Me.lstMain.Requery


This is the SQL for the listbox on the form:

SELECT tblMenuItems.SubMenuName
FROM tblMenuItems
WHERE (((tblMenuItems.MenuName)=[Forms]![xfrmMain2]![lstMain]))
ORDER BY tblMenuItems.SortOrder;

 

[jrbarnett]

It happens because when your program is running the DLookup, it issues a double quote in the code (Chr$ 34 is the ASCII code for a double quote mark):

thus

DLookup("DirectoryName", "tblMenuItems", _
"MenuName = " & Chr$(34) & Me.lstSubDirectory) & "\"

evaluates to:

"Subdirectoryname
Therefore, to terminate the code properly, either change to a single quote, or add a double quote on the other end.

Thus:

DLookup("DirectoryName", "tblMenuItems", _
"MenuName = " & Chr$(34) & Me.lstSubDirectory) & "\""

or

DLookup("DirectoryName", "tblMenuItems", _
"MenuName = '" & Me.lstSubDirectory) & "\'"

John

 

[LadyDev]

Thanks!