Form Function


M

mms

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?
 
Ad

Advertisements

F

fredg

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])
 
D

Dale Fye

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])
 
Ad

Advertisements

M

mms

Fred - Dale

Thanks for the suggestions -- I was on the right track - sorta - I
appreciate the help.
--
mms


Dale Fye said:
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])
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads


Top