DLookup Help Needed. New. Easy Question!

[kkrupsha]

Hello!

I am trying to build a fairly simple form for a project I am working on.
Have only intermediate Access knowledge. The form that I am building is
based off a table that contains a field "RepCommunicationType". This is a
lookup field based off a table "RepCommunication". There is another field
within that table called "CommunicationValue". I am trying to add the
lookup field to the form and once the user chooses a value from the list box
for the RepCommunicationType, the field for CommunicationValue will
automatically populate based on the value I have assigned to it in the
RepCommunication Table. Essentially, I am trying to assign value to fields
on the form so that I can rank the information based on the form. I cannot
get the DLookup function to populate on my form. The form name is Account
Requirements.
I am writing the DLookup like this:
DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=Forms![RepCommunicationType]")

I am not getting an error, but it is not auto filling the value that I
assign to it.

Any ideas on what I am doing wrong... or if this is the best way to go about
it?

I am trying to liken this function to vlookup in Excel where I give the
array to look for and return a value... but it is not working.

Thanks!

 

[Damian S]

Hi kkrupsha,

You have a quote (") in the wrong place, and are missing your form name...
Try this

dlookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]= "
& Forms!FORMNAME.[RepCommunicationType])

Hope this helps.

Damian.

 

[kkrupsha]

Damian,

Thanks for the quick help. I now get an error #Name? ... any thoughts?
--
kkrupsha

Hi kkrupsha,

You have a quote (") in the wrong place, and are missing your form name...
Try this

dlookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]= "
& Forms!FORMNAME.[RepCommunicationType])

Hope this helps.

Damian.

Hello!

I am trying to build a fairly simple form for a project I am working on.
Have only intermediate Access knowledge. The form that I am building is
based off a table that contains a field "RepCommunicationType". This is a
lookup field based off a table "RepCommunication". There is another field
within that table called "CommunicationValue". I am trying to add the
lookup field to the form and once the user chooses a value from the list box
for the RepCommunicationType, the field for CommunicationValue will
automatically populate based on the value I have assigned to it in the
RepCommunication Table. Essentially, I am trying to assign value to fields
on the form so that I can rank the information based on the form. I cannot
get the DLookup function to populate on my form. The form name is Account
Requirements.
I am writing the DLookup like this:
DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=Forms![RepCommunicationType]")

I am not getting an error, but it is not auto filling the value that I
assign to it.

Any ideas on what I am doing wrong... or if this is the best way to go about
it?

I am trying to liken this function to vlookup in Excel where I give the
array to look for and return a value... but it is not working.

Thanks!

 

[Damian S]

Probably due to having the control (eg: text box) with the same name as the
data field. It's always a good idea to have different names for your
controls, eg: the field is RepCommunicationType, you might have a text box
called txtRepCommunicationType, or a combo called cboRepCommunicationType.

You want your dlookup to reference the combo box/text field.

Damian.

Damian,

Thanks for the quick help. I now get an error #Name? ... any thoughts?
--
kkrupsha

Hi kkrupsha,

You have a quote (") in the wrong place, and are missing your form name...
Try this

dlookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]= "
& Forms!FORMNAME.[RepCommunicationType])

Hope this helps.

Damian.

Hello!

I am trying to build a fairly simple form for a project I am working on.
Have only intermediate Access knowledge. The form that I am building is
based off a table that contains a field "RepCommunicationType". This is a
lookup field based off a table "RepCommunication". There is another field
within that table called "CommunicationValue". I am trying to add the
lookup field to the form and once the user chooses a value from the list box
for the RepCommunicationType, the field for CommunicationValue will
automatically populate based on the value I have assigned to it in the
RepCommunication Table. Essentially, I am trying to assign value to fields
on the form so that I can rank the information based on the form. I cannot
get the DLookup function to populate on my form. The form name is Account
Requirements.
I am writing the DLookup like this:
DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=Forms![RepCommunicationType]")

I am not getting an error, but it is not auto filling the value that I
assign to it.

Any ideas on what I am doing wrong... or if this is the best way to go about
it?

I am trying to liken this function to vlookup in Excel where I give the
array to look for and return a value... but it is not working.

Thanks!

 

[kkrupsha]

I have tried changing the fields. I now get #error message and the
expression builder keeps changing the expression that I put in. Is this
supposed to happen.

Hmmm. I am sure this is supposed to be easy. :O)
--
kkrupsha

Probably due to having the control (eg: text box) with the same name as the
data field. It's always a good idea to have different names for your
controls, eg: the field is RepCommunicationType, you might have a text box
called txtRepCommunicationType, or a combo called cboRepCommunicationType.

You want your dlookup to reference the combo box/text field.

Damian.

Damian,

Thanks for the quick help. I now get an error #Name? ... any thoughts?
--
kkrupsha

Hi kkrupsha,

You have a quote (") in the wrong place, and are missing your form name...
Try this

dlookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]= "
& Forms!FORMNAME.[RepCommunicationType])

Hope this helps.

Damian.


:

Hello!

I am trying to build a fairly simple form for a project I am working on.
Have only intermediate Access knowledge. The form that I am building is
based off a table that contains a field "RepCommunicationType". This is a
lookup field based off a table "RepCommunication". There is another field
within that table called "CommunicationValue". I am trying to add the
lookup field to the form and once the user chooses a value from the list box
for the RepCommunicationType, the field for CommunicationValue will
automatically populate based on the value I have assigned to it in the
RepCommunication Table. Essentially, I am trying to assign value to fields
on the form so that I can rank the information based on the form. I cannot
get the DLookup function to populate on my form. The form name is Account
Requirements.
I am writing the DLookup like this:
DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=Forms![RepCommunicationType]")

I am not getting an error, but it is not auto filling the value that I
assign to it.

Any ideas on what I am doing wrong... or if this is the best way to go about
it?

I am trying to liken this function to vlookup in Excel where I give the
array to look for and return a value... but it is not working.

Thanks!

 

[Tom Lake]

I am writing the DLookup like this:

DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=Forms![RepCommunicationType]")

I am not getting an error, but it is not auto filling the value that I
assign to it.

Try this:

DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=
'" & Forms![RepCommunicationType] & "'")

Tom Lake

 

[kkrupsha]

Tom,

Thanks for the reply. I have tried that as well. Unfortunately, I am still
getting an error and it seems as though the expression builder keeps changing
the format of the expression to:

=DLookUp("[CommunicationValue]","RepCommunication","[RepCommunicationType]=
'" & Forms!RepCommunicationType & "'")

It is removing brackets. Any ideas or thoughts?

--
kkrupsha

I am writing the DLookup like this:
DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=Forms![RepCommunicationType]")

I am not getting an error, but it is not auto filling the value that I
assign to it.

Try this:

DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=
'" & Forms![RepCommunicationType] & "'")

Tom Lake

 

[Duane Hookom]

Brackets are not necessary if you don't have spaces in your object names.
"Forms!RepCommunicationType " is wrong. If RepCommunicationType is in the
current form, remove the "Forms!".
=DLookUp("[CommunicationValue]","RepCommunication","[RepCommunicationType]=
'" & RepCommunicationType & "'")

I wonder why you would not use a combo box to select RepCommunicationType
from the RepCommunication table. You could then include the
CommunicationValue in the Row Source like:

SELECT RepCommunicationType, CommunicationValue FROM RecpCommunication ORDER
BY RepCommunicationType;

Name the combo box cboRepComType. Then replace the DLookup() with
=cboRepComType.Column(1)

--
Duane Hookom
MS Access MVP

Tom,

Thanks for the reply. I have tried that as well. Unfortunately, I am
still
getting an error and it seems as though the expression builder keeps
changing
the format of the expression to:

=DLookUp("[CommunicationValue]","RepCommunication","[RepCommunicationType]=
'" & Forms!RepCommunicationType & "'")

It is removing brackets. Any ideas or thoughts?

--
kkrupsha

I am writing the DLookup like this:
DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=Forms![RepCommunicationType]")

I am not getting an error, but it is not auto filling the value that I
assign to it.

Try this:

DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=
'" & Forms![RepCommunicationType] & "'")

Tom Lake

 

[kkrupsha]

Duane,

After spending all evening (literally)... you were able to assist in
seconds. The first solution works best for what I am trying to do! Thank you
for your assistance!
Case Closed!
--
kkrupsha

Brackets are not necessary if you don't have spaces in your object names.
"Forms!RepCommunicationType " is wrong. If RepCommunicationType is in the
current form, remove the "Forms!".
=DLookUp("[CommunicationValue]","RepCommunication","[RepCommunicationType]=
'" & RepCommunicationType & "'")

I wonder why you would not use a combo box to select RepCommunicationType
from the RepCommunication table. You could then include the
CommunicationValue in the Row Source like:

SELECT RepCommunicationType, CommunicationValue FROM RecpCommunication ORDER
BY RepCommunicationType;

Name the combo box cboRepComType. Then replace the DLookup() with
=cboRepComType.Column(1)

--
Duane Hookom
MS Access MVP

Tom,

Thanks for the reply. I have tried that as well. Unfortunately, I am
still
getting an error and it seems as though the expression builder keeps
changing
the format of the expression to:

=DLookUp("[CommunicationValue]","RepCommunication","[RepCommunicationType]=
'" & Forms!RepCommunicationType & "'")

It is removing brackets. Any ideas or thoughts?

--
kkrupsha

I am writing the DLookup like this:
DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=Forms![RepCommunicationType]")

I am not getting an error, but it is not auto filling the value that I
assign to it.

Try this:

DLookup("[CommunicationValue]","RepCommunication","[RepCommunicationType]=
'" & Forms![RepCommunicationType] & "'")

Tom Lake