Populate value in form based on other fields

[Marcie]

I have a form that will enter new people into a table. Based on information
entered in the form I want a field populated from another table.

PayrollDetail is built based on information pulled out of our payroll system
and it contains annual rate. This database is used to supply information
before it is entered so I have a duplicated salaryplan table that contains
SalaryPlan, Grade and Step. When they entere information SalaryPlan, grade
and Step into PayrollDetail I want it to lookup the pay rate value and
populate PayrollDetails pay rate. Can this be accomplished?

 

[Burnsie]

Marcie

Use the Dlookup Function

Pay rate = Dlookup("Pay Rate", "SalaryPlan", "SalaryPlan = '" &
[txtSalaryPlan] & "' Grade = '" & [txtGrade] & "' Step = '" & [txtStep] & "'")

Pay Rate is the name of the field in your SalaryPlan table.
SalaryPlan is the name of the table
The third part of the DLookup Function is your where clause.
SalaryPlan, Grade and Step are the names of the fields in your SalaryPlan
table.
[txtSalaryPlan], [txtGrade] and [txtStep] are the names of the controls on
your form.

The hard part is getting the quotations right to create the valid where
clause. For a single where clause it would like like this:

If SalaryPlan is stored as a string:
"SalaryPlan = '" & [txtSalaryPlan] & "'" So after the equals you would have
a single quote and double quote and at the end you have a double quote,
single quote and double qoute.

If SalaryPlan is stored as a number
"SalaryPlan = " & [txtSalaryPlan] So after the equals you would have a
double quote.

Burnsie

 

[Marcie]

I am using the control source on the form field with the following:
=DLookUp([Annual Rate],[PS_SalaryPlans],"SalPlan = '" & [Sal Plan] & "'
Grade = '" & [Grade] & "'step = '" & [Step] & "'")
I get a #name error I think it is probably the qoutes and double quotes but
not sure. Can you explain what they are building in the where clause?
thanks for your help

Marcie

Use the Dlookup Function

Pay rate = Dlookup("Pay Rate", "SalaryPlan", "SalaryPlan = '" &
[txtSalaryPlan] & "' Grade = '" & [txtGrade] & "' Step = '" & [txtStep] & "'")

Pay Rate is the name of the field in your SalaryPlan table.
SalaryPlan is the name of the table
The third part of the DLookup Function is your where clause.
SalaryPlan, Grade and Step are the names of the fields in your SalaryPlan
table.
[txtSalaryPlan], [txtGrade] and [txtStep] are the names of the controls on
your form.

The hard part is getting the quotations right to create the valid where
clause. For a single where clause it would like like this:

If SalaryPlan is stored as a string:
"SalaryPlan = '" & [txtSalaryPlan] & "'" So after the equals you would have
a single quote and double quote and at the end you have a double quote,
single quote and double qoute.

If SalaryPlan is stored as a number
"SalaryPlan = " & [txtSalaryPlan] So after the equals you would have a
double quote.

Burnsie

I have a form that will enter new people into a table. Based on information
entered in the form I want a field populated from another table.

PayrollDetail is built based on information pulled out of our payroll system
and it contains annual rate. This database is used to supply information
before it is entered so I have a duplicated salaryplan table that contains
SalaryPlan, Grade and Step. When they entere information SalaryPlan, grade
and Step into PayrollDetail I want it to lookup the pay rate value and
populate PayrollDetails pay rate. Can this be accomplished?

 

[Burnsie]

Marcie,

My bad on the Where portion. It should have said something like this:
"SalPlan = '" & [Sal Plan] & "' And Grade = '" & [Grade] & "' And Step = '"
& [Step] & "'"

Just to make sure we are on the same page, what are the field types for [Sal
Plan], [Grade] and [Step]?

If I understood your question correctly, the [Annual Rate] is defined by the
three fields of [Sal Plan], [Grade] and [Step]. So, I assumed that your
table had fields of [Annual Rate], [Sal Plan], [Grade] and [Step]. I then
figured that you could have many records where the [Annual Rate] is different
if the only thing different in [Sal Plan], [Grade] and [Step] is the step.
Is this correct?

Burnsie

I am using the control source on the form field with the following:
=DLookUp([Annual Rate],[PS_SalaryPlans],"SalPlan = '" & [Sal Plan] & "'
Grade = '" & [Grade] & "'step = '" & [Step] & "'")
I get a #name error I think it is probably the qoutes and double quotes but
not sure. Can you explain what they are building in the where clause?
thanks for your help

Marcie

Use the Dlookup Function

Pay rate = Dlookup("Pay Rate", "SalaryPlan", "SalaryPlan = '" &
[txtSalaryPlan] & "' Grade = '" & [txtGrade] & "' Step = '" & [txtStep] & "'")

Pay Rate is the name of the field in your SalaryPlan table.
SalaryPlan is the name of the table
The third part of the DLookup Function is your where clause.
SalaryPlan, Grade and Step are the names of the fields in your SalaryPlan
table.
[txtSalaryPlan], [txtGrade] and [txtStep] are the names of the controls on
your form.

The hard part is getting the quotations right to create the valid where
clause. For a single where clause it would like like this:

If SalaryPlan is stored as a string:
"SalaryPlan = '" & [txtSalaryPlan] & "'" So after the equals you would have
a single quote and double quote and at the end you have a double quote,
single quote and double qoute.

If SalaryPlan is stored as a number
"SalaryPlan = " & [txtSalaryPlan] So after the equals you would have a
double quote.

Burnsie

I have a form that will enter new people into a table. Based on information
entered in the form I want a field populated from another table.

PayrollDetail is built based on information pulled out of our payroll system
and it contains annual rate. This database is used to supply information
before it is entered so I have a duplicated salaryplan table that contains
SalaryPlan, Grade and Step. When they entere information SalaryPlan, grade
and Step into PayrollDetail I want it to lookup the pay rate value and
populate PayrollDetails pay rate. Can this be accomplished?