Using DLookup with a control variable

[Rich1968]

I have a data entry form. On this form are a number of fields I would like to populate with information based on the Account type of the Customer. ie. nsf checks, apartment leases, legal claims etc.

When I enter the Customer Id I use a combo box to pull down a list and choose the correct customer ID. when I move to the next field I want the before update expression to populate the fields with information based on the account type. Heres the code I came up with but it's not working and I am at a loss as to why.

'Dim VarX As Variant

'VarX = DLookup("[Account Type]", "Customers", "[Customer ID]= Me![Customer ID]")

'If VarX = "9" Then Me![Account Type] = "9"

What im trying to accomplish is automating various fields based on the customers account type.

The error I get is "The object doesn't contain the automation object "Me!Customer ID"

If I want to list every customer ID I would have to make 1000 entrys to make it work. This would look like the following expression.

'If [Customer ID] = "Sun" Then [Account Type] = "9"

HELP!

Rich1968

 

[Mile-O]

This line is wrong:

'VarX = DLookup("[Account Type]", "Customers", "[Customer ID]= Me![Customer ID]")

If Customer ID is text, it should be:

VarX = DLookup("[Account Type]", "Customers", "[Customer ID] = '" & Me![Customer ID] & "'")

If it's numerical:

VarX = DLookup("[Account Type]", "Customers", "[Customer ID] = " & Me![Customer ID])

 

[pdx_man]

Hey Rich, did you read and implement my response to your original post?

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=40931