Dlookup issues (fairly new to VB) = Access 2007

[degas2011]

Hi, i am trying to get a latest salary figure to be shown in a text field on a parent form, sourced from a sub form.

But with the criteria that it is only for that employee number and that a checkbox indicating that it is the lastest salary has been checked. Here is the VB code I have used

Salary = DLookup("[AnnualNetSalary]", "TalbotTestRole", "[Employee Number]" = Forms!TalbotTestFormPersonalRecords!TalbotTestSubf orm![Employee Number] _
& "[LatestSalary]"" = Forms!TalbotTestFormPersonalRecords!TalbotTestSubf orm![LatestSalary] =" & True)


So, for example, I have two records in my database for John Smith and Tim Ball. Through the year 2008/2009 both have had two roles, where the salary has changed (these are subform records.

Although both salaries are recorded in the subform, on the parent form I only want to show the latest salary figure. To do this, I have a 'Latest Salary' checkbox in the sub form next to the salary field.

So, by clicking the checkbox, the main parent form record for John or Tim will show thier latest salaries.

Thats what I am trying to acheive with the Dlookup code.

Cant quite get it. Can anyone help? Its driving me mad!

thanks

marc

[Killer42]

I don't think you've told us in what way it's not-quite-right.

However, my debugging-senses are tingling.

The parameters of Dlookup consist of three string expressions - Field, Domain and Criteria. Your field and domain look ok. But, while I'm uncertain of my details here, I think your current criteria would resolve simply to something like the string "False". This seems unlikely to be what you intended.

You see, each of those "=" signs which is outside of quotes will be interpreted as a comparison. So for example, you're actually taking the string "[Employee Number]" and testing whether it's the same as the value of the [Employee Number] field on the subform. Seems pretty unlikely to match, wouldn't you say?

To test this, pull out the expression and paste it into a Debug.Print statement like this, immediately before the above statement...

Debug.Print "[Employee Number]" = Forms!TalbotTestFormPersonalRecords!TalbotTestSubform![Employee Number] & "[LatestSalary]"" = Forms!TalbotTestFormPersonalRecords!TalbotTestSubform![LatestSalary] =" & True

Or to boil it down and demonstrate what I mean in simpler form, try:

Debug.Print "A = B"
Debug.Print "A" = "B"

Here's an attempt at something closer to what I think you meant to write...

Salary = Dlookup("[AnnualNetSalary]","TalbotTestRole", _
"[Employee Number] = " _
& Forms!TalbotTestFormPersonalRecords!TalbotTestSubform![Employee Number] _
& " And [LatestSalary] = " _
& Forms!TalbotTestFormPersonalRecords!TalbotTestSubform![LatestSalary])

Couldn't quite work out what to do with your True on the end. You'll have to sort out that part. :)

[Killer42]

P.S. Another very quick and simple debugging technique you might try. Put a breakpoint on your "Salary=" line. When execution pauses there, select the entire Criteria parameter (that's everything from "[Employee Number]" to True) and hit Shift-F9 (quick watch). It should show you the value that the whole expression resolves to.