Link to home
Start Free TrialLog in
Avatar of Frank Freese
Frank FreeseFlag for United States of America

asked on

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
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Try this expression in that text box:

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

mx
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
This is a good article by Jim Dettman explaining DLookup and other domain functions for this and other purposes:
https://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.
"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])
Avatar of Frank Freese

ASKER

both solutions work...
capricorn, how do I get the lastname aslo in that control box, please?
mx: how can I modify the dlookup to include the first name, please?
: 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
got both now with your post and mbizup. just try to figure out how to modify cap's post.
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thanks to all - great solutions and much appreciated. thank mbizup for the link.
capricorn:
I hope I haven't offended you in any way. If so, I apoligize. You're so helpful to me.
fh_freese,
awarding points for repeated comments does not make sense.
Where are the 'repeated' comments ?
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.  
<revised the Record Source of the form with a query joining tblEmployee and tblPreliminaryBonus>
http:#a37046440
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

<I am looking for a way to get the First Name of the District Manger .... >
"how do I get the lastname aslo in that control box, please?"

!
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.
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
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.  
i guess i could have gone either way - no specific reason.