Michael Franz
asked on
MS 2010 Access Forms and Lookups
I am trying to create an Access Form to replace the Excel Workbook we currently use.
The Excel Workbook had a series of VLookup functions and drop downs to keep the entries accurate and consistent.
I am looking for help on how to do this in Access 2010.
Example 1) In the Form, an employee will select the type of new business he/she wrote and the proper bonus amount will then be displayed automatically. The bonus amount is dependent on the type of new business.
Example 2) An employee will select the company and then the line of the new business, enter the premium amount, and then the correct commission rate will be selected to display the earned revenue. The rate is dependent on the line. The line is dependent on the company.
Thank you for your help.
The Excel Workbook had a series of VLookup functions and drop downs to keep the entries accurate and consistent.
I am looking for help on how to do this in Access 2010.
Example 1) In the Form, an employee will select the type of new business he/she wrote and the proper bonus amount will then be displayed automatically. The bonus amount is dependent on the type of new business.
Example 2) An employee will select the company and then the line of the new business, enter the premium amount, and then the correct commission rate will be selected to display the earned revenue. The rate is dependent on the line. The line is dependent on the company.
Thank you for your help.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Select Busines_type, bonus_amount, company, premium_amount, commission_rate, earned_revenue From...
You will have above data in a query. Most likely coming from several tables:
To handle missing data do:
Select Nz(Busines_type,"") As BType, Nz(bonus_amount,0) As BAmount, Nz(company,"") As Co, Nz(premium_amount,0) As Perm, Nz(commission_rate,0) As CommRate, nz(earned_revenue,0) As Revenue From...
Here we have 6 criterias (Busines_type, bonus_amount, company, premium_amount, commission_rate, earned_revenue)
Build 6 text boxes for each. The following code will filter if you put some data in several and ignore the rest. What you ignore, the query will ignore it too.
As criteria in the above query (you may call it qSearch) supply criteria like:
IIF(fnBusines_type()="<all
In a module have one function call for each criteria like
* please try for Busines_type only. After you make it work add another one, for example Company.
You will have above data in a query. Most likely coming from several tables:
To handle missing data do:
Select Nz(Busines_type,"") As BType, Nz(bonus_amount,0) As BAmount, Nz(company,"") As Co, Nz(premium_amount,0) As Perm, Nz(commission_rate,0) As CommRate, nz(earned_revenue,0) As Revenue From...
Here we have 6 criterias (Busines_type, bonus_amount, company, premium_amount, commission_rate, earned_revenue)
Build 6 text boxes for each. The following code will filter if you put some data in several and ignore the rest. What you ignore, the query will ignore it too.
As criteria in the above query (you may call it qSearch) supply criteria like:
IIF(fnBusines_type()="<all
In a module have one function call for each criteria like
Function fnBusines_type()As Variant ' this could change to string or double to match your need.
On Error GoTO 10
Dim varVal as variant
varVal = Nz(Form!Morm1!txtBusines_type, "<all>") 'put break point to make sure the value is read correctly.
fnBusines_type=varVal
Exit Function
10:
fnBusines_type="<all>"
End Function* please try for Busines_type only. After you make it work add another one, for example Company.
ASKER
thank you.
you have to import/create the Lookup values from excel to access tables and use the table as the rowsource of your drop down boxes.
from here you can do your coding to filter cascading combo boxes.