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

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


"Ron" <Harvested@comcast.net> wrote in message 
news:hl4n2e$63g$1@news.eternal-september.org...
> 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
>
> 

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

"Arvin Meyer [MVP]" <arvinm@mvps.invalid> wrote in message 
news:O$Mp20FrKHA.6140@TK2MSFTNGP05.phx.gbl...
> 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
>
>
> "Ron" <Harvested@comcast.net> wrote in message 
> news:hl4n2e$63g$1@news.eternal-september.org...
>> 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
>>
>>
>
> 

"Ron" <Harvested@comcast.net> wrote in
news:hl59j0$h30$1@news.eternal-september.org: 

> 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


> 
> "Arvin Meyer [MVP]" <arvinm@mvps.invalid> wrote in message 
> news:O$Mp20FrKHA.6140@TK2MSFTNGP05.phx.gbl...
>> 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
>>
>>
>> "Ron" <Harvested@comcast.net> wrote in message 
>> news:hl4n2e$63g$1@news.eternal-september.org...
>>> 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

PA is y I've altered my email address.

>> 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"

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

>
>
>>
>> "Arvin Meyer [MVP]" <arvinm@mvps.invalid> wrote in message
>> news:O$Mp20FrKHA.6140@TK2MSFTNGP05.phx.gbl...
>>> 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
>>>
>>>
>>> "Ron" <Harvested@comcast.net> wrote in message
>>> news:hl4n2e$63g$1@news.eternal-september.org...
>>>> 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" <Harvested@comcast.net> wrote in
news:hl6h8e$3eu$1@news.eternal-september.org: 

> 
>>> 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"
> 
> 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.

 

-- 
Bob Quintal

PA is y I've altered my email address.

On Fri, 12 Feb 2010 18:08:58 -0500, "Ron" <Harvested@comcast.net> wrote:

>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 [MVP]

On Sat, 13 Feb 2010 10:42:01 -0500, "Ron" <Harvested@comcast.net> wrote:

>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.
-- 

             John W. Vinson [MVP]

>>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 

On Sat, 13 Feb 2010 21:55:38 -0500, "Ron" <Harvested@comcast.net> wrote:

>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!
-- 

             John W. Vinson [MVP]

> 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... :)   ) 

On Sun, 14 Feb 2010 01:53:21 -0500, "Ron" <Harvested@comcast.net> wrote:

>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!
-- 

             John W. Vinson [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


"Ron" <Harvested@comcast.net> wrote in message 
news:hl59j0$h30$1@news.eternal-september.org...
> 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
>
> "Arvin Meyer [MVP]" <arvinm@mvps.invalid> wrote in message 
> news:O$Mp20FrKHA.6140@TK2MSFTNGP05.phx.gbl...
>> 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
>>
>>
>> "Ron" <Harvested@comcast.net> wrote in message 
>> news:hl4n2e$63g$1@news.eternal-september.org...
>>> 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
>>>
>>>
>>
>>
>