DLookup Help, " Moved to Correct Forum"

[melodyF]

I have two tables "tblEmployees and "tblResults", the employees tables holds current salary dollars and results holds incentive points, etc. My relationship is a one(Employees) to many (Results)using EmployeeID as the key. For each new record input in the Results table I want to pull the current salary dollars from the employees table to populate the salary field in the result table. I do incentive calculations per month and roll them up each quarter and need to hold the historical salary dollar amount for that month in the result table.

How do I default the salary data in the Employees.salary field to the salary data in the Results.salary field for each record entered. I have tried the below, but get nothing
Private Sub Salary_AfterUpdate()
Me![frmSummary].[Salary] = DLookup("Salary", "[tblEmployees]", "[EmployeeID]")

Thanks for suggesting DLookup Rich...

 

[Jack Cowley]

DLookup("[Salary]", "tblEmployees", "[EmployeeID] = " & Me.EmployeeID)

The above assumes you have a field on your form called EmployeeID and that EmployeeID is a numeric field...

 

[melodyF]

Jack, it works to a point, the salary will not show in the field or be captured in the table until I type something into the field and only then does it show. Any Suggestions?

 

[melodyF]

Jack, the above works to a point. The Salary will only show if I try to type something in the field, then it will pop up. Any suggestions?

 

[David R]

You are using the Salary control's AfterUpdate event to update the frmSummary field Salary - is this all on one form? If so you are updating your own control from AfterUpdate and that may be the problem. Aren't we trying to update a different control when the Salary control is filled in?

[This message has been edited by David R (edited 04-26-2002).]

 

[melodyF]

The frmSummary is the a subform that contains both the fields Salary and EmployeeID. You are correct in that I'm using the AfterUpdate Event of the Salary field. The salary is not filled in. It should be pulled from the Employee table using the EmployeeID. This allows me to update the results table with the Employees current salary with other inputed data for calculations.

It looks like I'm getting close, but still so far away..

Thanks, for any suggestions

 

[Jack Cowley]

I hope I am following you correctly.... Try this in the On Current event of the form...

If Not Me.NewRecord Then Exit Sub

Me![Salary] = DLookup("[Salary]", "tblEmployees", "[EmployeeID] = " & Me.EmployeeID)