Auto Populating a Field From Data Range

[homeshark]

I am wondering how you would go about setting this up in a form.

FYI this is on a switchboard gui. In Field A, someone can type in a specific number. I would like Field B to autopopulate with information based on what is in Field A. Field B’s information will come from a range.



This is an example of possible table data



Field A Field B

Range 1000-1999 H/K

Range 2000-2999 Cyp





As you can see, if someone types in a number such as, 1520 in Field A, it would return the value of H/K to Field B. If someone were to type in the value 2000 into Field A, it would return the value of Cyp in Field B.



If you need any clarification, please let me know



Thank you for your time,



Chase

 

[MarkK]

If your table, say tData, looks like ...

ID    Min    Max   Text
1     1000  1999  "H/K"
2     2000  2999  "Cyp"

Then do someting like this in the BeforeUpdate handler for the FieldA ...

Private Sub FieldA_BeforeUpdate(Cancel as Integer)
  If IsNumeric(Me.FieldA) Then  [COLOR="Green"]'do FieldA data validation here[/COLOR]
    Me.FieldB = _
      DLookup("Text", "tData", "Min <= " & Me.FieldA & " AND Max >= " & Me.FieldA)
  Else  [COLOR="Green"]'recover from invalid data here[/COLOR]
    Me.FieldA.Undo
    MsgBox "Warn user that data is not valid"
    Cancel = True
  End If
End Sub