lookup\Autofill

[twisted]

Hello all,

Is it possible to look up 2 field to auto fill another field?


Field 1 is "Exposure" this autofill with "Probability" is user input.
I need to lookup Exposure and Probability and autofill a score in to "Risk Rating". This to stop incorrect data being inputted.
I have a table with all the results combination in it.
Would it be possible to use a Dlookup to look at Exposure and Probability to give me the score

I tried a Combox with autofill. But the power to be would like it done without user input.

Thanks
Andrew

[June7]

You mean something like:

DLookup("Score", "tablename", "Exposure='" & Me.textboxExposure & "' AND Probability='" & Me.textboxProbability & "'")

[twisted]

Thanks June7
Can I put this in to Control Source?
or
Can I put it under "event procedure" on charge?

[June7]

The expression can be used in ControlSource, or in query, or in code.

[twisted]

The expression can be used in ControlSource, or in query, or in code.

I ended up using ControlSource on my form. but it is returning #name?. I have also noticed [] around my code.
This is what my code looks like, including the [] that i get after entering.
=DLookUp("RiskRating","tblRisk","ExcavationRating= '" & [Me].[ExcavationRating] & "'AND Probability='" & [Me].[TextboxProbability] & "")

[June7]

Sorry, the Me. prefix is intended only for VBA code. It is an alias for the full form name. Drop that if using in ControlSource. In a query would have to use the actual form name.

Don't forget a space in front of the AND after the apostrophe.

[twisted]

Thanks again,

I did try it in VBA first time around. i ended up with a compile error.
Still the same problem
=DLookUp("RiskRating","tblRisk","ExcavationRating= '" & [TextboxExcavationRating] & "' AND Probability='" & [TextboxProbability] & "")

[twisted]

I can post the database if you think it will help at all.
Its not pretty tho

[June7]

You can provide db. Follow instructions at bottom of my post.

If you use in VBA code, need to set a variable or field or control to the value returned by the DLookup, like:

Me![Risk Rating] = DLookup(...)

[twisted]

MWRA.zip

Welcome to comment on anything in the database. As i am still new at this.

[June7]

The DLookup isn't using the actual field names from tblRisk nor the actual field or textbox names from the form. Don't use the dummy names from the example I provided, use your real names.

I don't see fields in tblRisk that correspond to Probability and ExcavationRating.

Domain aggregate functions (DLookup, DSum, DCount, etc) follow this syntax:

FunctionNameHere("fieldname with desired data to return", "tablename to search", "criteria expression")

Example of criteria expression:

"fieldname in table = parameter"

Parameter can be a concatenated variable. Reference to form field/textbox is a variable.

"fieldname in table = " & textbox or field from form

[twisted]

The DLookup isn't using the actual field names from tblRisk nor the actual textbox names from the form. Don't use the dummy names from the example I provided, use your real names.

I don't see fields in tblRisk that correspond to Probability and ExcavationRating.

Sorry. That was a mistake. Did not mean to provide miss leading information.

tblRisk is as follow. "Probability" is "Risk" and "ExcavationRating" is "RiskLetter". "RiskRating" is "Number". So I need to look at the "Risk" & "RiskLetter" to come up with a "Number" which is my RiskRating.

Again thank you June7 and sorry for the trouble