Text Box Control Source

[Em]

I have an new Employee entry form. I have a combo box to
choose the department. I would like a text box to display
the manager of that department once the user selects the
department, however this control is unbound, it's just for
information.

Code for combo box:

SELECT DISTINCTROW [tbl_Department].[DEPT_ID],
[tbl_Department].[DEPT_NAME] FROM [tbl_Department];

The combo box control source is emp_dept.

I have the control source of the text box (txtSupervisor)
set to:

=(SELECT [tbl_employee.emp_lastname] & ", " &
[tbl_employee.emp_firstname] AS Name FROM tbl_Department
INNER JOIN tbl_Employee ON tbl_Department.DEPT_MANAGER =
tbl_Employee.EMP_ID WHERE (((tbl_Department.DEPT_ID)=
[forms]![frm_newemployee]![cbodepartment]))

This query runs fine if I just run it in the query
designer.

When I run the form I get a #NAME? error.

Any ideas?

 

[Steve Schapel]

Em,

Basically, you can't use a query as the Control Source of a textbox...
which makes sense when you think about it, I mean Access has no way of
knowing which field or record you want.

There are a couple of ways you could do this:
1. Change the query for the emp_dept combobox so it is 3 columns, with
the 3rd column being the Manager, and then in the Control Source of your
unbound textbox, the equivalent of...
=[emp_dept].[Column](2)
2. Use an appropriate DLookup() function in the Control Source of your
unbound textbox to return the Manager

 

[Em]

Thanks, Steve. The 1st option looks like it will work the best for my purposes. Thanks for your help!

 

[prabha]

Hi,

That's not how to get the result. But it all depends how your data is
stored in your database.

If you CAN modify the Combo box Row Source to something like

SELECT DISTINCTROW [tbl_Department].[DEPT_ID], [xxxx].[DEPT_NAME],
[tbl_Department].[Manager]
FROM [tbl_Department] INNER JOIN ...........

For the text box control make it's control source
= [name of above combo box].Column(1)

See: ACC2000: How to Use the Column Property of a Combo Box to Update a
Text Box
http://support.microsoft.com/default.aspx?scid=kb;en-us;209738

Not sure how your Form's are layed out nor how your data is structured but
here's a long way that I'm sure you can find shortcuts to:

Textbox1: get's the manager ID
=DLookUp("[Dept_Manager]","[tbl_Department]","[Dept_ID] = ' " &
[forms]![frm_newemployee]![cbodepartment] & " ' ")

Textbox2: get's the manager name
=DLookUp("[emp_lastname]","[tbl_Employee]","[Emp_ID] =
[Textbox1]")

I hope this helps! If you have additional questions on this topic, please
respond back to this posting.


Regards,

Eric Butts
Microsoft Access Support

"Microsoft Security Announcement: Have you installed the patch for
Microsoft Security Bulletin MS03-026? If not Microsoft strongly advises
you to review the information at the following link regarding Microsoft
Security Bulletin MS03-026
<http://www.microsoft.com/security/security_bulletins/ms03-026.asp> and/or
to visit Windows Update at <http://windowsupdate.microsoft.com/> to install
the patch. Running the SCAN program from the Windows Update site will help
to insure you are current with all security patches, not just MS03-026."


--------------------
| Content-Class: urn:content-classes:message
| From: "Em" <[email protected]>
| Sender: "Em" <[email protected]>
| Subject: Text Box Control Source
| Date: Fri, 20 Feb 2004 17:46:24 -0800
| Lines: 28
| Message-ID: <[email protected]>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcP4HINs3YVbOf3kRMWcmrMxtxqq2w==
| Newsgroups: microsoft.public.access.forms
| Path: cpmsftngxa07.phx.gbl
| Xref: cpmsftngxa07.phx.gbl microsoft.public.access.forms:256077
| NNTP-Posting-Host: tk2msftngxa11.phx.gbl 10.40.1.163
| X-Tomcat-NG: microsoft.public.access.forms
|
| I have an new Employee entry form. I have a combo box to
| choose the department. I would like a text box to display
| the manager of that department once the user selects the
| department, however this control is unbound, it's just for
| information.
|
| Code for combo box:
|
| SELECT DISTINCTROW [tbl_Department].[DEPT_ID],
| [tbl_Department].[DEPT_NAME] FROM [tbl_Department];
|
| The combo box control source is emp_dept.
|
| I have the control source of the text box (txtSupervisor)
| set to:
|
| =(SELECT [tbl_employee.emp_lastname] & ", " &
| [tbl_employee.emp_firstname] AS Name FROM tbl_Department
| INNER JOIN tbl_Employee ON tbl_Department.DEPT_MANAGER =
| tbl_Employee.EMP_ID WHERE (((tbl_Department.DEPT_ID)=
| [forms]![frm_newemployee]![cbodepartment]))
|
| This query runs fine if I just run it in the query
| designer.
|
| When I run the form I get a #NAME? error.
|
| Any ideas?
|