Autofill a field based on the selection of 2 other fields

[Traceyann1964]

Hello. Hoping someone can help me out.


I have:- field 1 in my form called "size" where the user can select from a choice of 5
field 2 called "type" again, the user can select from a choice

What I want is to autofill a field 3 based on the selection of both fields 1 and 2

For example, selected from field 1 = "24cm"
selected from field 2 = "Fitted"

Field 3 would be autofilled with a code " 12345"

Hope that makes sense -there could be many combinations

Fields are from 1 table and those fields are a "look up" from other tables

[ranman256]

Build a query that joins these tables to produce the results needed.
the query looks at the controls on the form.
..select * from tabe1,table2 where table1.field = table2.field

then you can call DLOOKUP to pull the result based on the 2 picks.....

vResult= Dlookup("[result]","qsQuery")

[Traceyann1964]

Thanks but they are from the same table. Does that matter?

[Bulzie]

Where is the autofill values located? Do you have a table that has the 3 values: 24cm, fitted, 12345 to be able to search on?

[Traceyann1964]

Yes I have 3 other tables for the looks up. TblSizes ,TblTypes and TblOrdercodes

[Bulzie]

I assume TblOrdercodes has Size and Type fields in it also to be able to look up which Ordercode to pull?

[Bulzie]

I think you would use a DLookup as ranman suggested. Check that both combo boxes have values, then lookup the value in TblOrdercodes table(again assume it has Size and Type fields).

If IsNull(Me.Size) then

MsgBox "Size must be entered"
Me.Size.SetFocus
End

Else

If IsNull(Me.Type) then

MsgBox "Type must be entered"
Me.Type.SetFocus
End

End IF

End IF

Me.OrderCode = DLookup("OrderCode" , "TBLOrderCodes" , "Size = '" & Me.Size & "' And "Type = '" & Me.Type & "'")

On the conditions, if my syntax is right, the quotes are like this:
SingleQuote DoubleQuote & Me.Size & DoubleQuote SingleQuote
SingleQuote DoubleQuote & Me.Type & DoubleQuote SingleQuote DoubleQuote

[Traceyann1964]

Thanks - I will see if I can do that