dlookup not working!!

**mseo** · Jul 11 '10, 07:59 PM

hi,
Welcome to bytes
try this code, in afterupdate trigger use the names as in your table and form

Code:

dim c as integer
dim d as integer
c = DLookup("[customerID]", "customertable", "customername= '" & me.customerid)
d=DLookup("[customerID]", "customertable", "deliveryaddress= '" & me.customerid)
me.deliveryaddress=d
me.customercode=c

again use your names
hope this helps

**jimatqsi** · Jul 12 '10, 12:26 PM

Mseo, I'm not sure where you are going with that. Looks like you are assuming the customer code is not known and needs to be looked up. I think that's not the case.

Jasmineeyes, you don't say whether customer code is numeric or alpha. If it is alpha, you need to wrap quote marks around it. (It's a very common error).

Try this

Code:

CustomerName= dlookup("CustomerName","Customers","customerCode=""" & CustomerCode)& """"

or, alternatively, this

Code:

CustomerName= dlookup("CustomerName","Customers","customerCode="' & CustomerCode)& "'"

I sometimes have trouble with the latter example because the VBA editor wants to think I've started a comment in the middle of the code. Sometimes it works, sometimes not, and I've never discovered the key to always making the editor understand what I want.

Maybe someone else could chime in with a tweak to this.

Hope this helps,
Jim

**jimatqsi** · Jul 12 '10, 12:28 PM

Hmmm, wonder what happened to my post ... it's got a lot of blank lines in the code.

**NeoPa** · Jul 12 '10, 12:53 PM

I suppose the most likely thing you're talking about is a form. Assuming that, I would suggest the form be bound to a query rather than one of the tables. The query would include an INNER JOIN (Type 1 link) between the two tables based on the [customerCode] field. That way all data would be available to the form. No extraneous DLookups() required even.

**mseo** · Jul 12 '10, 01:04 PM

Originally posted by jimatqsi

Mseo, I'm not sure where you are going with that. Looks like you are assuming the customer code is not known and needs to be looked up. I think that's not the case.

Jasmineeyes, you don't say whether customer code is numeric or alpha. If it is alpha, you need to wrap quote marks around it. (It's a very common error).

Try this

Code:

CustomerName= dlookup("CustomerName","Customers","customerCode=""" & CustomerCode)& """"

or, alternatively, this

Code:

CustomerName= dlookup("CustomerName","Customers","customerCode="' & CustomerCode)& "'"

I sometimes have trouble with the latter example because the VBA editor wants to think I've started a comment in the middle of the code. Sometimes it works, sometimes not, and I've never discovered the key to always making the editor understand what I want.

Maybe someone else could chime in with a tweak to this.

Hope this helps,
Jim

hi,
in the main post the problem is about dlookup the customercode and delivery address for specific customer and view them in controls within a form, I supposed that the customerID is PK and in this case as soon as I select the customer from a combobox, I will get the controls of delivery address and customercode filled
I didn't think about it as you mentioned
thank you very much

**NeoPa** · Jul 12 '10, 01:04 PM

Originally posted by jimatqsi

jimatqsi: Maybe someone else could chime in with a tweak to this.

No problem Jim.

If you look at the posted example (I've indicated character positions) :

Code:

         1         2         3         4         5         6         7         8      
12345678901234567890123456789012345678901234567890123456789012345678901234567890123456
CustomerName= dlookup("CustomerName","Customers","customerCode="' & CustomerCode)& "'"

A single quote (') is treated as a comment indicator in VBA unless it is found within a string. If you look at the characters at positions #64 & #65, you'll notice the double-quote (") appears before the single-quote ('). As "customerCo de=" is a string, this means that the double-quote (") is closing the string. The single-quote (') therefore, is outside of a string, and correctly interpreted as a comment character. It should really be inside the string (swap the two characters at positions #64 & #65) as it is part of the string data. When done this way around you will not suffer from this behaviour.

**jimatqsi** · Jul 12 '10, 01:36 PM

Ah, I can't believe I made that goofball error ... oh, yes I can. So probably the latent mystery I feel about how VBA treats the ' is only because I wasn't observant enough to notice that sometimes I just did it wrong, and you only get out what you put in.

Thanks,
Jim

**NeoPa** · Jul 12 '10, 01:48 PM

No problem Jim :)

It would probably help a fair bit if the VBA IDE had a separate colour for string literals (as SQL Server does). That way we could all have that sort of problem highlighted clearly.

**jasmineeyes** · Jul 12 '10, 04:45 PM

ok, ive tried everuthing and i'm still getting an error.. "data type mismatch"
btw, the customercode is numeric.
NeoPa, sorry but im pretty new to this so i dont really understand what you meant by the inner join link.
jimatqsi, i'd like to dlookup the customerName and deliveryAddress based on the customerCode
Thanks alot..

**Denburt** · Jul 12 '10, 05:36 PM

Originally posted by jasmineeyes

i've beat my head on the table for this n spent hours searching the net but i cant find out whats wrong.. (btw, i'm pretty new to access..)

i've got a Customers table with information on customers, and another table, Customer Transactions. ive got a lookup box in the customer tranasactions table that looks up the customer code. i want the customer name and delivery address to automatically pop in their fields..

i tried using this in the after event:
CustomerName= dlookup("Custom erName","Custom ers","customerC ode=" & CustomerCode)

but i get different errors everytime i try it.

thanks to anyone who replies..

Maybe this will help...

If you are referring to fields in your form then use Me! to help distinguish between a field in the form and a field in the Record Source. I have seen MS Access get a little confused at times if you are not very specific.

Just my opinion but I always try to make sure my field names on the form or report do not have a similar or the same name as a field in the Record Source. Instead of a control on the form called "CustomerNa me" (since it seems to be in the Record Source) use "CustomerNameCt rl" I know that using drag and drop MS Access will name the control on the form or report the same as the field in the Record Source but I usually change it to prevent a number of issues like this.

Take a close look at the code below and see if it applies.

Code:

Me!CustomerName= dlookup("CustomerName","Customers","customerCode=" & Me!CustomerCode)

BTW NeoPa's post #5 would be more efficient and and a much cleaner way of resolving this.
I hope this helps let us know.

**jasmineeyes** · Jul 12 '10, 06:01 PM

yep, i think i'm through with dlookup..
i didnt really understand what NeoPa meant though..

**Denburt** · Jul 12 '10, 06:36 PM

Did you try ANY of the changes I suggested?

Looking your post over again I am wondering if the control on the form you are using for the customer code is pulled from a list box, combo box, or are you just typing it into a text box from memory?

The issue for us is to understand exactly how everything is set up and exactly what do you want to see on the form (table normalization for the tables used would help tremendously. See the below link).

503 Service Unavailable

http://bytes.com/topic/access/insights/585228-database-normalization-table-structures

Personally if I understand this correctly I would use a combo box using your customers table as your Row Source to select the customer code, you would have any number of columns in that combo box that you could reference on the form with several fields (delivery address etc.) The fields on the form that only need to be viewed and not stored can use the Control Source to reference the different columns on the combo box so the data can be seen on the form. The only info that needs to be stored in the Record Source (using the control source) should be that customer code (I would think).
It all depends on how the tables are set up and what you are using to reference them to each other.

**jimatqsi** · Jul 12 '10, 06:47 PM

Hmmm ... I'm a little unsure about the best approach for you. I think you are saying you don't know about building queries. If that's the case then you need to consider that as you decide where the most efficient solution lies.

If you're going to be in this Access project for a long future, you're going to need to learn about building queries, and today might be the day you start. If, however, this form is your one shot venture into the Access world, maybe it would be better to tinker with dlookup until you solve the little coding problem you've got.

If you want to fix the dlookup call, I'd simply debug through it to see what is happening. But you might first try changing it to include the Me keyword as Denburt suggested.

Code:

CustomerName= dlookup("CustomerName","Customers","customerCode=" & me!CustomerCode)

Because your customer code is numeric you don't need to enclose it in quotes as I first suggested.

Jim

**Steven Kogan** · Jul 12 '10, 07:24 PM

'Data type mismatch' suggests that CustomerCode in Customers and CustomerCode in your form or CustomerTransac tions table are different data types. For example, you might be storing the customer name in CustomerTransac tions. Check the table design to see what the data types are.

When you get the error message try typing 'Debug.print CustomerCode' in the 'Immediate Window'. (To open and close the Immediate Window press Ctrl-G.)

For debugging this problem you can also try adding these lines to your code:

Code:

debug.print dlookup("CustomerName","Customers")
debug.print CustomerCode
debug.print me!CustomerCode

You can also try a known value for the customer code and use it in your dlookup function. For example, if you have a customer with CustomerCode = 100, try this in the immediate window:

Code:

'See if customer code is numeric
debug.print dlookup("CustomerName","Customers","CustomerCode=100")
'See if customer code is a string
debug.print dlookup("CustomerName","Customers","CustomerCode='100'")

dlookup not working!!

dlookup not working!!

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment

Comment