Dlookup from Excel

[Guest]

Dear gurus
I would really appreciate some light in the dark. I have a problem with
Dlookup in Access from Excel.

The code below does the following:
Access opens Excel and receives a string value from column G; example MGF.
Access takes that value and compares it, in a table with Dlookup. When Access
finds the value in the table (tblInvesterare) it enters the value
(personnummer) from tblInvesterare to table tblOrder. Personnummer is also a
string value.

When I use:
strKundKopNr = Application.DLookup("[Personnummer]", "tblInvesterare",
"[Kortnamn]= 'MGF'")
It works fine, but when I change MGF to:
strKundKopNr = Application.DLookup("[Personnummer]", "tblInvesterare",
"[Kortnamn] = " & strKundKopNamn)

I get an error.

It is probably something wrong with the string, but I can’t figure out what
is wrong??? Any help would be appreciated.

Private Sub ImporteraExcel_Click()
Dim Db As DAO.Database
Dim rs As DAO.Recordset
Set Db = CurrentDb
Set rs = Db.OpenRecordset("tblOrder", dbOpenDynaset)
Dim xlApp As New Excel.Application
Dim strKundKopNamn As String
Dim strKundKopNr As String
xlApp.Workbooks.Open (CurrentProject.Path & "\sax.xls")

For X = 2 To 14
rs.AddNew
rs![Aktie] = xlApp.Sheets(1).Cells(X, 4).Value
rs![ISINkod] = xlApp.Sheets(1).Cells(X, 3).Value
strKundKopNamn = xlApp.Sheets(1).Cells(X, 7).Value
strKundKopNr = Application.DLookup("[Personnummer]",
"tblInvesterare", "[Kortnamn] = " & strKundKopNamn)

rs![Nr] = strKundKopNr
rs.Update
Next

xlApp.Quit
Set xlApp = Nothing
End Sub

 

[Guest]

You still need the qoutes when you are using a variable to pass a value to
the DLookup if the data type is text or string.
strKundKopNr = Application.DLookup("[Personnummer]", "tblInvesterare",
"[Kortnamn] = '" & strKundKopNamn & "'")