By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,728 Members | 1,404 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,728 IT Pros & Developers. It's quick & easy.

dlookup not working!!

P: 3
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..
Jul 11 '10 #1
Share this Question
Share on Google+
17 Replies


mseo
100+
P: 181
hi,
Welcome to bytes
try this code, in afterupdate trigger use the names as in your table and form
Expand|Select|Wrap|Line Numbers
  1. dim c as integer
  2. dim d as integer
  3. c = DLookup("[customerID]", "customertable", "customername= '" & me.customerid)
  4. d=DLookup("[customerID]", "customertable", "deliveryaddress= '" & me.customerid)
  5. me.deliveryaddress=d
  6. me.customercode=c
  7.  
again use your names
hope this helps
Jul 11 '10 #2

Expert 100+
P: 1,240
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
Expand|Select|Wrap|Line Numbers
  1. CustomerName= dlookup("CustomerName","Customers","customerCode=""" & CustomerCode)& """"
or, alternatively, this
Expand|Select|Wrap|Line Numbers
  1. 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
Jul 12 '10 #3

Expert 100+
P: 1,240
Hmmm, wonder what happened to my post ... it's got a lot of blank lines in the code.
Jul 12 '10 #4

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 12 '10 #5

mseo
100+
P: 181
@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
Jul 12 '10 #6

NeoPa
Expert Mod 15k+
P: 31,769
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) :
Expand|Select|Wrap|Line Numbers
  1.          1         2         3         4         5         6         7         8      
  2. 12345678901234567890123456789012345678901234567890123456789012345678901234567890123456
  3. 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.
Jul 12 '10 #7

Expert 100+
P: 1,240
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
Jul 12 '10 #8

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 12 '10 #9

P: 3
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..
Jul 12 '10 #10

Denburt
Expert 100+
P: 1,356
@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.
Expand|Select|Wrap|Line Numbers
  1. 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.
Jul 12 '10 #11

P: 3
yep, i think i'm through with dlookup..
i didnt really understand what NeoPa meant though..
Jul 12 '10 #12

Denburt
Expert 100+
P: 1,356
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.
Jul 12 '10 #13

Expert 100+
P: 1,240
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.
Expand|Select|Wrap|Line Numbers
  1. 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
Jul 12 '10 #14

Expert 100+
P: 107
'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:
Expand|Select|Wrap|Line Numbers
  1. debug.print dlookup("CustomerName","Customers")
  2. debug.print CustomerCode
  3. debug.print me!CustomerCode
  4.  
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:

Expand|Select|Wrap|Line Numbers
  1. 'See if customer code is numeric
  2. debug.print dlookup("CustomerName","Customers","CustomerCode=100")
  3. 'See if customer code is a string
  4. debug.print dlookup("CustomerName","Customers","CustomerCode='100'")
  5.  
Jul 12 '10 #15

Expert 100+
P: 1,240
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
Jul 12 '10 #16

mseo
100+
P: 181
hi,
jasmineeyes
try this
Expand|Select|Wrap|Line Numbers
  1. Private Sub CustomerCode_AfterUpdate()
  2.     Dim strFilter As String
  3.     strFilter = "customerCode= " & Me!customercode
  4.     Me!customername= DLookup("CustomerName", "customers", strFilter)
  5. end sub
  6.  
if you still get the same error,you can send a Metadata of your table like this:
Expand|Select|Wrap|Line Numbers
  1. customercode; Autonumber; PK 
  2. customername; String
  3. deliveryaddress; String 
  4.  
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
Jul 13 '10 #17

NeoPa
Expert Mod 15k+
P: 31,769
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.
Jul 13 '10 #18

Post your reply

Sign in to post your reply or Sign up for a free account.