Autofill part of form depending on multiple inputs

[Scott Reed via AccessMonster.com]

I found a similar thread (
http://www.accessmonster.com/Uwe/Fo...tofill-fields-based-on-other-fields-responses
) that pertains to my issue, but this thread deals with the following:
Cookie flour
Cookie sugar
Cookie milk
Pancake flour
Pancake butter
Tart flour
Tart sugar
Tart fruit

mine differs in that I have 2 fields that require input for a needed output:
example
if field 1 = 1 and field 2 = low then field 3 would be 3.0
if field 1 = 1 and field 2 = medium then field 3 would be 4.0

Fields 1 and 2 have set variables (field 1 is numbered 1 through 9 and there
are three choices for each number, Low: medium or high and each choice has a
different outputed number associated with it)

My question is on my form, when a user selects fields 1 and 2, can the 3rd
field automattically be generated based on the combination of 1 and 2?

 

[Douglas J. Steele]

The easiest way would be to store the values in a table.

I realize that you're posting this to the macros newsgroup, but using VBA
would be extremely simple.

Use combo boxes for field1 and field2. If you're storing the values for
Field1, Field2 and Field3 in Table1, use the following query for the
RowSource of the first combo box (Combo1):

SELECT DISTINCT Field1 FROM Table1 ORDER BY Field1

For the AfterUpdate event of that combo box, use code like:

Private Sub Combo1_AfterUpdate()

Me.Combo2.RowSource = "SELECT Field2, Field3 " & _
"FROM Table1 " & _
"WHERE Field1 = '" & Me.Combo1 & "' " & _
"ORDER BY Field2"

End Sub

For the AfterUpdate of the second combo box (Combo2), use:

Private Sub Combo2_AfterUpdate()

Me.Field3 = Me.Combo2.Columns(1)

End Sub