Dlookup - yet another question

[Guest]

Hi,

I'm having a problem with this Dlookup function. Here's the line:

Dim MSL As Variant
Dim MLST As Variant

MSL = Me.SLNUM
MLST = Me.List

Me.LASTNAME.Value = DLookup("[LNAME]", "[TEST]", "[SLNUM] = " & MSL & " And

• = " & MLST)
  
  I get the error: "You cancelled the last operation."
  
  Any help?
  
  Thanks in advance.

 

[aaron.kempf]

well do either of those have a NULL in the field?

I hate the debugger in Access; I just wish it would give logical error
messages; but NO-- ms was too busy trying to convince us to use
SharePOINT instead of fixing MS Office.

 

[John W. Vinson]

Hi,

I'm having a problem with this Dlookup function. Here's the line:

Dim MSL As Variant
Dim MLST As Variant

MSL = Me.SLNUM
MLST = Me.List

Me.LASTNAME.Value = DLookup("[LNAME]", "[TEST]", "[SLNUM] = " & MSL & " And

• = " & MLST)
  
  I get the error: "You cancelled the last operation."
  
  Any help?
  
  Thanks in advance.

• 
  
  The "cancelled" error is sort of generic and many things can cause it.
  What's the context?
  
  I'm guessing that the problem may be due to the datatype of these
  fields. Is either SLNUM or LIST a Text type field? If so the search
  criterion must be delimited with either ' or " characters. Say if MSL
  is a text field, you could use
  
  Me.LASTNAME = DLookup("[LNAME]", "[TEST]", "[SLNUM] = '" & MSL & "'
  And
  • = " & MLST)
    
    Note also that you don't need to (and perhaps cannot, I don't recall)
    set the Value property of a textbox - just set the textbox.
    
    John W. Vinson [MVP]

 

[Guest]

They are both text field - by george I think you've got it.

I'll give it a try. Thanks.

Hi,

I'm having a problem with this Dlookup function. Here's the line:

Dim MSL As Variant
Dim MLST As Variant

MSL = Me.SLNUM
MLST = Me.List

Me.LASTNAME.Value = DLookup("[LNAME]", "[TEST]", "[SLNUM] = " & MSL & " And

• = " & MLST)
  
  I get the error: "You cancelled the last operation."
  
  Any help?
  
  Thanks in advance.

• 
  
  The "cancelled" error is sort of generic and many things can cause it.
  What's the context?
  
  I'm guessing that the problem may be due to the datatype of these
  fields. Is either SLNUM or LIST a Text type field? If so the search
  criterion must be delimited with either ' or " characters. Say if MSL
  is a text field, you could use
  
  Me.LASTNAME = DLookup("[LNAME]", "[TEST]", "[SLNUM] = '" & MSL & "'
  And
  • = " & MLST)
    
    Note also that you don't need to (and perhaps cannot, I don't recall)
    set the Value property of a textbox - just set the textbox.
    
    John W. Vinson [MVP]

 

[Guest]

Hi John,

I think it's the right track but I'm still having syntax trouble. How do I
write it when both variables are text?

Thanks for your help.

B

Hi,

I'm having a problem with this Dlookup function. Here's the line:

Dim MSL As Variant
Dim MLST As Variant

MSL = Me.SLNUM
MLST = Me.List

Me.LASTNAME.Value = DLookup("[LNAME]", "[TEST]", "[SLNUM] = " & MSL & " And

• = " & MLST)
  
  I get the error: "You cancelled the last operation."
  
  Any help?
  
  Thanks in advance.

• 
  
  The "cancelled" error is sort of generic and many things can cause it.
  What's the context?
  
  I'm guessing that the problem may be due to the datatype of these
  fields. Is either SLNUM or LIST a Text type field? If so the search
  criterion must be delimited with either ' or " characters. Say if MSL
  is a text field, you could use
  
  Me.LASTNAME = DLookup("[LNAME]", "[TEST]", "[SLNUM] = '" & MSL & "'
  And
  • = " & MLST)
    
    Note also that you don't need to (and perhaps cannot, I don't recall)
    set the Value property of a textbox - just set the textbox.
    
    John W. Vinson [MVP]

 

[fredg]

Hi,

I'm having a problem with this Dlookup function. Here's the line:

Dim MSL As Variant
Dim MLST As Variant

MSL = Me.SLNUM
MLST = Me.List

Me.LASTNAME.Value = DLookup("[LNAME]", "[TEST]", "[SLNUM] = " & MSL & " And

• = " & MLST)
  
  I get the error: "You cancelled the last operation."
  
  Any help?
  
  Thanks in advance.

• 
  
  The values appear to be text values.
  You don't need the variables.
  Just use Me.SLNUM and Me.List directly in the DLookUp.
  
  Me.LASTNAME = DLookup("[LNAME]", "[TEST]", "[SLNUM] = '" & Me.SLNUM &
  "' And
  • = '" & Me.List & "'")

 

[Van T. Dinh]

Try:

Me.LASTNAME = DLookup( "[LNAME]", "[TEST]", _
"([SLNUM] = '" & MSL & "') And (

• = '" & MLST & "')" )

 

[Pat Hartman \(MVP\)]

Me.LASTNAME = DLookup("[LNAME]", "[TEST]", "[SLNUM] = '" & MSL & "' And

• = '" & MLST & "'")
  
  When I have trouble with string syntax, I create a variable so I can see
  what it looks like as I am building it.
  
  Dim tempvar as string
  tempvar = "[SLNUM] = '" & MSL & "' And
  • = '" & MLST & "'"
    Me.LASTNAME = DLookup("[LNAME]", "[TEST]", tempvar)
    
    That way I can put a stop in the code and examine tempvar to see if it looks
    correct.
    
    

    Hi John,
    
    I think it's the right track but I'm still having syntax trouble. How do
    I
    write it when both variables are text?
    
    Thanks for your help.
    
    B
    
    

    Hi,
    
    I'm having a problem with this Dlookup function. Here's the line:
    
    Dim MSL As Variant
    Dim MLST As Variant
    
    MSL = Me.SLNUM
    MLST = Me.List
    
    Me.LASTNAME.Value = DLookup("[LNAME]", "[TEST]", "[SLNUM] = " & MSL & "
    And
    

    • = " & MLST)
      
      I get the error: "You cancelled the last operation."
      
      Any help?
      
      Thanks in advance.

    • 
      
      The "cancelled" error is sort of generic and many things can cause it.
      What's the context?
      
      I'm guessing that the problem may be due to the datatype of these
      fields. Is either SLNUM or LIST a Text type field? If so the search
      criterion must be delimited with either ' or " characters. Say if MSL
      is a text field, you could use
      
      Me.LASTNAME = DLookup("[LNAME]", "[TEST]", "[SLNUM] = '" & MSL & "'
      And
      • = " & MLST)
        
        Note also that you don't need to (and perhaps cannot, I don't recall)
        set the Value property of a textbox - just set the textbox.
        
        John W. Vinson [MVP]

 

[Guest]

Thanks ya'll. This is a big help. I always have trouble with the multiple
commas.

Bonnie

Try:

Me.LASTNAME = DLookup( "[LNAME]", "[TEST]", _
"([SLNUM] = '" & MSL & "') And (

• = '" & MLST & "')" )
  
  
  --
  HTH
  Van T. Dinh
  MVP (Access)
  
  
  
  

  Hi John,
  
  I think it's the right track but I'm still having syntax trouble. How do
  I
  write it when both variables are text?
  
  Thanks for your help.
  
  B

 

[Yanksfan07]

So I've been trying to do a dlookup function on a report that will return an
Items [Length] from the [Items] Table:
Here is the code I have
=DLookUp("Length","Items","[Item_ID]=" & [Item])
Where length is located in the items table and Item_ID is the primary key in
the items table as well. I'm wondering if the problem could be because the
[Item] field is also a dlookup (looking up the item_ID from the Orders Table
where the Customer_ID = Customer (which is input by the user))
The return result is #Error and I'm not sure why this is. If there is
anyone out there that can please help I would greatly appreciate it


Thanks,

Nate

 

[Steve Schapel]

Nate,

Is 'Item' the name of the textbox? If so, the first thing to check is
that there is not also a field named Item in the table/query that the
report is based on.

Another thing to confirm is that the Item_ID field is a number rather
than text?

Also, you mention that the Customer value is "input by the user". At
which point in the process does this happen? And where does the user
input the Customer?