DLookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a form in which I am scheduling speakers it called “Speaker Assignmentsâ€

I am trying to use DLookup to retrieve information from a Table called
“SpeakerInfo†I have created a unbound text box called “txtSelectSpeakerâ€.
What I want to do is enter the last name of the speaker in the unbound text
box and using after update when I tab out I want the information to be placed
in other field..

In the “SpeakerInfo†Table I have the following fields: “SpeakerName†(just
Last Name) “CityName†, “PhoneNumber†and finally “FullNameâ€. What I want
to accomplish is to be able to type in the last name of speaker and tabbing
out place the “FullName†in the field “Speaker†the “CityName†in the field
“City†and finally the “PhoneNumber†in a the field “ContactNumber†the first
line of code that was to place the FullName into the “Speaker†field is:
Me.Speaker = Nz(DLookup("[SpeakerName]", "[SpeakerInfo]", "[FullName]= " &
Me.txtSelectSpeaker), "")

This of course didn’t work. Can anyone help.
 
sandrao said:
I have a form in which I am scheduling speakers it called "Speaker
Assignments"

I am trying to use DLookup to retrieve information from a Table called
"SpeakerInfo" I have created a unbound text box called "txtSelectSpeaker".
What I want to do is enter the last name of the speaker in the unbound
text
box and using after update when I tab out I want the information to be
placed
in other field..

In the "SpeakerInfo" Table I have the following fields: "SpeakerName"
(just
Last Name) "CityName" , "PhoneNumber" and finally "FullName". What I want
to accomplish is to be able to type in the last name of speaker and
tabbing
out place the "FullName" in the field "Speaker" the "CityName" in the
field
"City" and finally the "PhoneNumber" in a the field "ContactNumber" the
first
line of code that was to place the FullName into the "Speaker" field is:
Me.Speaker = Nz(DLookup("[SpeakerName]", "[SpeakerInfo]", "[FullName]= " &
Me.txtSelectSpeaker), "")

This of course didn't work. Can anyone help.
Click to expand...

Since txtSelectSpeaker is probably text, you need to put quotes around it:

Me.Speaker = Nz(DLookup("[SpeakerName]", "[SpeakerInfo]", "[FullName]=
""" & Me.txtSelectSpeaker) & """")

That produces something like this:

[FullName]="Smith"

Carl Rapson
 
When using Dlookup on a string value you need to add a single quote before
and after the string

Me.Speaker = Nz(DLookup("[SpeakerName]", "[SpeakerInfo]", "[FullName]= '" &
Me.txtSelectSpeaker & "'"), "")

Also, It's better using a combo where all the fields included in the RowSource

Select FullName, SpeakerName, CityName , PhoneNumber From SpeakerInfo

Change the ColumnCount to 4
And change the ColumnWidth to 3;0;0;0
that way only the FullName will be displayed

Then you can refer to each value using
Me.Speaker = Me.[ComboName].Column(1)
Or write directly in the Speaker text box ControlSource
=[ComboName].Column(1)

The column count start with 0.

You can do the same the rest of the values, that way you don't access the
table for each value so the process will be faster, and the user wont be able
to make mistakes selecting the values from existing table
 
Back
Top