Dlookup in side of a Loop Is it possible?

Question

I am "creating" my first loop, I copied code and am trying to get it to work. I have the loop functioning but when I try to do a Dlookup in the middle of the loop it does not work.

I am sure there are some ways to make this code work better, Just trying to retrieve dynamic data for the body of my email.

Here is the relevant part of the loop.

strSQL = "SELECT * FROM emailbody Where EmailMainID = " & Me.EmailMainID

Set rs = CurrentDb.OpenRecordset(strSQL)

With rs
    If Not .BOF And Not .EOF Then

            .MoveLast
            .MoveFirst

            While (Not .EOF)
                LookupInfo = rs.Fields("beforetable") & "-" & rs.Fields("beforefield") 'Get Table and Field to lookup
                LookupLen = Len(LookupInfo) 'Find how many letters are in the string
                SubtractLen = InStr(1, [LookupInfo], "-") ' Find the number of letters to the left
                RightCut = LookupLen - SubtractLen ' Find how many are to the right
                Table = Left([LookupInfo], InStr(1, [LookupInfo], "-") - 1) ' Set the table value
                Field = Right([LookupInfo], RightCut) ' Set the Field Value
                InfoInsert = DLookup("Table", "Field", TeamDetailsID = 39)
                FreshData = rs.Fields("emailbodyid") & " " & rs.Fields("bodycontent") 

                LongEmail = EmailMe & FreshData
                EmailMe = LongEmail
                FreshData = ""
                LongEmail = ""

              .MoveNext
            Wend

        End If

        .Close

    End With

What is error? Is "Table" a "name of a field in a table, a control on a form, a constant, or a function?" Is "Field" a "string expression identifying the set of records that constitutes the domain?" What happens if you put "TeamDetailsID = 39"? It looks like it should be in quotes when I checked online. — Riley Carney, Apr 17 '20 at 15:24
So Table is the name of a table in my database, I stored this table name in BeforeTable. I am trying to use it with the criteria as you have listed above. "TeamDetailsID = 39" My problem is the dlookup is not picking up the table name and the field name. — Mr. Formula, Apr 19 '20 at 00:33

score 2 · Answer 1 · answered Apr 17 '20 at 14:16

2

it should be:

InfoInsert = DLookup("Table", "Field", "TeamDetailsID = 39")

or, if you use a variable

InfoInsert = DLookup("Table", "Field", "TeamDetailsID = " & idteam)

answered Apr 17 '20 at 14:16

Duncan Drake

218
4
11

I did find the error in my dlookup, I changed that but it is still having a problem doing the dlookup... no error message just nothing happens. – Mr. Formula Apr 17 '20 at 17:23
1) make sure dlookup is indeed returning a valid value. Either use Debug.Print or Msgbox to check the returned value 2) you are not updating your record before moving to the next. Add .Update before .MoveNext – Duncan Drake Apr 17 '20 at 17:48
I am changing directions a little bit. I want to do a loop and do a dlookup using variables stored in a table for the Table Name and the Field name. My information is stored in EmailTable and EmailField. How can I make this work inside a loop?.... I guess the change is I am not concerned about all the other Gymnastics I now just want the dlookup to work....Is this even possible? – Mr. Formula Apr 19 '20 at 00:11
Hey are you doing this all in MS Access? – Riley Carney Apr 23 '20 at 22:29

score 0 · Answer 2 · answered Apr 19 '20 at 00:44

0

So I my most recent test tells me my difficulty is not the loop. I tested the code this way (Yes I had the field and the table mixed up in the above example)

I get the result in Test2 But not Test1

Dim Table As String
Dim Field As String

Table = TeamDetails
Field = DepartDate

test2 = DLookup("DepartDate", "TeamDetails", "TeamDetailsID = 39")
MsgBox test2
test1 = DLookup("Field", "Table", "TeamDetailsID = 39")
MsgBox test1

answered Apr 19 '20 at 00:44

Mr. Formula

11
1

1

This fixed the problem Table = "TeamDetails" Field = "DepartDate" Test2 = "Test2 " & DLookup("DepartDate", "TeamDetails", "TeamDetailsID = 39") MsgBox Test2 Test1 = "Test1 " & DLookup(Field, Table, "TeamDetailsID = 39") MsgBox Test1 – Mr. Formula Apr 19 '20 at 01:40

Dlookup in side of a Loop Is it possible?

2 Answers2