Retaining a Field Value

[Lupson2011]

I have a parent and sub form linkage.



The parent form holds static information such as name, employee number. but also have a locked field displaying the latest salary (see below for source of the value of this field). It also has a combo box which has a list of years, which you can change.

The subform has information regarding a specific role, so there may be one or more records per person, depending if their job has change during the course of the year.

For example, John Smith in 2009/2010, could have two records. Once with his original part time hours and the other displaying an increase in hours to full time which has occurred during the year 2009/2010.

The latest salary figure in the parent form is drawn from this sub form by clicking on a check box next to a salary field in the latest record.

However, what I am trying to do is to get the latest salary always shown in the parent form. What happens at the moment is that when you are in the first record (part time hours for John Smith), nothing is shown in the latest Salary field in the parent form field, as I have unchecked the check box indicating it to be the latest salary. However, when you click onto the next record, which is the John Smith full time hours records, there is a value in the parent form salary field, as I have checked the box indicating it to be the latest salary.

What i want access to do is to always have the latest salary shown in the parent form no matter what record you are in. If that makes sense?

FOr example, if there are six records in the subform indicating six changes in role for one person. If I have checked the box indicating which role has the latest salary, I want that shown at all times in the parent form salary field, no matter which of the 6 records you are in?

Hope this all makes sense and sorry for long winder explanation

thanks

marc

[Bob Fitz]

Hi

I would suggest using the DLookup(expr, domain, [criteria]) function as the Control Source property of the locked control that displays the salary. You will need to supply the criteria in the DLookup function so that it returns the correct amount. I think this will be the EmpoyeeID and that that Checkbox field value is True.
Take a look at the help files for DLookup. Post back if you need more help.

[Lupson2011]

Bob, thanks for that.

The subform records are differentiated by role, not EmployeeID, the Employee ID remains the same. The checkbox field is entitle LatestSalary, so would it be something like this...

Private Sub Salary_AfterUpdate()
Salary = DLookup("Annual Net Salary (FTE Equiv)", "TalbotTestRole", "LatestSalary" = 1)
End Sub

So what I am asking is...the value i want in the 'Salary' field on the parent form, I am asking to look for the 'Annual Net Salary (FTE Equiv)' in the 'TalbotTestRole' table, where the 'LatestSalary' checkbox = true?

Thats what I am after but didnt seem to work alas. Should it in after update? or have i done something wrong in the vb code?

THanks

marc

[Bob Fitz]

Is:
Annual Net Salary (FTE Equiv)
the name of a field? If it is it should be renamed using something without the brackets and spaces. eg: AnnualNetSalaryFteEquiv

[Lupson2011]

Alas, didnt make a difference, even tried..


Salary = DLookup("AnnualNetSalary", "TalbotTestRole", "LatestSalary" = True)

It just brings up a blank field.

Just trying to work it through. I am asking that, for the value to be put into the Salary field on the parent form, I want access to look at the AnnualNetSalary field in the TalbotTestRole table, but only pull through that value if the LatestSalary checkbox states true (has been checked). Should work?

Can anyone help? Its been infuriating me all afternoon as my logic thinks it should work?

thanks

marc