Dlookup Hyperlinks to display in MsgBox

Question

Im attempting to replace all my path constants in VBA with an administration table in my datatbase so users can change the location of folders and files without having to edit vba code.

the code is as follows

Private Sub Command8_Click()

    Debug.Print DLookup("fsFileLink", "tblFileSystem", "fsFileName= 'TEMPLATES'")
    MsgBox = DLookup("fsFileLink", "tblFileSystem", "fsFileName= 'TEMPLATES'")

End Sub

debug.print returns

C:\Users\... \templates\

but the msgbox returns the error "left must be variant or object". How can I get my dlookup value as a string that I can display and edit in a text box?

Thank You

Maybe you are looking for strResult = InputBox("Your title", DLookup("fsFileLink", "tblFileSystem", "fsFileName= 'TEMPLATES'")), which should show you the looked-up path as a default value but let you edit it on screen. — pteranodon
I tried that code but it set the title as the dlookup value, not the input box EDIT: code'strResult = InputBox("Your title", default:=DLookup("fsFileLink", "tblFileSystem", "fsFileName= 'TEMPLATES'")) works — Ian Jackson

Ian Jackson Ian Jackson · Accepted Answer · 2014-06-30T12:57:52

I'm using the below code:

Dim strCOCTemplate As String

strCOCTemplate = Nz(DLookup("fsFileLink", "tblFileSystem", "fsFileName= 'TEMPLATE_COC2'"), "none")

If strCOCTemplate = "none" Then Err.Raise Number:=11001, Description:=ERR_DESC_11001 
Set docJobSpec = WordApp.Documents.Add(Template:=strCOCTemplate, NewTemplate:=True)

Dlookup returns a variant, but Template:= can't accept null values and throws an akward error. I compensated for this by using nz() function to return a string, and raised my own custom error to say the table couldn't find data

Dlookup Hyperlinks to display in MsgBox

1 Answers