[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Error #Name?

Posted on 2011-10-28
25
Medium Priority
?
210 Views
Last Modified: 2012-05-12
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
0
Comment
Question by:Frank Freese
  • 8
  • 8
  • 6
  • +1
25 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 668 total points
ID: 37046440
test this

revised the Record Source of the form with a query joining tblEmployee and tblPreliminaryBonus
StoreBonusRevX1.mdb
0
 
LVL 75
ID: 37046454
Try this expression in that text box:

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

mx
0
 
LVL 61

Assisted Solution

by:mbizup
mbizup earned 664 total points
ID: 37046504
Try this:

=DLookUp("[EmployeeLastName] &  ',  ' & [EmployeeFirstName]","tblEmployee","EmployeeID= " & [DistrictManager])
0
Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

 
LVL 61

Expert Comment

by:mbizup
ID: 37046538
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
 
LVL 75
ID: 37046556
"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
 

Author Comment

by:Frank Freese
ID: 37046601
both solutions work...
capricorn, how do I get the lastname aslo in that control box, please?
0
 

Author Comment

by:Frank Freese
ID: 37046636
mx: how can I modify the dlookup to include the first name, please?
0
 
LVL 75
ID: 37046669
: 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
 

Author Comment

by:Frank Freese
ID: 37046707
got both now with your post and mbizup. just try to figure out how to modify cap's post.
0
 
LVL 75

Assisted Solution

by:DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform)
DatabaseMX (Joe Anderson - Microsoft MVP, Access and Data Platform) earned 668 total points
ID: 37046716
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
 

Author Closing Comment

by:Frank Freese
ID: 37046767
thanks to all - great solutions and much appreciated. thank mbizup for the link.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37046790
:-( ?
0
 

Author Comment

by:Frank Freese
ID: 37046945
capricorn:
I hope I haven't offended you in any way. If so, I apoligize. You're so helpful to me.
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37046977
fh_freese,
awarding points for repeated comments does not make sense.
0
 
LVL 75
ID: 37047044
Where are the 'repeated' comments ?
0
 

Author Comment

by:Frank Freese
ID: 37047266
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
 
LVL 75
ID: 37047292
works for me ...
0
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37047355
<revised the Record Source of the form with a query joining tblEmployee and tblPreliminaryBonus>
http:#a37046440
0
 
LVL 75
ID: 37047456
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37047469

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

!
0
 

Author Comment

by:Frank Freese
ID: 37047505
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
 
LVL 61

Expert Comment

by:mbizup
ID: 37047515
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
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 37047556
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
 

Author Comment

by:Frank Freese
ID: 37047583
i guess i could have gone either way - no specific reason.
0

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…
Suggested Courses

865 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question