Error #Name?

Experts,
My form is based upon my table tblPreliminaryBonus
This table has various fields, but the ones I'm interested in is:
StoreID (FK)
DistrictManager (FK)

I have an unbound text box label txtDM used only for information and is not enabled. I am looking for a way to get the First Name of the District Manger that is associated with Store and display that name in the text box txtDM
Currently, when I select a store it displays the FK for that district manager, where I need a name instead.

I've attached a copy of the db with the form in question. Although there are 3 other employees shown on the form when I get the DM I'll do the rest. StoreBonusRevX.mdb
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Rey Obrero (Capricorn1)Commented:
test this

revised the Record Source of the form with a query joining tblEmployee and tblPreliminaryBonus
StoreBonusRevX1.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Try this expression in that text box:

=DLookUp("[EmployeeLastName]","[tblEmployee]","[EmployeeID] = " & [DistrictManager])

mx
0
mbizupCommented:
Try this:

=DLookUp("[EmployeeLastName] &  ',  ' & [EmployeeFirstName]","tblEmployee","EmployeeID= " & [DistrictManager])
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mbizupCommented:
This is a good article by Jim Dettman explaining DLookup and other domain functions for this and other purposes:
http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_12-Dlookup-and-the-Domain-Functions.html

If you find it helpful, click the 'yes' voting link at the top of the article.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"I am looking for a way to get the First Name of the District Manger that is associated with Store and display that name in the text box txtDM"

So then ..

=DLookUp("[EmployeeFirstName]","[tblEmployee]","[EmployeeID] = " & [DistrictManager])
0
Frank FreeseAuthor Commented:
both solutions work...
capricorn, how do I get the lastname aslo in that control box, please?
0
Frank FreeseAuthor Commented:
mx: how can I modify the dlookup to include the first name, please?
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
: how can I modify the dlookup to include the first name, please?

So, you want Both then?  Pretty much all combinations have been posted above ...

mx
0
Frank FreeseAuthor Commented:
got both now with your post and mbizup. just try to figure out how to modify cap's post.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
You know ... for this form, I would skip the DLookup() and make the Form record source this:

SELECT tblPreliminaryBonus.*, [EmployeeFirstName] & " " & [EmployeeLastName] AS EmpName FROM tblPreliminaryBonus LEFT JOIN tblEmployee ON tblPreliminaryBonus.DistrictManager=tblEmployee.EmployeeID;

Then make the Control Source of that text box EmpName ...

Since that text box is locked ... really no issue.

Done.
0
Frank FreeseAuthor Commented:
thanks to all - great solutions and much appreciated. thank mbizup for the link.
0
Rey Obrero (Capricorn1)Commented:
:-( ?
0
Frank FreeseAuthor Commented:
capricorn:
I hope I haven't offended you in any way. If so, I apoligize. You're so helpful to me.
0
Rey Obrero (Capricorn1)Commented:
fh_freese,
awarding points for repeated comments does not make sense.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Where are the 'repeated' comments ?
0
Frank FreeseAuthor Commented:
I'm confused also - the points were distributed equally to three players that's all. When there are multiple solutions I take that into consideration. If there is one solution then that solution gets all the points.  
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
works for me ...
0
Rey Obrero (Capricorn1)Commented:
<revised the Record Source of the form with a query joining tblEmployee and tblPreliminaryBonus>
http:#a37046440
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
Your query only brings down the FirstName ... mine brings down both ... if that is what you are referring to ... so, it's not a 'repeat' ...

mx
0
Rey Obrero (Capricorn1)Commented:

<I am looking for a way to get the First Name of the District Manger .... >
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database ArchitectCommented:
"how do I get the lastname aslo in that control box, please?"

!
0
Frank FreeseAuthor Commented:
capricorn: granted I did initially request only first name thinking I could follow what was done and complete the last name by myself. When I tried to modify your work I couldn't get it to work. I try not to be a burden and learn by doing from the Experts. I'd just like to go forward always grateful for everyone;s help ove the past several years - and I trult mean that. Have a great weekend.
0
mbizupCommented:
Hey guys -

You're both right.

The duplication in text is indeed present (and if MX had noticed it at the time, the post could have been improved with an intro like "as capricorn1 mentioned in the first post") , but the technical content of the two posts makes them 'supporting' solutions versus true duplicates - which ultimately got the desired results.

That said - in a seperate post, I demonstrated how you can really step on someones toes unintentionally around here.



just my $.02
0
Rey Obrero (Capricorn1)Commented:
then why change the idea on using dlookup into changing the record source of the form 36 minutes after the first suggestion of changing the record source.  
0
Frank FreeseAuthor Commented:
i guess i could have gone either way - no specific reason.
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.