DLookup through 2 linked tables

[Russie]

Hi.

I have three tables.

1. tblSummaryBudget
2. tblInitialRiskAssessment
3. tblImpactScoresFinancial

On the summary budget form I have the RiskID field looking up from table 2 (tblInitialRiskAssessment).

Is DLookup able to query tblInitialRiskAssessment for a ImpactFinancial value (an integer) and using this to then look up the field RiskDescription in tblImpactScoresFinancial?

The link will look like this:

Tables: tblSummaryBudget<-->tblInitialRiskAssessment<-->tblImpactScoresFinancial
Fields: [RiskID]<-find->[ImpactFinancial]<-find->[FinancialDescription]

I'll give a star to an answer that puts me on the track.

Thanks for anything.

Russie

 

[PHV]

Take a look at the Recordset object and its FindFirst method.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zion7]

Don't mean to be sarcastic Russie, but didn't you answer your own question, basically,(just by stepping us through it)?

Or are you wondering, if it's feasible in ONE step.


sRiskDesc = DLookup("RiskDescription ", "tblImpactScoresFinancial","ImpactFinancial =" & DLookup("ImpactFinancial ", "tblInitialRiskAssessment"))


Or in two steps?

Dim iImpFin As integer
Dim sRiskDesc As String

iImpFin = DLookup("ImpactFinancial ", "tblInitialRiskAssessment")

sRiskDesc = DLookup("RiskDescription ", "tblImpactScoresFinancial","ImpactFinancial =" & iImpFin)

This is an outline, you probably need a Where clause, in the first lookup, & correct any field names & comparisons I assumed.

Otherwise, PHV's suggestion is equally effective.

Good luck either way!

 

[Russie]

Thanks for the help. The expression that I required is below.

=DLookUp("[DescriptionOfImpact]","tblImpactScoresFinancial","[FinancialImpact] = " & (DLookUp("[FinancialImpact]","tblInitialRiskAssessment","[RiskID] = '" & [Forms]![frmSummaryBudget]![txtRiskID] & "'")))

Russie