Results 1 to 3 of 3
  1. #1
    Join Date
    Jan 2004

    Angry Unanswered: DLookup on Continuous Form?


    I have 3 controls ([need1], [need2], [need3]) on a continuous form. I want them to return the needed "[Number]" from a table specific to that record (field [color]). I have the following code.... and it returns semi-correct values... but they are the same for every single record on that form. What am I doing wrong? I hate DLookup (but I need it!!).

    Dim str1, str3 As String
    Dim str2 As Integer

    str1 = "'[" & field & "] = "

    If color = "Red" Then
    str2 = 30
    ElseIf color = "white" Then
    str2 = 40
    ElseIf color = "blue" Then
    str2 = 50
    ElseIf color = "green" Then
    str2 = 60
    End If

    str3 = str1 & str2 & "'"

    Need1 = DLookup("[Number]", "Table1", str3)
    Need2 = DLookup("[Number]", "Table2", str3)
    Need3 = DLookup("[Number]", "Table3", str3)

    Please and Thank you!!

  2. #2
    Join Date
    Sep 2003
    Look again at your criteria ... From the looks of it your string makes: '[XXXXXXXX]=30'

    Is this right? It sure doesn't to me ... I can see something like: [XXXXXXX]='30' ... Also check your types. Is the number 30 supposed to be a string?

  3. #3
    Join Date
    Jan 2004
    All the data types in the table I'm looking in are numbers.

    Ideally, the string should look something like ("field" is variable):
    "[field] = 30"

    Shouldn't the result be different for every record on the form??

    I'm self-taught at all of this, so sometimes my logic is a little fuzzy.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts