If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
lookup field
I have an emplyeees table with a "reports to" field which
contains the ID number of the employees supervisor. I have a combo box on my form which allows you to select a supervisor, but that box only shows the ID number of the supervisor, unless you depress the spin button. Then it opens showing the name of the current supervisor and the supervisors ID number, plus all the names of supervisors which might be chosen. I would like a text box just to show the name of the currently chosen supervisor. |
#2
|
|||
|
|||
lookup field
The easiest thing to do is to delete the combo and rebuild
it with the wizard turned on. At some point the wizard will ask if you want to hide the key field, check this box. Your form will then just show the supervisor's name. If you want to show several fields from this table, you should change the RecordSource of the form to be a query that joins the main table to the supervisor table. You can then select what ever columns you need from both tables. Then you can add additional controls to the form to hold the supervisor information. It is best to set the locked properties of these fields to yes to prevent accidental updating. Now when you choose a supervisor id from the combo, the name and other fields will AUTOMATICALLY be populated. No code is required. -----Original Message----- I have an emplyeees table with a "reports to" field which contains the ID number of the employees supervisor. I have a combo box on my form which allows you to select a supervisor, but that box only shows the ID number of the supervisor, unless you depress the spin button. Then it opens showing the name of the current supervisor and the supervisors ID number, plus all the names of supervisors which might be chosen. I would like a text box just to show the name of the currently chosen supervisor. . |
#3
|
|||
|
|||
lookup field
Nah-uh, won't work.
The key field is the employees IDNO The "ReportsTo" field is a foreign key in the same table. this field needs to use the foreign key, shown on the form as the "Supervisors ID" to look up the same value in the IDNO column and report back the supervisors name. I have not been able to get the Wizard to do this. I'm thinking of a text box where the control source is a SQL statement that says something like Select Employees! Name From "Employees" Where IDNO = CurrentRecord! ReportsToID, but I don't know how to do it. -----Original Message----- The easiest thing to do is to delete the combo and rebuild it with the wizard turned on. At some point the wizard will ask if you want to hide the key field, check this box. Your form will then just show the supervisor's name. If you want to show several fields from this table, you should change the RecordSource of the form to be a query that joins the main table to the supervisor table. You can then select what ever columns you need from both tables. Then you can add additional controls to the form to hold the supervisor information. It is best to set the locked properties of these fields to yes to prevent accidental updating. Now when you choose a supervisor id from the combo, the name and other fields will AUTOMATICALLY be populated. No code is required. -----Original Message----- I have an emplyeees table with a "reports to" field which contains the ID number of the employees supervisor. I have a combo box on my form which allows you to select a supervisor, but that box only shows the ID number of the supervisor, unless you depress the spin button. Then it opens showing the name of the current supervisor and the supervisors ID number, plus all the names of supervisors which might be chosen. I would like a text box just to show the name of the currently chosen supervisor. . . |
#4
|
|||
|
|||
lookup field
I tried to add a ReportsToName field in the employees
table similar to the one used in the NorthWind Database but couldn't make it work. I f I use the lookup wizard the employees tabe is not one of the options. If I create a query, then the wizard disallows it because the query is based on the employees table. I copied the SQL code from the NW database and copied it into the field descriptions, but could not make that work either. -----Original Message----- The easiest thing to do is to delete the combo and rebuild it with the wizard turned on. At some point the wizard will ask if you want to hide the key field, check this box. Your form will then just show the supervisor's name. If you want to show several fields from this table, you should change the RecordSource of the form to be a query that joins the main table to the supervisor table. You can then select what ever columns you need from both tables. Then you can add additional controls to the form to hold the supervisor information. It is best to set the locked properties of these fields to yes to prevent accidental updating. Now when you choose a supervisor id from the combo, the name and other fields will AUTOMATICALLY be populated. No code is required. -----Original Message----- I have an emplyeees table with a "reports to" field which contains the ID number of the employees supervisor. I have a combo box on my form which allows you to select a supervisor, but that box only shows the ID number of the supervisor, unless you depress the spin button. Then it opens showing the name of the current supervisor and the supervisors ID number, plus all the names of supervisors which might be chosen. I would like a text box just to show the name of the currently chosen supervisor. . . |
#5
|
|||
|
|||
lookup field
On Tue, 6 Jul 2004 10:57:40 -0700, wrote:
I would like a text box just to show the name of the currently chosen supervisor. Two ways: - use the Combo Box, but change its Column Widths property to set the width of the ID to 0. Set the combo's properties to Enabled = False, Locked = True so the user can't drop it down. - Or, use a Textbox with a control source =DLookUp("[LastName] & ', ' & [FirstName]", "[Employees]", "[EmployeeID] = " & SupervisorID) using, of course, your own field and table names. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#6
|
|||
|
|||
lookup field
I can't seem to make the combo box work. How is the data
displayed in the combo box synchronized with the other data showing on the form? The main portion of the form is based on the employees table, but the combo box can't be based on the employees table: it has to be based on a query that links the emplyees table back to a copy of the employees table so the ReportsToID and the employees IDNO are linked. When I do that the form apparently has no idea that the ReportsToID of interest is the same one displayed in another field on the form. It appears that the combo box is OK for selecting data, but not displaying it on a lookup basis. -----Original Message----- On Tue, 6 Jul 2004 10:57:40 -0700, wrote: I would like a text box just to show the name of the currently chosen supervisor. Two ways: - use the Combo Box, but change its Column Widths property to set the width of the ID to 0. Set the combo's properties to Enabled = False, Locked = True so the user can't drop it down. - Or, use a Textbox with a control source =DLookUp("[LastName] & ', ' & [FirstName]", "[Employees]", "[EmployeeID] = " & SupervisorID) using, of course, your own field and table names. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public . |
#7
|
|||
|
|||
lookup field
The Dlookup worked, but I can only get the Combo box to
display the ReportsToID. Then when you press the spin button all of the available supervisors appear. I'm I correct in saying its OK for selection but not presentation? -----Original Message----- On Tue, 6 Jul 2004 10:57:40 -0700, wrote: I would like a text box just to show the name of the currently chosen supervisor. Two ways: - use the Combo Box, but change its Column Widths property to set the width of the ID to 0. Set the combo's properties to Enabled = False, Locked = True so the user can't drop it down. - Or, use a Textbox with a control source =DLookUp("[LastName] & ', ' & [FirstName]", "[Employees]", "[EmployeeID] = " & SupervisorID) using, of course, your own field and table names. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public . |
#8
|
|||
|
|||
lookup field
On Wed, 7 Jul 2004 06:52:40 -0700, "Miaplacidus"
wrote: The Dlookup worked, but I can only get the Combo box to display the ReportsToID. Then when you press the spin button all of the available supervisors appear. I'm I correct in saying its OK for selection but not presentation? No, you are not correct; and no, it's not the case that a combo box can only display the ID! A Combo Box is normally based on a Query - the "Row Source" property. This query can have one to ten fields; you would set the Combo's Column Count property to the number of fields that you want to include in the combo. In this case your query might contain the ID and the supervisor's name, and you'ld set Column Count to 2. The combo's Control Source is the field into which the selected value will be stored - ReportsToID in this case. The combo's Bound Column is the value which will be stored into the Control Source - if the ID is the first column, you'ld set this to 1. Finally, the ColumnWidths property controls how wide each of the columns is in the display. If you set the width of the ID field to 0, by setting ColumnWidths to something like 0;1.25 then it will *store* the ID, but *display* the name from the second column; what the user sees when the combo is not dropped down is the contents of the first non-zero width column. John W. Vinson[MVP] Come for live chats every Tuesday and Thursday http://go.compuserve.com/msdevapps?loc=us&access=public |
#9
|
|||
|
|||
lookup field
Add a text box with the control source equal to:
(If you have first and last name fields) =DLookUp('[EMP_LNAME] & ", " & [EMP_FNAME]',"[tblEmployee]","[EMP_ID_NO]=[Forms]![your_form_name]![cboEMP_I D_NO]") (or if you do not have a first name field) =DLookUp("[EMP_LNAME]","[tblEmployee]","[EMP_ID_NO]=[Forms]![your_form_name] ![cboEMP_ID_NO]") substitute the last name field name for EMP_LNAME the first name field name for EMP_FNAME the employee table name for tblEmployee the employee ID field name for EMP_ID_NO your form name for your_form_name the name of your combo box for cboEMP_ID_NO JohnC "miaplacidus" wrote in message ... Nah-uh, won't work. The key field is the employees IDNO The "ReportsTo" field is a foreign key in the same table. this field needs to use the foreign key, shown on the form as the "Supervisors ID" to look up the same value in the IDNO column and report back the supervisors name. I have not been able to get the Wizard to do this. I'm thinking of a text box where the control source is a SQL statement that says something like Select Employees! Name From "Employees" Where IDNO = CurrentRecord! ReportsToID, but I don't know how to do it. -----Original Message----- The easiest thing to do is to delete the combo and rebuild it with the wizard turned on. At some point the wizard will ask if you want to hide the key field, check this box. Your form will then just show the supervisor's name. If you want to show several fields from this table, you should change the RecordSource of the form to be a query that joins the main table to the supervisor table. You can then select what ever columns you need from both tables. Then you can add additional controls to the form to hold the supervisor information. It is best to set the locked properties of these fields to yes to prevent accidental updating. Now when you choose a supervisor id from the combo, the name and other fields will AUTOMATICALLY be populated. No code is required. -----Original Message----- I have an emplyeees table with a "reports to" field which contains the ID number of the employees supervisor. I have a combo box on my form which allows you to select a supervisor, but that box only shows the ID number of the supervisor, unless you depress the spin button. Then it opens showing the name of the current supervisor and the supervisors ID number, plus all the names of supervisors which might be chosen. I would like a text box just to show the name of the currently chosen supervisor. . . |
Thread Tools | |
Display Modes | |
|
|
Similar Threads | ||||
Thread | Thread Starter | Forum | Replies | Last Post |
NUMBERING the pages | Bob | New Users | 7 | June 14th, 2004 12:20 AM |
lookup field | angie | Running & Setting Up Queries | 1 | June 10th, 2004 12:57 PM |
lookup field | rvmom | New Users | 2 | May 23rd, 2004 03:06 AM |
Make a field lookup dependent on the value in another field of a record? | Susan A | Database Design | 8 | May 22nd, 2004 09:10 PM |
Lookup fields - what's wrong with them? | NATHAN SANDERS | New Users | 2 | May 4th, 2004 10:51 AM |