Auto Populate text box based on two combo box selections

[mikereid1977]

Hi all
I have a form with two combo boxes that are used to populate Consequence and Likelihood within table RiskAssessment. I have added a calculated field to the same table called ConsLikeliCombo that combines Consequence and Likelihood.
e.g.
Consequence possible values in combo box:
C1
C2
C3
C4
C5
Likelihhod possible values in combo box:
L1
L2
L3
L4
L5
ConsLikeliCombo is a calculated field based on Consequence and Likelihood:
e.g.
C1L1
C1L2
C1L3
etc
What I want to do is create a text box that will lookup the ConsLikeliCombo value into a separate table called RiskLookup and return the Risk value from this RiskLookup table.
RiskLookup table looks like:
ConsLikeliCombo Risk
C1L1 RL1
C1L2 RL1
C1L3 RL1
C1L4 RL2
C1L5 RL2
etc etc

Then I want to bind this Risk value to field OverallRisk, which is held within my RiskAssessment table.

I thought this was going to be simple but it's driving me crazy.
Can anyone assist?

 

[plog]

This will ultimately be achieved with a Dlookup (https://www.techonthenet.com/access/functions/domain/dlookup.php). However you have set up your tables incorrectly.

You should not store calculated values. Yes, I know they give you a calculated field option, but that's for people who don't understand databases. Instead of those you would create your calculated value in a query and just reference the query when you want it. However, your situation doesn't even require that. Your calculated value doesn't come into play for this at all.

So, remove the calculated field from RiskAssesment and break it out in RiskLookup into 2 fields. In RiskLookup store the Consequence and Likelihood values sepereately. Then, in the control on the form you want to display the the Risk field value, use a Dlookup as its control source using the two drop downs in the DLookup criteria argument.

 

[mikereid1977]

Thanks Plog, I was playing about with Dlookup but how would I combine two fields into the Dlookup expression?

 

[plog]

Dlookup("[Risk]", "RiskLookup", "[Cons]='" Forms!YourFormNameHere!ConsLikeliCombo & "' AND [Likeli]='" & Forms!YourFormNameHere!Likelihood & "'")

 

[mikereid1977]

Finally got it to work with the following:
=DLookUp("OverallRisk","RiskLookup","Consequence= '" & [Cons_Level] & "' AND Likelihood= '" & [Like_Level] & "'")

Thanks for your help Plog