I am writing a simple form which displays a list of names in a ComboBox
and then populates the text boxes on the form, problem is the forms
works for the first choice but fails after that.

I stepped through the code and I see that the .ListIndex value is
correct only the first time.

Am I doing this the wrong way, in the end I just want a simple form to
view, edit and add records.

Thanks in advance.
Sal


The spreadsheet is labeled "facilities" and is populated:
---------------------------------------------------------
Line Facility Street City ST ZipCode
1 Copps 3 Maple Mystic CT 06845
....
168 G.Acres Ryan Ave Canaan CT 06018


The code is:
------------
Option Explicit
Dim facilities_count As Integer
Dim facilities_current As Integer
Dim rng_facilities As Range

Private Sub cmd_Close_Click()
Unload Me
Worksheets("Facilities").Activate
End Sub

Private Sub txt_Facility_Name_Change()
' Write value back to cell

End Sub

Private Sub txt_Facility_Name_DropButtonClick()
Dim i As Integer
Worksheets("Facilities").Activate
facilities_count = ActiveSheet.Cells(600, 1).End(xlUp).Row
For i = 2 To facilities_count
txt_Facility_Name.AddItem (ActiveSheet.Cells(i, 2))
Next i
End Sub

Private Sub txt_Facility_Name_Click()
' Get index number of selected record
<<<< the next line is the bad code
facilities_current = txt_Facility_Name.ListIndex + 1
populate_facilities
End Sub

Private Sub populate_facilities()
Set rng_facilities = Range("A2", "I300")
txt_Facility_Street.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 3, False)
txt_Facility_City.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 4, False)
txt_Facility_State.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 5, False)
txt_Facility_Zip.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 6, False)
txt_Facility_Phone.Value = WorksheetFunction.VLookup _
(facilities_current, rng_facilities, 7, False)
End Sub