Lookup Field Based on another Lookup Field

beanhead0321 · #1 (**permalink**) 07-19-11, 21:26

I have a form that has two combo boxes Name and Model. When these two have been selected, click a button that adds the value of the two combo boxes into a table. However, linked with each combination is a price that I would like to have automatically appear on the table and not on the form after the two choices have been made. How do I do that? The price is in a table with the name and model.

Sinndho · #2 (**permalink**) 07-20-11, 04:49

Provided that the combination Name + Model is a unique identifier for the source table, you could use:

Code:

    Dim strSQL As String
    
    strSQL = "INSERT INTO <DestinationTable> ( Name, Model, Price ) " & _
             "SELECT Name, Model, Price " & _
             "FROM <SourceTable> " & _
             "WHERE <SourceTable>.Name = '" & Me.<ComboName>.Value & "' AND " & _
                   "<SourceTable>.Model = '" & Me.<ComboModel>.Value & "';"
    CurrentDb.Execute strSQL, dbFailOnError

The elements between brackets (<>) must be replaced by the actual names. If the columns containing the Name and the Model in the combos are not the bound columns of the combos, you have to replace Me.Combo.Value with Me.Combo.Column(x) where x is the index of the proper column (the index of the first column of a combo is 0 (zero)).

Internet Services

Web Development

Digital Marketing

Consumer Tech