just to satisfy my curiousity what the difference? - =DLookUp("[order received]","[qryorder received]","[order ID] = '" & Forms!mainform![order id] & "'")
- =DLookUp("[order received]","[qryorder received]","[order ID] = [Forms]![mainform]![order id])
i apologise if im digressing the thread
Dan
6  1415 
The difference is as follows:
When "[somefield] = [somecontrol]" is passed as a string to the function there is no attempt to substitute a value for the [somecontrol] reference. The code behind the DLookup function must then evaluate what [somecontrol] means, if it can. It may not be interpretable at all - in which case it will treat the [somecontrol] reference as an unknown parameter missing a value and return an error accordingly.
When the expression is sent as "[somefield] = '" & [somecontrol] & "'" the expression is evaluated before the string is sent to the function, and the value of the control is substituted for the control reference. Say the control contained ABC123 as its value. The string sent to the DLookup function would be
"[somefield] = 'ABC123'"
which it can interpret without trying to obtain a value from an unknown control.
-Stewart
so i presume that your method is faster/efficient does the same apply in SQL WHERE, VBA etc,
Dan, there are many posts in which the error message 'too few parameters - expected 1' has arisen (try a search for that phrase and you will see what I mean). This arises when queries and other expressions that refer directly to form controls in their WHERE clauses fail on being run within VBA code (for example where the query concerned is the source specified for the DAO OpenRecordset method).
The problem does not arise consistently, because within the query editor say Access can interpret most of these references before they get to the inner core of the database engine - but OpenRecordset and similar VBA functions working on objects within the inner core of the database do not 'see' the forms environment, and are not able to interpret such references.
Even when run from the Access Query Editor such form field references will fail in some circumstances. A form control reference in the WHERE clause will work absolutely fine in a SELECT query and fail entirely if the same query is changed to be a crosstab - except this time Access will return an error message saying that the reference concerned is an unrecognised field name.
It's not a matter of efficiency, but of being able to do it at all in some cases. A consistent approach is that in code you should always use the value of the control concerned instead of trying to pass the name of the control as part of a WHERE clause or similar element.
-Stewart
can't say i've had any problems myself so far but i will change my method
thanks
Dan
NeoPa 32,564
Recognized Expert Moderator MVP
This (quite interesting) question has been split off from http://bytes.com/topic/access/answers/866790-dlookup.
PS. I would certainly go along with Stewart on all of this Dan. When creating SQL in VBA, use the literal value where possible. Save the references for QueryDefs which are run without VBA control, keeping them to the bare minimum.
Sign in to post your reply or Sign up for a free account.
Similar topics | |
by: ckerns |
last post by:
I want to loop thru an array of controls,(39 of them...defaults = 0).
If value is null or non-numeric I want to assign the value of "0".
rowString = "L411" //conrol name
if (isNaN(eval ("document.forms."+rowString+".value")) == true )
{
//this alert works if the value is a letter,i.e,"a"
alert("You have entered an non-numeric...
|
by: hasho |
last post by:
Why is "call by address" faster than "call by value"?
|
by: z_learning_tester |
last post by:
I'm reading the MS press C# book and there seems to be a contradiction.
Please tell me which one is correct, 1 or 2.
Thanks!
Jeff
1. First it gives the code below saying that it prints 0 then 42.
They say that 42 is printed the second time since the value was wrapped in a
class and therefore became passed by reference.
(sorry for any...
|
by: daniel |
last post by:
This is a pretty basic-level question, but I'd really like to know, so
thanks for any help or pointers you can provide (like what I would
google for ;o)
Suppose:
<code>
myFunc()
{
|
by: ALI-R |
last post by:
Hi All,
First of all I think this is gonna be one of those threads :-) since I have
bunch of questions which make this very controversial:-0)
Ok,Let's see:
I was reading an article that When you pass a Value-Type to method call
,Boxing and Unboxing would happen,Consider the following snippet:
int a=1355;
myMethod(a);
......
| | |
by: Arne |
last post by:
Will the dataset below be returned by value or reference?
Public Shared Function getDS() As DataSet
Dim ds As New DataSet
'... do something
Return ds
End Function
|
by: dotnetnoob |
last post by:
i keep getting Object references not set to an instance of an object from
this code:
Private Sub EqBinding()
Dim x As Integer
x = 0
Do
If CStr(arlsType.Item(x)) = "Bacnet Point" Then
Dim str1, str2 As String
str1 = CStr(arlsFilePath.Item(x))
|
by: Jon Slaughter |
last post by:
I'm reading a book on C# and it says there are 4 ways of passing types:
1. Pass value type by value
2. Pass value type by reference
3. Pass reference by value
4. Pass reference by reference.
My interpretation:
1. Essentially pushes the value type on the stack
|
by: Robert Dailey |
last post by:
Hi,
I noticed in Python all function parameters seem to be passed by
reference. This means that when I modify the value of a variable of a
function, the value of the variable externally from the function is
also modified.
Sometimes I wish to work with "copies", in that when I pass in an
integer variable into a function, I want the...
|
by: Jim Langston |
last post by:
I remember there was a thread a while back that was talking about using the
return value of a function as a reference where I had thought the reference
would become invalidated because it was a temporary but it was stated that
it would not. This has come up in an irc channel but I can not find the
original thread, nor can I get any code to...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main...
| | |
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it. ...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in...
| | |
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
