dlookup not working!!

[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("CustomerName","Customers","customerCode=" & CustomerCode)

but i get different errors everytime i try it.

thanks to anyone who replies..

[mseo]

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

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]

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

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

or, alternatively, this

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]

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

[NeoPa]

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]

@jimatqsi
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]

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) :

         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 "customerCode=" 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]

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]

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]

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]

@jasmineeyes
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 "CustomerName" (since it seems to be in the Record Source) use "CustomerNameCtrl" 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.

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]

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

[Denburt]

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).
http://bytes.com/topic/access/insigh...ble-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]

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.

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]

'Data type mismatch' suggests that CustomerCode in Customers and CustomerCode in your form or CustomerTransactions table are different data types. For example, you might be storing the customer name in CustomerTransactions. 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:

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:

'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'")
 

[jimatqsi]

It's possible the datatype mismatch only came after the OP tried putting the customer code in quotes, thereby comparing an alpha customer code to a numeric from the table. It's not clear because the original poster did not identify the original error(s), only "i get different errors everytime i try it."

Jasmineeyes, I think you were really close originally. You had some little detail wrong, a misspelling or something.

Do you know to do an F9 to set a debug point in the code? You could do an F9 on your dlookup instruction and then when you run the code the program will pause before executing that instruction. You can look at values in the VBA editor just by pointing at a variable name, or type ?variablename in the immediate window.

Jim

[mseo]

hi,
jasmineeyes
try this

Private Sub CustomerCode_AfterUpdate()
    Dim strFilter As String
    strFilter = "customerCode= " & Me!customercode
    Me!customername= DLookup("CustomerName", "customers", strFilter)
end sub
 

if you still get the same error,you can send a Metadata of your table like this:

customercode; Autonumber; PK 
customername; String
deliveryaddress; String 
 

and if you can post the names of the controls in your Form, that will help us to understand the reason of the error
hope this helps

[NeoPa]

I expect you're a little overwhelmed at all the responses. I suspect this is as much to do with communication than your problem being difficult per se.

Without ignoring any of the responders (it's only polite to reply to all attempts at help), I suggest you take a little while first to see if SQL JOINs helps you understand your situation a little better. I expect a little understanding of the concepts of database queries would help fundamentally.

There are a lot of other good ideas in here. How many you will be able to appreciate at this time is another matter. Do what you can and, most importantly of all, explain any problems you have as clearly as you can. People will make efforts to help you at your level if you make it clear what it is you can understand. Clear communication is always critical.

As far as debugging goes, what you've been told already is good, but to fill in any gaps, more of a reference if you prefer, I provide Debugging in VBA. It may be a little obscure for you at this time, but can be used as a reference if required.

Remember, the most important issue is always communication. The clear expression of exactly what your problem is. With that we can be much more help generally.