Fred,
Did you mean unbound combo box, or unbound textbox?
I cannot agree too strongly with Fred about using an EmployeeID. In
addition to the issue he has raised regarding multiple employees with the
same name, you must also account for the possibility that the Loan Officer
could get married, divorced, or just change their name. If you are using the
name as foreign key value in any of your tables, this could be disasterous
(unless you have your db setup for cascading updates).
My preferred solution to your situation would be to use a combo box to
display the Loan Officers name. It would probably be bound to your
tbl_Loans.LoanOfficer field if you are processing a loan application.
The row source for the combo would be something like:
SELECT EmployeeID, EmpName, UnsecuredLimit, SecuredLimit, ODLimit
FROM yourTable
ORDER BY EmpName
By including all of these fields in the combo boxes row source, you can then
populate three unbound, locked textboxes in the afterupdate event of the
LoanOfficer combo box.
Private Sub cbo_LoanOfficer_AfterUpdate
me.txt_UnsecuredLimit = me.cbo_LoanOfficer.column(2)
me.txt_SecuredLimit = me.cbo_LoanOfficer.column(3)
me.txt_ODLimit = me.cbo_LoanOfficer.column(4)
End sub
HTH
Dale
--
Don''t forget to rate the post if it was helpful!
email address is invalid
Please reply to newsgroup only.
fredg said:
I created a table that lists our Loan Officers and their approved lending
limits.
The table has these fields: officer name, unsecured limit, secured limit,
and OD limit only. On a data entry form, I'd like the unsecured, secured and
OD limit to automatically fill based on the selection of the Officer name.
Does anyone have any suggestions?
Have an unbound combo box. Set it's control source to:
=DLookUp("[UnsecuredLimit]","TableName","[OfficerName] = """ &
Me.[OfficerName] & """")
Do the same for each of the other values you wish to display on the
form.
Note: It's not a good idea to use the [OfficerName] field to search
by. In any large company (even small ones), having more than one
person with the same name is not uncommon... Just check your local
telephone directory for Jones, Smith, Andersen, etc.
Each person in the company should have their unique EmployeeID.
Use that instead of the name.
Include the [EmployeeID] field in the table and on the form (usually
best done using a combo box that displays the OfficerName but is bound
to the [EmployeeID] field).
If the EmployeeID is a number datatype, then use:
=DLookUp("[UnsecuredLimit]","TableName","[OfficerID] = " &
Me.[OfficerID])
