Lookup field table/value based on another fields value

[RobertEastham]

Is it possible for a field to change depending on the value of another? What I want is to have lookup field 1 and in it I have 5 letter types, if I choose "support letter" then the next lookup field displays the choices from the "support letter" table. If I choose "transfer letter" then it displays the choices from the "transfer letter" table and so on. Am I making any sense?

Thank you for your help.

 

[anvil19]

Hi Robert

If the values in your list are named after tables then I would just add the following line to the Change Event of the list/ComboBox (or whatever) control, something like so :

Private Sub Combo3_Change()
    Dim strSQL As String
    Dim vara

    vara = UserForm1.Combo1.Value
    strSQL = "SELECT * FROM " & vara & ""

    UserForm1.RecordSource = strSQL
End Sub

Add this code to the control that has the options. Just change the names (UserForm1) and numbers (Combo3) to whatever yours are and every time you select a new value in the list your form will show the records for that table name - the "Transfer Letter" table or the "Support Letter" table etc.

Try That

anvil19

 

[bat17]

Yes, assuming that you are using combo boxes the way to do it is to base the second box on a query that is filterd by using the first box as the criteria. In the first combobox you need to set its AfterUpdate event to requery the second combobox.

HTH

Peter