DLookup

[Duane]

I am trying to obtain the value of a field that is not in the current record
on my form. I was hoping to use the DLookup function to get the value I
need, but it is not working correctly.

I have a form called PrisonerData with the following controls:
text field - PNumber
text field - Race

I have a link to another table in a different db that is called Prisoners.
The keyed filed in the table is the Number field, which has the same values
as the control PNumber that is on my form.

The control source in the Race field on the form:
=DLookUp("[Race]","Prisoners","[PNumber] =" &
[Forms]![PrisonerData]![PNumber])

I am only receiving the value in the RACE field of the prisoners table which
is in the first record. In this case it is WH. In this scenario, there are
WH & NW.

I am hoping someone can give me a hand. Maybe I can't go about it this way,
and if so, can someone give me an idea for another method?

Thanks

 

[John Vinson]

I have a link to another table in a different db that is called Prisoners.
The keyed filed in the table is the Number field, which has the same values
as the control PNumber that is on my form.

If PNumber is in fact a Text field, you need quotes around the
criterion. Not sure why you're not getting an error message though!
Try

=DLookUp("[Race]", "[Prisoners]", "[PNumber] ='" &
[Forms]![PrisonerData]![PNumber]) & "'")

So if PNumber is A1234, the third argument will be

[PNumber] = 'A1234'

and will be parsed correctly.

John W. Vinson[MVP]

 

[Duane]

Thanks John. I tried what you suggested but that didn't work either. At
first I received an error for too man closing parentheses. I tinkered with
that, but Access would always remove the brackets []. =DLookup("[Race]",
"[Prisoners]", "[PNumber] ="' & (Forms!PrisonerData!PNumber} & "'").

It is not that I am receivng any errors, but I am only receiving the value
in the race field of the first record in the table. In this instance, the
first record is WH. The are over a thousands records, or which half are NW,
but the value in the Race field on my form for all records is WH.

Within my database I have a table called PrisonerData. There is also a form
called PrisonerData. I know, at this time my naming convention is horrible.
What is worse, the field in the table is PNumber, which is the same as the
text field on the form. It is named PNumber and it is bound to the PNumber
field in the table. There are several fields on the form but the two in
particular are the PNumber and Race text fields.

I have a linked table called Prisoners which is in another database on our
network. The Prioners table has a field that contains prisoners numbers.
It is called Number. It is the primary key field in the table. The value
in this field is the same value as the value in the PNumber field of the
PrisonerData table. There is also a field in the Prisoner table called
Race. This is what I am looking to populate the Race text box on the
PrisonerData form with.

Any help would be great.

I have a link to another table in a different db that is called Prisoners.
The keyed filed in the table is the Number field, which has the same
values
as the control PNumber that is on my form.

If PNumber is in fact a Text field, you need quotes around the
criterion. Not sure why you're not getting an error message though!
Try

=DLookUp("[Race]", "[Prisoners]", "[PNumber] ='" &
[Forms]![PrisonerData]![PNumber]) & "'")

So if PNumber is A1234, the third argument will be

[PNumber] = 'A1234'

and will be parsed correctly.

John W. Vinson[MVP]

 

[John Vinson]

Thanks John. I tried what you suggested but that didn't work either. At
first I received an error for too man closing parentheses. I tinkered with
that, but Access would always remove the brackets []. =DLookup("[Race]",
"[Prisoners]", "[PNumber] ="' & (Forms!PrisonerData!PNumber} & "'").

That's not what I posted. You're missing some brackets and you've got
extra parentheses.

=DLookup("[Race]",
"[Prisoners]", "[PNumber] ='" & [Forms]![PrisonerData]![PNumber] &
"'")

as the Control Source for a form textbox should work. Note the order
of the quotes - doublequote, [PNumber] =, singlequote, doublequote.

John W. Vinson[MVP]

 

[Duane]

Thanks John and sorry for the confusion..

On your first response to me you provided an expression of

=DLookUp("[Race]", "[Prisoners]", "[PNumber] ='" &
[Forms]![PrisonerData]![PNumber]) & "'")

There is an extra closing parentheses after [PNumber]). Access was giving
me an error so I initially added a preceding ( to enclose the
[Forms]![PrisonerData]![PNumber] in order to satisfy the error message. It
did not help or change the situation. I was still only getting the value of
the [Race] field in the first record. Then I removed the parentheses, but
that didn't make any difference. After I typed in the expression you
provided into the control source for the Race text box, discovered nothing
was different, I went back to the control source and discovered that Access
had removed the [] from the expression. In now looks like
=DLookup("[Race]", "[Prisoners]", "[PNumber] ='" &
Forms!PrisonerData!PNumber & "'")

For some reason I cannot get past the value of the first record.

Once again, sorry for the confusion. Thanks in advance.

Thanks John. I tried what you suggested but that didn't work either. At
first I received an error for too man closing parentheses. I tinkered
with
that, but Access would always remove the brackets []. =DLookup("[Race]",
"[Prisoners]", "[PNumber] ="' & (Forms!PrisonerData!PNumber} & "'").

That's not what I posted. You're missing some brackets and you've got
extra parentheses.

=DLookup("[Race]",
"[Prisoners]", "[PNumber] ='" & [Forms]![PrisonerData]![PNumber] &
"'")

as the Control Source for a form textbox should work. Note the order
of the quotes - doublequote, [PNumber] =, singlequote, doublequote.

John W. Vinson[MVP]

 

[John Vinson]

Thanks John and sorry for the confusion..

On your first response to me you provided an expression of

=DLookUp("[Race]", "[Prisoners]", "[PNumber] ='" &
[Forms]![PrisonerData]![PNumber]) & "'")

There is an extra closing parentheses after [PNumber]).

My apologies. Typo on my part.

Access was giving
me an error so I initially added a preceding ( to enclose the
[Forms]![PrisonerData]![PNumber] in order to satisfy the error message. It
did not help or change the situation. I was still only getting the value of
the [Race] field in the first record. Then I removed the parentheses, but
that didn't make any difference. After I typed in the expression you
provided into the control source for the Race text box, discovered nothing
was different, I went back to the control source and discovered that Access
had removed the [] from the expression. In now looks like
=DLookup("[Race]", "[Prisoners]", "[PNumber] ='" &
Forms!PrisonerData!PNumber & "'")

For some reason I cannot get past the value of the first record.

What is actually in the control named PNumber on the form
PrisonerData? I do not understand why this would return an arbitrary
(first?) record from the Prisoners table.

Just to verify:

- You have a Form named [PrisonerData] open
- It contains a textbox or other control named [PNumber]
- That control contains a value which matches the Primary Key field in
the linked table Prisoners
- The Form is currently displaying the data about the prisoner you're
seeking
- the Prisoners table contains a field Race


As an alternative, do you have a Combo Box to select the PNUmber? What
is that combo's RowSource? If it's based on the table Prisoners, you
could simply include Race in the RowSource query (it can be a zero
width column in the combo); and you can put a textbox on the form with
a control source

=cboPNumber.Column(n)

where cboPNumber is the name of the combo box and n is the zero-based
index of the column containing the prisoner's race - e.g. if the
combo's rowsource were

SELECT PNumber, LastName & ", " & FirstName AS PrisonerName, Race FROM
Prisoners ORDER BY LastName, FirstName;

you'ld use (2).

John W. Vinson[MVP]