UtterAccess.com
X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
 
   Reply to this topicStart new topic
> Need Help Dlookup Syntax, Access 2013    
 
   
mmunkey
post Sep 21 2017, 11:07 AM
Post#1



Posts: 20
Joined: 22-October 14



Pulling my hair out. Have done limited VBA programming and try to get some code to work. I have an unbound box on my form I'm trying to populate with data from a table that is relational to a value in the current tale using DLookup

Dim tmpplate As String
MsgBox invvin
tmpplate = DLookup("[vplate]", "Vehicle", "[vvin] =" & "'" & [invvin] & "'")
MsgBox tmpplate

Where the external table "Vehicle" has text values in both the "vvin" and "vplate" fields and the current form has a text field "invvin"
When I run the above Msgbox returns the correct value for invvin then I get a incorrect use of null.
I also tried this
tmpplate = DLookup("[vplate]", "vehicle", "[vvin] = ""& [invvin]""")
with the same results and have confirmed the data does reside in the vehicle tale . What am I doing wrong. I suspect syntax in the criteria

In another unbound box I used this which works properly but it was using numeric data in the criteria
Text53 = DLookup("[clastname]", "client", "[ccode] =" & [inccnum#])
Go to the top of the page
 
theDBguy
post Sep 21 2017, 11:12 AM
Post#2


Access Wiki and Forums Moderator
Posts: 71,239
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Double-check spellings to make sure they're correct. The syntax is basically:

Me.TextboxName = DLookup("FieldName", "TableName", "CriteriaField='" & Me.ControlName & "'")

Hope it helps...

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Microsoft Access MVP | Access Website | Access Blog | Email
Go to the top of the page
 
BruceM
post Sep 21 2017, 11:44 AM
Post#3


UtterAccess VIP
Posts: 7,024
Joined: 24-May 10
From: Downeast Maine


I expect the parameter is a VIN, which wouldn't have an apostrophe, but I'll just mention anyhow that if there is an apostrophe in the parameter you need to delimit with double quotes, replacing each single quote with two double quotes:

tmpplate = DLookup("[vplate]", "Vehicle", "[vvin] =""" & [invvin] & """")

Actually, there are other ways of handling quotes within quotes, but there's no need to get into that here.

A likely suspect, as DBGuy suggested, is the spelling, but it could be that there is no record where vvin = invvin, so the DLookup returns Null. tmpplate is a string variable, which does not accept Null, thus the error.
Go to the top of the page
 
mmunkey
post Sep 21 2017, 12:01 PM
Post#4



Posts: 20
Joined: 22-October 14



Checked the names they are perfect. This is what I ran
Dim tmpplate As String
MsgBox invvin
tmpplate = DLookup("[vplate]", "vehicle", "[vvin] =""& [invvin]&""")
MsgBox tmpplate
'''tmpplate = DLookup("[vplate]", "vehicle", "[vvin] = ""& [invvin]""")
''''tmpplate = DLookup("[vplate]", "Vehicle", "[vvin] =" & "'" & [invvin] & "'")
'''tmpplate = DLookup("[vplate]", "vehicle", "[vvin] = ""& [invvin]""")
The texted out commands are all the ones I have tried. When I ran the code msgbox returned invvin as test. I ran a query using test as the criteria and it returned properly
SELECT vehicle.vvin, vehicle.vplate, vehicle.Code, vehicle.vnotes
FROM vehicle
WHERE (((vehicle.vvin)="test"));
I just don't get it
Go to the top of the page
 
RAZMaddaz
post Sep 21 2017, 12:13 PM
Post#5


UtterAccess VIP
Posts: 9,522
Joined: 23-May 05
From: Bethesda, MD USA


mmunkey,

I can't see all the quotes you have tried already, but please try the following:

DLookup("[vplate]", "vehicle", "[vvin] =' " & [invvin] &" ' ")

Do not include the spacing before and after the invvin, I just added them so you could see everything.

RAZMaddaz
Go to the top of the page
 
BruceM
post Sep 21 2017, 12:19 PM
Post#6


UtterAccess VIP
Posts: 7,024
Joined: 24-May 10
From: Downeast Maine


The quotes are not correct. It is either (spaces between quotes added for clarity):

"[SomeField] = ' " & Me.SomeControl & " ' "

or

"[SomeField] = " " " & Me.SomeControl & " " " "

Try using a string variable for the criteria:

Dim strCrit As String

strCrit = "[vvin] = '" & [invvin] & "'"

Debug.Print strCrit ' or MsgBox strCrit

tmpplate = DLookup("[vplate]", "vehicle", strCrit)

This will let you take a look at the criteria to be sure it is what you need. BTW, if invvin is a control on the form or a field in the form's record source, you would do well to use the Me prefix: Me.invvin. For one thing the Intellisense menu will show a list of possibilities after typing Me{dot}, and reduce the chance of a typo. For another the prefix specifies that what follows is a property of the form and nothing else, so it narrows down where Access has to search. It is not likely to make a practical difference in the speed, but there is no reason not to use the available tools.

Also, be sure you have Option Explicit at the top of the code module, directly below Option Compare Database. Then compile the code.

Edit: I see Raz and I had the same approach to describing the quotes.
Go to the top of the page
 
mmunkey
post Sep 21 2017, 01:15 PM
Post#7



Posts: 20
Joined: 22-October 14



working now...Its ridicules that the spacing and syntax is that critical. vb should recognize the intent and correct it. However ty so much for your help it was driving me nuts. Is there any tools out there for vb that automate this or make it simpler?
Go to the top of the page
 
mmunkey
post Sep 21 2017, 01:22 PM
Post#8



Posts: 20
Joined: 22-October 14



BruceM pls explain explicit ( trying to learn) The sheet I was on was the sheet for the form that popped up when I created my first code on the form and only had compare database. What is the advantage of the explicit and where do I apply it. Its working without the Me(dot) now but if I was to apply it would it still be in []. Unfortunately its long periods between my VBA coding and unless something really sticks in my head its forgotten. Lol not that I really was any good at it but I generally get by. I enjoy it and learn as I go. ty for teaching me
Go to the top of the page
 
BruceM
post Sep 22 2017, 07:00 AM
Post#9


UtterAccess VIP
Posts: 7,024
Joined: 24-May 10
From: Downeast Maine


Option Explicit requires that variables be declared. If variables are not declared Access assumes they are of variant type, which is the only variable that accepts null. Without Option Explicit, if you mistype a function name (Dare instead of Date, or whatever) Access may assume Dare is a variant variable. If Dare instead of Date is used as a criterium (Something = Dare) the effect may be Something = Null. There is more information about this and other debugging techniques here, including how to switch it on by default. It mystifies me that the default is not to require variable declarations.

As for VBA being fussy about the syntax, perhaps you are thinking of the way Excel suggests corrections. One problem with having something like that in VBA is that there is no way to sort out a string, since its contents could be anything. If you use two double quotes in a row where you intended three, Access has no way to know whether you intended one or three quotes. All it knows is that two won't work, and the line will be highlighted in red. If Auto Syntax Check is turned on (the same place where you turn on Require Variable Declaration as described in the link) it may spot some of those things and let you know right away, but after a while you are likely to prefer that it not try to be so helpful. I turned off that option years ago.

Perhaps Access could realize that a declared Long variable should not be surrounded by quotes, but what if it is a situation where for some reason you need to render the value as text? You would need to tell Access to stop helping, or perhaps you could just ignore the suggestion, but I think over time you will prefer it not try to help too much.

If you use the Me prefix I think you will find that square brackets are used automatically only if the name has spaces or special characters. I never have such things in names, so I don't recall for sure.

Note that the expression service, which is used in query and text box expressions (that is, not VBA), tends to surround just about everything with square brackets. However, it does not use square brackets for query fields unless they have spaces or special characters, so you may end up with:

SELECT tblEmployee.ID, tblEmployee.DeptCode, [FirstName] & " " & [LastName] As FullName FROM tblEmployee

In a text box control source:

=[MyComboBox].[Column](1)

You can't get rid of the square brackets in this case, even though Column is a property and there is no need for the brackets around it. But they do no harm, so there it is.
Go to the top of the page
 


Custom Search
RSSSearch   Top   Lo-Fi    18th December 2017 - 03:29 PM