VBA Dlookup up error  what is wrong with my method ,or code

Ossie_Jesson
Ossie_Jesson used Ask the Experts™
on
Dear experts
I amd doing something that returns this error
" Run time error 3075 Syntax error (missing operator) in query CustomerID= ."

VBA Dlookup up error

background
Access beginner    (yes I am an Access Dummy )
VBA very very Green hand    cut and paste only

 I am attempting to  use  dlookup  for the first time. I have followed an example  from an online tutorial [ which I suspect was published  with  access 2003 ] its here http://599cd.com/tips/access/dlookup-function/?key=AllExperts
please  realise that I do not understand yet the term "  control " or "bound "  I understand  look up column as that is what I  use regularly


Why am I using dlookup up?  because  in many situations  I have found that  I want to bring more than one column into a table when I use a look up column   i.e. if I  use a lookup to pick company  to go in the  incident table  why cant  it bring its  buddies along too ..     Address and post code??  I have been assured by Access Experts that.. As with nearly EVERYtiNg that you want to do in access to achieve something..."  OH NOO LAD you cant do that !! NOT  unless you use VBA " so HERE i am trying VERY HARD  to do something very simple and use vba to help the job along

My need is to get address field populated in a form based on an incident table when I look up company  from the customers table if I can learn to do that then surely I can progress to doing it  somewhere real  and useful

Situation: - I have two tables customers and incident (these are example tables only to try to get the dlookup to work before I apply it to real situation
The company  column in the  incident table   is a lookup column  that gives me a drop down where I pick  the company name  to populate company   in the incident  tbl /form . in a vague kind of way I am aware that  company is tied to the customer ID  so that is what is actually being picked  just what the implications are for that arr in the wide world of vba I am clueless

I have
1 created the form Incident 2  clicked on design  mode  3 clicked on the company field  4open the properties  sheet and in 5 the event tab   on the line after update 6   clicked on the ... button chosen code builder   and then 7 pasted in the line of code  where the tutorial seemed to indicate & saved & closed
8 Returned to the from ,9into from view and 101 entered a row of data using the company drop down  and then  I get the error  message  when I  click  on for a new record
I have attached the file of the data base

I suspect that in my haste to learn and get some work done I have in my gross ignorance missed a trick somewhere
Your help would be appreciated in shedding light on what the trick is that dumbo has missed.

If I can get this to work ,this will give me a momentary feeling of elation that might help me to continue using this   infuriating, antagonising software a for another day !!
Thanks

Option Compare Database
 
Private Sub Company_AfterUpda()
End Sub
 
Private Sub Company_AfterUpdate()
Address = DLookup("[Address]", "[CustomersTbl]", "[CustomerID] = " & CustomerID)
End Sub

Open in new window

dlookup-trial-mdb.mdb
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Most Valuable Expert 2012
Top Expert 2014

Commented:
Is "CustomerID" a Text value? If so, you must do this:
 
Private Sub Company_AfterUpdate()
Address = DLookup("Address", "CustomersTbl", "[CustomerID] = '" & CustomerID & "'")
End Sub

Note also I've removed the square brackets surrounding your first two arguments ... those are string values, and should be passed exactly as needed. The third argument should be a valid WHERE clause (without the word WHERE), and thus the square brackets aren't really needed.

In general, most VBA code will work in 07, even if it's written for 03 or earlier. That's not always the case, but the DLookup functions exactly the same.

Note also that you should NOT store any other data in your Incidents table except for the CustomerId value ... this is the "relating" field in that table, and you should not store other information about the customer in the INcidents table.


Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
Ossie,
   You might want to take a look at the article I wrote on DLookup and domain functions.  There are a few tips/tricks in there to help you get started with Access:
http://www.experts-exchange.com/articles/Microsoft/Development/MS_Access/Dlookup-and-the-Domain-Functions.html
HTH,
JimD.

Author

Commented:
THANKS ANYWAY   BUT

iTS ALL BECOMING AS CLEAR AS MUD NOW

 THAT CODE DOESNT WORK EITHER   i DID INCLUDE THE DATABASE  TO TRY AND MAKE THINGS AS CLEAR AS POSSIBLE aS FAR AS i AM ABLE TO DETERMINE  THE  CUSTOMERID IN THE CUSTOMERS TABLE  IS A NUMBER FIELD

IN REGARDES TO WHERE THE DATA GOES AND IN WHICH TABLE  WHAT IF if I WANTED TO PRINT OFF AN INCIDENT REPORT AND INCLUDE THE ADDRESS  HOW WOULD THAT HAPPEN IF I COULDNT INCLUDE IT IN THE INCIDENT  FORM (BASED ON THE INCIDENT  TABLE ) THAT PRODUCED THE REPORT THAT WAS TO BE PRINTED ? AM I MISSING SOMETHING ? IS IT POSSIBLE TO GET DATA TOPRINT OUT WITHOUT IN BEING IN THE TABLE THAT  THE PRINT OUT IS BASED ON ??
Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Jim Dettman (EE MVE)President / Owner
Most Valuable Expert 2017
Most Valuable Expert 2012

Commented:
When you do a form or report in Access, you can base it on a query; that is a view of the data, which can be from one or more tables. Within a query you can filter, sort, format, etc the data presented.
Since your just starting out, my suggestion would be to use one of the built-in templates and create a database. Then look at that to see how things are done.
JimD.
Most Valuable Expert 2012
Top Expert 2014
Commented:
Your code should look like this:

Address = DLookup("[Address]", "[Customers]", "[CustomerID] = " & Me.Company)

First, you have CustomersTbl as the name of the Table, which was incorrect.

Next, you were referring to CustomerID, and there was no Field in the Customers table by that name. I assume you meant to refer to the Company combo, so I changed it.

Now when I select a Company, the Address field fills with the Address.

Also, please do NOT post in ALL CAPITALS. It's very difficult to read, and is considered rude by many of the Experts here on EE (and on other forums as well).

Finally - the Help file in Access is a great place to start when you have issues. While you may not have been able to figure out the second part, a review of the DLookup topic probably would have lead you to the first issue (i.e. the wrong table name).

Author

Commented:
My apologise for the capitals. The comment I  submitted was a rather head down and write in between other jobs affair and i hadnnt noticed  that it was  all in caps before pulling the  trigger and submitted it to the site

I hope to get time later to trial the code you have put in you post I will get back to you with the outcome

Thanks for your time   Ossie Jesson

Author

Commented:
Ok I have used the code and it works  thank you very much I am on my way again

Though for clarity in my education was the fault  1 ) that I have got tbl at the end of  Customers
2  though there is a CustomersID  field in the Customers table   it is not needed or relevant in the code perhaps becase it is the primary Key ?  or  some other reason

and 3 what does the  "&Me"  refer to please ?

Thanks

Author

Commented:
Thanks  for the code On my way again
Most Valuable Expert 2012
Top Expert 2014

Commented:
First: If the code I suggested worked, then why would you award this a B grade? I see that you're a fairly new member, so you may want to review this:

http://www.experts-exchange.com/help.jsp?hi=403

In this case, the solution worked, and as such should have been awarded no less than an A. If you'd like to change this grade, use the Request Attention link at the top of this question to ask the Moderators to do so.

Next:

1) Yes, the trouble was that (a) you had not correctly identified the table and that (b) you were referring to a value that did not exist on your form. The basic syntax for DLookup is:

DLookup("Field you want to return", "Table you want to look in", "Some valid where clause")

2) You were attempting to return the value of Customers.Company, based on the value in Customers.CustomerID. However, the CONTROL on the form which contained the value of CustomerID was named "Company" (i.e. your combo), so the Dlookup had to refer to that control to retrive the value you wished to lookup.

3) In Access, "Me" refers to the active object, whatever that may be. It's only used on Forms and Reports (that is, you cannot use it in a stand-along module). The "&" is used to paste strings together. The syntax used in the third argument:

"[CustomerID] = " & Me.Company

Basically tells Access to write whatever is in Me.Company to that placeholder in the string, so if the value in the Company dropdown is 9, Access would pass this string to the DLookup:

[CustomerID] = 9




Author

Commented:
Thank  you for taking the time to come back and explain things  which you have done very clearly .

Thanks .You make it almost sound as if Access is a creature open to reasoned argument  rather than the brute beast  I generally seem to be wrestling with.
 Any how ,I  will learn from this ,and in future perhaps ask  .. please explain questions rather than fix it for me   questions  .,enabling  an expert such as yourself  to illuminate  the way rather than try and drag them down into  my dark hole to fix the darn thing which I have broke or misused  in a rash attempt to just  get things done ...  now   .

So thanks for your time   .
Ossie Jesson

Ps I will  do my best to  remedy  the point thing after work today

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial