*DLookup

[JMorrell]

I've tried, really I have. But just can't get the DLookup funciton to work for me

tables: tblEmp, tblClassCodes
fields in tblClassCodes are: CC (text,6) and Title (text,30
form: frmNewEmp (fed from a query that pulls most fields from tblEmp), is a Data Entry form and has an unbound text box named; boxCC (source code is tblEmp.CC). I want to populate another unbound text box (boxTitle) with a corresponding value from tblClassCodes (tblClassCodes.CC to tblClassCode.Title is 1:1

my function code, in the control source for boxTitle is
=DLookUp("[Title]","[tblClassCodes]","[CC] = '" & Me!boxCC & "'"

when opening the frmNewEmp, field boxTitle = #Name?, and never fills with the corresponding Title from tblClassCode

I've checked spelling, data types, box names, etc. What am I missing here? I'm at my wits end

thanks in advance

 

[Peter Hoyle]

Try
=DLookUp("[Title]","[tblClassCodes]","[CC]='" & [boxCC] & "'")

I've tried, really I have. But just can't get the DLookup funciton to work for me.

tables: tblEmp, tblClassCodes.
fields in tblClassCodes are: CC (text,6) and Title (text,30)
form: frmNewEmp (fed from a query that pulls most fields from tblEmp), is

a Data Entry form and has an unbound text box named; boxCC (source code is
tblEmp.CC). I want to populate another unbound text box (boxTitle) with a
corresponding value from tblClassCodes (tblClassCodes.CC to
tblClassCode.Title is 1:1)

my function code, in the control source for boxTitle is:
=DLookUp("[Title]","[tblClassCodes]","[CC] = '" & Me!boxCC & "'")

when opening the frmNewEmp, field boxTitle = #Name?, and never fills with

the corresponding Title from tblClassCodes

 

[Victor Delgadillo]

Perhaps you are missing a reference (check in the Visual Basic editor /
Tools / References and make sure you have the corresponding dll's for the
funcions.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_

I've tried, really I have. But just can't get the DLookup funciton to work for me.

tables: tblEmp, tblClassCodes.
fields in tblClassCodes are: CC (text,6) and Title (text,30)
form: frmNewEmp (fed from a query that pulls most fields from tblEmp), is

a Data Entry form and has an unbound text box named; boxCC (source code is
tblEmp.CC). I want to populate another unbound text box (boxTitle) with a
corresponding value from tblClassCodes (tblClassCodes.CC to
tblClassCode.Title is 1:1)

my function code, in the control source for boxTitle is:
=DLookUp("[Title]","[tblClassCodes]","[CC] = '" & Me!boxCC & "'")

when opening the frmNewEmp, field boxTitle = #Name?, and never fills with

the corresponding Title from tblClassCodes

 

[John Vinson]

I've tried, really I have. But just can't get the DLookup funciton to work for me.

tables: tblEmp, tblClassCodes.
fields in tblClassCodes are: CC (text,6) and Title (text,30)
form: frmNewEmp (fed from a query that pulls most fields from tblEmp), is a Data Entry form and has an unbound text box named; boxCC (source code is tblEmp.CC). I want to populate another unbound text box (boxTitle) with a corresponding value from tblClassCodes (tblClassCodes.CC to tblClassCode.Title is 1:1)

my function code, in the control source for boxTitle is:
=DLookUp("[Title]","[tblClassCodes]","[CC] = '" & Me!boxCC & "'")

when opening the frmNewEmp, field boxTitle = #Name?, and never fills with the corresponding Title from tblClassCodes

I've checked spelling, data types, box names, etc. What am I missing here? I'm at my wits end.

thanks in advance.

Try deleting the Me! in the criterion. That is meaningful within a VBA
module, but not AFAIK in a control source. If BoxCC is another control
on the form just using

"[CC] = '" & [BoxCC] & "'"

should work.

You can avoid the DLookUp altogether though by including tblClassCodes
in the Query upon which your form is based, or by using a Combo Box
bound to CC but displaying Title.

 

[JMorrell]

there is no dll called DLookup. should I be looking for something different?


----- Victor Delgadillo wrote: -----

Perhaps you are missing a reference (check in the Visual Basic editor /
Tools / References and make sure you have the corresponding dll's for the
funcions.

--
Victor Delgadillo [MVP Access]
Miami, Florida

Consultas al grupo, asi todos nos beneficiamos.

_

I've tried, really I have. But just can't get the DLookup funciton to work for me.
fields in tblClassCodes are: CC (text,6) and Title (text,30)
form: frmNewEmp (fed from a query that pulls most fields from tblEmp), is

a Data Entry form and has an unbound text box named; boxCC (source code is
tblEmp.CC). I want to populate another unbound text box (boxTitle) with a
corresponding value from tblClassCodes (tblClassCodes.CC to
tblClassCode.Title is 1:1)

my function code, in the control source for boxTitle is:

=DLookUp("[Title]","[tblClassCodes]","[CC] = '" & Me!boxCC & "'")

when opening the frmNewEmp, field boxTitle = #Name?, and never fills with the corresponding Title from tblClassCodes
I've checked spelling, data types, box names, etc. What am I missing here? I'm at my wits end.
thanks in advance.

 

[JMorrell]

----- John Vinson wrote: ----

On Tue, 24 Feb 2004 10:26:07 -0800, "JMorrell

I've tried, really I have. But just can't get the DLookup funciton to work for me

tables: tblEmp, tblClassCodes

fields in tblClassCodes are: CC (text,6) and Title (text,30
form: frmNewEmp (fed from a query that pulls most fields from tblEmp), is a Data Entry form and has an unbound text box named; boxCC (source code is tblEmp.CC). I want to populate another unbound text box (boxTitle) with a corresponding value from tblClassCodes (tblClassCodes.CC to tblClassCode.Title is 1:1

my function code, in the control source for boxTitle is

=DLookUp("[Title]","[tblClassCodes]","[CC] = '" & Me!boxCC & "'"

when opening the frmNewEmp, field boxTitle = #Name?, and never fills with the corresponding Title from tblClassCode
I've checked spelling, data types, box names, etc. What am I missing here? I'm at my wits end
thanks in advance

Try deleting the Me! in the criterion. That is meaningful within a VB
module, but not AFAIK in a control source. If BoxCC is another contro
on the form just usin

"[CC] = '" & [BoxCC] & "'

should work.

You can avoid the DLookUp altogether though by including tblClassCode
in the Query upon which your form is based, or by using a Combo Bo
bound to CC but displaying Title

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi

Changed the control source to
=DLookUp("[Title]","[class code descriptions]","[CC] = '" & [BoxCC] & "'"
which produces "Error" in field on form

 

[JMorrell]

This worked! Thank you, thank you

The only thing now is that even though all the other form fields populate tblEmp, the Title field is empty. Is that because it's unbound? I'm not sure

----- Peter Hoyle wrote: ----

Tr
=DLookUp("[Title]","[tblClassCodes]","[CC]='" & [boxCC] & "'"

I've tried, really I have. But just can't get the DLookup funciton t work for me
fields in tblClassCodes are: CC (text,6) and Title (text,30
form: frmNewEmp (fed from a query that pulls most fields from tblEmp), i

a Data Entry form and has an unbound text box named; boxCC (source code i
tblEmp.CC). I want to populate another unbound text box (boxTitle) with
corresponding value from tblClassCodes (tblClassCodes.CC t
tblClassCode.Title is 1:1

my function code, in the control source for boxTitle is

=DLookUp("[Title]","[tblClassCodes]","[CC] = '" & Me!boxCC & "'"

when opening the frmNewEmp, field boxTitle = #Name?, and never fills wit the corresponding Title from tblClassCode
I've checked spelling, data types, box names, etc. What am I missin here? I'm at my wits end
thanks in advance

 

[John Vinson]

Changed the control source to:
=DLookUp("[Title]","[class code descriptions]","[CC] = '" & [BoxCC] & "'")
which produces "Error" in field on form.

There is in fact a table named [Class code descriptions], with a text
field Title and another text field CC? What are the values in BoxCC
(you may get an error if BoxCC is empty).

 

[JMorrell]

----- John Vinson wrote: ----

On Tue, 24 Feb 2004 11:41:05 -0800, "JMorrell

Changed the control source to
=DLookUp("[Title]","[class code descriptions]","[CC] = '" & [BoxCC] & "'"
which produces "Error" in field on form

There is in fact a table named [Class code descriptions], with a tex
field Title and another text field CC? What are the values in BoxC
(you may get an error if BoxCC is empty)


John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi

I got it working... for the most part. The control source for the box now reads, after a few modifications:
=DLookUp("[Title]","[tblClassCodes]","[CC]='" & [boxCC] & "'"
but it gives me the Design View warning that "this control has a reference to itself". It populates the field with a valid value from tblClassCodes, but doesn't populate the underlying table tblEmp, which is what I'm after all along

Am I trying to get Access to do something it isn't designed to do
Jeff

 

[John Vinson]

I got it working... for the most part. The control source for the box now reads, after a few modifications:
=DLookUp("[Title]","[tblClassCodes]","[CC]='" & [boxCC] & "'")
but it gives me the Design View warning that "this control has a reference to itself". It populates the field with a valid value from tblClassCodes, but doesn't populate the underlying table tblEmp, which is what I'm after all along.

Am I trying to get Access to do something it isn't designed to do?

Is *THIS TEXTBOX* named CC or BoxCC? If so, just rename it to txtCC or
something.

 

[JMorrell]

----- John Vinson wrote: ----

On Tue, 24 Feb 2004 13:01:09 -0800, "JMorrell

I got it working... for the most part. The control source for the box now reads, after a few modifications:
=DLookUp("[Title]","[tblClassCodes]","[CC]='" & [boxCC] & "'"
but it gives me the Design View warning that "this control has a reference to itself". It populates the field with a valid value from tblClassCodes, but doesn't populate the underlying table tblEmp, which is what I'm after all along

Am I trying to get Access to do something it isn't designed to do

Is *THIS TEXTBOX* named CC or BoxCC? If so, just rename it to txtCC o
something

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi

Thanks for all your help. I got the error to go away by renaming it. But I still can't get the retrieved value to populate the table (as the other fields do). I feel like I’m so close to what I’m after. It really shouldn’t be this difficult.

 

[John Vinson]

But I still can't get the retrieved value to populate the table (as the other fields do).

Two points:

- You didn't say that's what you wanted

- You should NOT be storing a looked-up value redundantly in your
table anyhow! Just look it up in a Query when you need it.

 

[JMorrell]

----- John Vinson wrote: ----

On Tue, 24 Feb 2004 14:21:08 -0800, "JMorrell

But I still can't get the retrieved value to populate the table (as the other fields do).

Two points

- You didn't say that's what you wante

- You should NOT be storing a looked-up value redundantly in you
table anyhow! Just look it up in a Query when you need it

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=publi

Points well taken. Thanks for all your help in getting my DLookup working.