Refresh fields with dlookup

[Foster Purdy via AccessMonster.com]

I have a form that has two drop down fields. The user selects a pay group
and a pay year. Based on those two fields there are two unbound fields that
calculate the biweekly and annual pay through dlookup:

BW_Rate: DLookUp("[hourly_amount]","tbl_group","[group]=" & [Group] And "
[step]=" & [year] And "[year]= " & Year([last_review_date]))*84

AnnualDollars: (DLookUp("[hourly_amount]","tbl_group","[group]=" & [Group]
And "[step]=" & [year] And "[year]= " & Year([last_review_date]))*84)*26

In order to have those two fields refreshed when the drop down field for pay
year is changed I included the following code in the pay year Change Event:

Me.BW_Rate.Requery
Me.AnnualDollars.Requery

When I select a new value in the pay year field the two fields appear to
refresh but still show the first record in the table new matter what I choose.


Is there something else I should be using other than requery?

 

[Douglas J. Steele]

Your Ands need to be inside the strings:

BW_Rate: DLookUp("[hourly_amount]","tbl_group","[group]=" & [Group] & " And
[step]=" & [year] " & " And [year]= " & Year([last_review_date]))*84

AnnualDollars: (DLookUp("[hourly_amount]","tbl_group","[group]=" & [Group] &
" And [step]=" & [year] & " And [year]= " & Year([last_review_date]))*84)*26

You'd be well advised to rename the Group and Year fields in your table.
Both are reserved word, and shouldn't be used for your own purposes. For a
comprehensive list of names you should avoid, see what Allen Browne has at
http://www.allenbrowne.com/AppIssueBadWord.html