DLookup - why this syntax?

[Ron]

After a couple frustrating weeks - typos and my own ignorance - I finally
got a functioning control source for a textbox (and it works as desired LOL)
:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatustxtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's how
I've seen it done here and elsewhere (without explanation for the syntax).
My (very noob) question is: Why is this concatenation necessary?

-Ron

 

[Arvin Meyer [MVP]]

Because you are using a variable, not a value. The variable hold the value,
but is not itself the value. If you were to use a variable from the code
instead of from the form it would look like:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatustxtBox.ItemsID
= " & lngID

 

[Ron]

Ok, thanks. But isn't the the first parameter also a variable? I guess
what it comes down to is that the function "knows" that parameter will
always be a field name, so the quotes requirement is simply "protocol."
(Just trying to get some insight into designation convention. Gotta read
more.)

Thanks. -Ron

Because you are using a variable, not a value. The variable hold the
value, but is not itself the value. If you were to use a variable from the
code instead of from the form it would look like:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatustxtBox.ItemsID
= " & lngID
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

After a couple frustrating weeks - typos and my own ignorance - I finally
got a functioning control source for a textbox (and it works as desired
LOL) :

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatustxtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's
how I've seen it done here and elsewhere (without explanation for the
syntax). My (very noob) question is: Why is this concatenation necessary?

-Ron

 

[Bob Quintal]

Ok, thanks. But isn't the the first parameter also a variable? I
guess what it comes down to is that the function "knows" that
parameter will always be a field name, so the quotes requirement
is simply "protocol." (Just trying to get some insight into
designation convention. Gotta read more.)

Thanks. -Ron

No, in your examplethe first paramater is a literal. You are telling
the DLookup function to look in the named field. in the filter
parameter you are telling Dlookup to look at the value contained in
lngID and use that value. Without the concatenation, DLookup would
try to filter on the literal string "lngID"

You can use a variable for the field, and for the table, as follows
in VBA code.
Dim MyField as string
Dim MyTable as string
Myfield = "FldStatus"
MyTable = "QryforCurrentStatusTxtBox"
something =Dlookup(""[" & MyField & "]"",""[" & MyTable & "]"", ...
etc., but that's not often necessary.

Q

Because you are using a variable, not a value. The variable hold
the value, but is not itself the value. If you were to use a
variable from the code instead of from the form it would look
like:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentS
tatustxtBox.ItemsID = " & lngID
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

After a couple frustrating weeks - typos and my own ignorance -
I finally got a functioning control source for a textbox (and it
works as desired LOL) :

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrent
StatustxtBox.ItemsID = " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the
entire criteria parameter in quotes. I put the quotes as shown,
because that's how I've seen it done here and elsewhere (without
explanation for the syntax). My (very noob) question is: Why is
this concatenation necessary?

-Ron

 

[Ron]

No, in your examplethe first paramater is a literal. You are telling
the DLookup function to look in the named field. in the filter
parameter you are telling Dlookup to look at the value contained in
lngID and use that value. Without the concatenation, DLookup would
try to filter on the literal string "lngID"

Well, lngID was from Arvin's example. I thought my OP criteria expression
contained only field names also. I guess the fact that the ID field is
expressed as a *form property* makes it a variable? Anyway, I'll keep
reading.

You can use a variable for the field, and for the table, as follows
in VBA code.
Dim MyField as string
Dim MyTable as string
Myfield = "FldStatus"
MyTable = "QryforCurrentStatusTxtBox"
something =Dlookup(""[" & MyField & "]"",""[" & MyTable & "]"", ...
etc., but that's not often necessary.

Thanks to both of you for the VBA suggestions. I may need them. Turns out
I forgot to anticipate that my DLookup expression generates an error when
the form moves to a new record (because some fields on which the query are
based are still null). So I may have to define the textbox controlsource
conditionally in code. (It'd be nice if there were an OnError() function
that works like IIF(). )

Thanks, -Ron

Because you are using a variable, not a value. The variable hold
the value, but is not itself the value. If you were to use a
variable from the code instead of from the form it would look
like:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentS
tatustxtBox.ItemsID = " & lngID
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com


After a couple frustrating weeks - typos and my own ignorance -
I finally got a functioning control source for a textbox (and it
works as desired LOL) :

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrent
StatustxtBox.ItemsID = " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the
entire criteria parameter in quotes. I put the quotes as shown,
because that's how I've seen it done here and elsewhere (without
explanation for the syntax). My (very noob) question is: Why is
this concatenation necessary?

-Ron

 

[Bob Quintal]

Well, lngID was from Arvin's example. I thought my OP criteria
expression contained only field names also. I guess the fact that
the ID field is expressed as a *form property* makes it a
variable? Anyway, I'll keep reading.

There is a difference between the name of a field and the contents of
that field. And yes, a textbox on a form is a sort of variable.

For your issue with a new record, it's a lot easier to move the
Dlookup to code, leaving the target textbox's control source blank.
You'd put it in the form's on current event, and surround it with a
if statement that tests for an empty textbox and skips doing the
lookup. You'd also put code to (re)do the lookup if you change that
textbox in its after_update event.

 

[John W. Vinson]

After a couple frustrating weeks - typos and my own ignorance - I finally
got a functioning control source for a textbox (and it works as desired LOL)
:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatustxtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's how
I've seen it done here and elsewhere (without explanation for the syntax).
My (very noob) question is: Why is this concatenation necessary?

-Ron

It's not that the third parameter needs to be in quotes; the function just
requires that it be *a text string*, in particular a text string which is a
valid SQL WHERE clause (without the word WHERE).

If you had a criterion such as

"[ZipCode] = '83660'"

with no variables, then a string constant would work fine.

However, it's much more useful to be able to *construct* the text string by
pieceing together fragments - e.g. a literal text string

"QryforCurrentStatustxtBox.ItemsID ="

and a variable value 3156 (pulled from the form reference). The DLookUp
function then ends up using

"QryforCurrentStatustxtBox.ItemsID = 3156"

as its criteria parameter.

 

[John W. Vinson]

Thanks to both of you for the VBA suggestions. I may need them. Turns out
I forgot to anticipate that my DLookup expression generates an error when
the form moves to a new record (because some fields on which the query are
based are still null). So I may have to define the textbox controlsource
conditionally in code. (It'd be nice if there were an OnError() function
that works like IIF(). )

A getaround is to use NZ() in the criteria parameter, to return some value
which doesn't correspond to any record if the control is NULL.

 

[Ron]

After a couple frustrating weeks - typos and my own ignorance - I finally

got a functioning control source for a textbox (and it works as desired
LOL)
:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatustxtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's
how
I've seen it done here and elsewhere (without explanation for the syntax).
My (very noob) question is: Why is this concatenation necessary?

-Ron

It's not that the third parameter needs to be in quotes; the function just
requires that it be *a text string*, in particular a text string which is
a
valid SQL WHERE clause (without the word WHERE).

If you had a criterion such as

"[ZipCode] = '83660'"

with no variables, then a string constant would work fine.

However, it's much more useful to be able to *construct* the text string
by
pieceing together fragments - e.g. a literal text string

"QryforCurrentStatustxtBox.ItemsID ="

and a variable value 3156 (pulled from the form reference). The DLookUp
function then ends up using

"QryforCurrentStatustxtBox.ItemsID = 3156"

as its criteria parameter.

Thanks John and thanks Bob for the helpful comments. Understood the
criteria parameter needs to be a string. You guys are gonna laugh at this,
but there's something even more fundamental that I didn't realize (and I
proved it for myself in debug): The concatenation of a string and a number
yields... a string! Doh! It all falls into place now. DLookup is mine!

I'm moving that unbound textbox expression from the control source property
to event methods. Since the box is on a main form, and the data on which
its value depends are in a combobox on a subform, and I'm inexperienced
, it's a matter of providing for different contingencies, since the user
could update existing records on both the main and subforms, and could also
add new records to either.

Thanks again. -Ron

 

[John W. Vinson]

I'm moving that unbound textbox expression from the control source property
to event methods. Since the box is on a main form, and the data on which
its value depends are in a combobox on a subform, and I'm inexperienced
, it's a matter of providing for different contingencies, since the user
could update existing records on both the main and subforms, and could also
add new records to either.

Note that neither a DLookUp nor a text literal will be updateable...

If you're having trouble post the SQL view of the form and subform's
recordsource and a bit more information about what you're trying to
accomplish. Having a single textbox on a mainform dependent on a combo box on
a subform (which could represent thousands of records) seems suspect to me!

 

[Ron]

Note that neither a DLookUp nor a text literal will be updateable...

So far it's working. I have the DLookup executing from the main form change
event (only if record is not new) and from the subform combobox's
after_update. I need to add a few more dummy records and make sure all
contingencies are covered.

If you're having trouble post the SQL view of the form and subform's
recordsource and a bit more information about what you're trying to
accomplish.

Thank you! You actually helped me already in another thread.

Having a single textbox on a mainform dependent on a combo box on
a subform (which could represent thousands of records) seems suspect to
me!

It's a conceit really. The app is for my wife's hobby jewelry business.
The subform's record source is a query run on the join table between Items
and Status. The subform has a combobox for status (eg. construction
finished, for sale, placed on consignment, sold, etc. - just a few, not
thousands) and a text box formatted for date (when the status changed). The
subform is to track the progress of jewelry making , from construction
through sale, for each item in inventory. So every time an item's status
changes, she can add a record to the subform, choosing status in the combo
box and the date in the text box. The conceit of the text boxes on the
*main form* is to show the status with the *latest* date - ie. the latest
entry in the subform. Just for easy visualization in the main form as she
scrolls through the inventory. It's not necessary, but it improves the
esthetics, and I wanted to learn how to do it. You helped by pointing me to
the notion of a query with a subquery using DMax. I run that on the join
table from which the subform is generated. That gives a dataset with only
the *latest* status and date for each item of jewelry (of which there'll
probably never be more than several hundred), and that becomes the domain of
the DLookup, whose criteria gives the row for the *current* item of jewelry
in the main form. Hope that conveys the flavor of the project.

No immediate coding issue that I can see. I'm sure there'll be more as I
implement tabs or new forms for other tables in my design (suppliers,
customers, craft show contacts, etc.). Thanks for the offer. Will post if
new issues arise. -Ron

Incidentally, the original motivation behind all this - aside from the
simple kick I get from it - is to create a prototype of a data-driven
website. I thought to model a MySQL/PHP app from it. But I see now that
Access 2010 will have direct website capability. I'm hoping that will make
the process even easier. (Those "where's my website?" questions are getting
a tad more frequent... )

 

[John W. Vinson]

The conceit of the text boxes on the
*main form* is to show the status with the *latest* date - ie. the latest
entry in the subform. Just for easy visualization in the main form as she
scrolls through the inventory.

Sounds like a very nice touch. Thanks for the explanation!

 

[Arvin Meyer [MVP]]

Yes but there are form variables and Dim'd variables. They take similar but
slightly different syntax.
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.accessmvp.com
http://www.mvps.org/access

Ok, thanks. But isn't the the first parameter also a variable? I guess
what it comes down to is that the function "knows" that parameter will
always be a field name, so the quotes requirement is simply "protocol."
(Just trying to get some insight into designation convention. Gotta read
more.)

Thanks. -Ron

Because you are using a variable, not a value. The variable hold the
value, but is not itself the value. If you were to use a variable from
the code instead of from the form it would look like:

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatustxtBox.ItemsID
= " & lngID
--
Arvin Meyer, MCP, MVP
http://www.datastrat.com
http://www.mvps.org/access
http://www.accessmvp.com

After a couple frustrating weeks - typos and my own ignorance - I
finally got a functioning control source for a textbox (and it works as
desired LOL) :

=DLookUp("[fldStatus]","QryforCurrentStatusTxtBox","QryforCurrentStatustxtBox.ItemsID
= " & [Forms]![frmMainForm].[id])

The generic formulations for DLookup that I see in books has the entire
criteria parameter in quotes. I put the quotes as shown, because that's
how I've seen it done here and elsewhere (without explanation for the
syntax). My (very noob) question is: Why is this concatenation
necessary?

-Ron