VBA Dlookup up error what is wrong with my method ,or code
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
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
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-exchang
HTH,
JimD.
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-exchang
HTH,
JimD.
ASKER
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 ??
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 ??
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.
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.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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
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
ASKER
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
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
ASKER
Thanks for the code On my way again
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:
https://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
https://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
ASKER
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
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
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.