Unable to resolve runtime error 2465

[Amit]

MS Access 2000, Win XP
===================
Hi,

This is probably quite simple, but I'm totally missing it, and need some
fresh eyes to look at this.

I have a form with a combo-box control that is used to select a name. The
names and their phone numbers are stored in a table called 'tblPerson'.

I also have unbound text control to display the phone number of the person
selected in the combo-box.

I'm setting this in the AfterUpdate event of the combo-box, using a DLookup.
Here's the code:
============================================
Private Sub cboHCTeamCoordinatorID_AfterUpdate()

Dim varHCCoordinatorPhone As Variant
varHCCoordinatorPhone = DLookup([PersonPhoneNumber], "tblPerson",
"[PersonID] = " & Me.cboHCTeamCoordinatorID.Value)
Me.txtHCCoordinatorPhone = varHCCoordinatorPhone

End Sub
===============================================
This gives me run-time error 2465, with the following : MS Access can't find
the field '|' referred to in your expression.

I've used different combinations in the code:
1. Instead of assigning the value to a variant, I've tried to assign it
directly to the unbound textbox.

2. In the criteria of Dlookup, I've used "Me.cboHCTeamCoordinatorID" and
"Me.HCTeamCoordinatorID" (that's the field in the table where value is stored.

I've checked and re-checked the table and field names, and they are correct
(or, I'm really being dense, which is a possibility .

Any help will be appreciated.

Thanks.

-Amit

 

[Amit]

Never mind. Figured it out.

DLookup("[PersonPhoneNumber]", ...)

Putting quotes around [PersonPhone Number] did it.

Thanks.

-Amit

 

[Ofer]

If the name and the phone number are on the same table why do you use
dlookup, instead add to the combo source another field: phone number and in
the code of the after update you can write
Me.txtHCCoordinatorPhone = me.cboHCTeamCoordinatorID.column(1) ' it start
with 0 that resurve for the name

Or in the ControlSource Property of the Phone text box you can write
= me.cboHCTeamCoordinatorID.column(1)

So you wont need to write any code, it will refresh it self with every
selection.

 

[Amit]

If the name and the phone number are on the same table why do you use
dlookup, instead add to the combo source another field: phone number and in
the code of the after update you can write
Me.txtHCCoordinatorPhone = me.cboHCTeamCoordinatorID.column(1) ' it start
with 0 that resurve for the name

Or in the ControlSource Property of the Phone text box you can write
= me.cboHCTeamCoordinatorID.column(1)

So you wont need to write any code, it will refresh it self with every
selection.

Ah, that's a better idea. Thanks!!!
I'll try this out right now.

-Amit