Dlookup with numerical criteria

[ST4RCUTTER]

This should be very easy but I'm goofing it up somewhere. I am performing a simple Dlookup on a table where the value of a combo box is compared to the primary key field.

Dim xTimer As Boolean

xTimer = DLookup("Timer", "tbl_ref_assignment", "Me.AssignmentType = AssignmentID")

In this case Timer is a boolean field.
Me.AssignmentType is a combo box which contains a numeric integer value
AssignmentID is a primary key which is also a numeric integer

I need to find the "Timer" value which is either True or False for the record that matches the value in Me.AssignmentType but I keep getting a:

"Run-time error '2001': You cancelled the previous operation."

I've tried formatting the criteria in a number of ways but nothing works.

 

[dkinley]

Yah ... there is something backwards. Swap the combo box reference and the ID reference. DLookup wants to look up the assignment ID using the combo box reference, not look up the the combo box with the ID reference.

-dK

 

[ST4RCUTTER]

Thanks dkinley,

I reversed the order...I guess the transitive property only works with mathematics! Anyway here is the new code:

xTimer = DLookup("Timer", "tbl_ref_assignment", "AssignmentID = Me.AssignmentType")

Now when the code executes I receive 2001 run time error, then it says "This expression is typed incorrectly or is too complex to be evaluated. For example, a numeric expression may contain too many complicated elements. Try simplifying the expression by assigning parts of the expression to variables." Then it says "Object invalid or no longer set."

Now I believe this to be a bunch of hooey. I think I'm still just dealing with a syntax issue.

 

[Seecott]

It should probably be something like this.

xTimer = DLookup("Timer", "tbl_ref_assignment", "AssignmentID = " & Me.AssignmentType)

You can add Nz() around Me.AssignmentType just incase it's null.

 

[ST4RCUTTER]

Seecott,

That did the trick. That seems odd to me because the value that makes up the criteria is clearly a number, it's an autonumber primary key! I was under the impression that "& Me.xxx" was strictly used for text values. Thanks though!