Textbox lookup pricing from table/query not in Form Source 1

[nastar1]

Is it possible to program the Control Source of a form textbox to display a corresponding value from a table or query that is not included in the Form's recordsource and is dependant upon values in a couple of the form's other controls?

My current form is based upon a query of two tables to display project data (tblONE) and contract data (tblTWO).

I have a third table with unique pricing that changes each year for each contract. The pricing table is linked to the contract table in a many (pricing) to one (contract) relationship.

I'm now wanting to only display the appropriate pricing on my main project form based upon the year and contract# fields in the current record.

I've tried some IIF statements like: =IIf([Year]=[tblTHREE]![Year] And [CLIN]=[tblTHREE]![CLINID],[tblTHREE]![Rate],"N/A")

but I get an error as soon as I attempt to run the form at my FormLoad statment: DoCmd.GoToRecord acDataForm, "frmEDITWA", acNewRec

 

[GingerR]

Try DLookup()

=Dlookup("FieldNameInOtherTable","OtherTableName","WhereStatement")

the where statement might be something like

=dlookup("Rate","tblThree","[Year]=" & [Year] & " and [CLINID] = " & [CLIN])

This will give you the rate from tblThree where Year = the year on the form and ClinID = CLIN on the form.

Also you may not want to name a field "YEAR" because that is an Access Keyword.

Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244. Basics at

http://r937.com/relational.html

 

[nastar1]

GingerR,

I am now getting a #Error on the form in this unbound textbox with the new syntax of:

=DLookUp("[tblCLINRATE]![Rate]","[tblCLINRATE]","[tblCLINRATE]![Yr] = " & [FY] & " and [tblCLINRATE]![CLINID] = " & [CLIN])

 

[nastar1]

GingerR,

I discovered another tip related in a google search that suggested removing the ampersands and darned if that didnt work.

Thanks so much for the Dlookup tip. That was the biggest help of all.