excel - 如何从列表框中填充文本框？

Question

我有一个包含员工编号和姓名的列表框。所以你看到的是 0001-John Doe 但现在当我尝试用列表框信息填充文本框时它不起作用。

我如何填充我的列表框:

Private Sub UserForm_Initialize()
 Dim conn As New ADODB.Connection
 Dim rsst As New ADODB.Recordset

   dbPath = Sheets("Info").Range("a2").Value
    conn.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath      

  rsst.Open "SELECT EmpNumber,EmpFirstName,EmpSurname FROM Employees;", _
   conn, adOpenStatic

 With rsst
 .MoveFirst
  Do Until .EOF

Me.lbxNextOfKinEmployeeNumber.AddItem rsst.Fields(0).Value & " - " & rsst.Fields(1) & " " & " " & rsst.Fields(2) & " "
 rsst.MoveNext
  Loop
 End With
End Sub

我的 View 按钮中有什么:

Private Sub btnNextOfKinSelect_Click()
            Dim CNOK As New ADODB.Connection
            Dim RNOK As New ADODB.Recordset

            txtNextofKinEmployeeNumber.Enabled = False
            'btnEditNextOfKin.Visible = True

        If lbxNextOfKinEmployeeNumber.ListIndex = -1 Then
                MsgBox "Please Select a Employee Number"
            Else

            dbPath = Sheets("Info").Range("a2").Value
             CNOK.Open "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & dbPath

    RNOK.Open "SELECT * FROM EmployeeNextOfKin Where EmpNumber ='" & 
     Me.lbxNextOfKinEmployeeNumber & "'", _
      CNOK, adOpenStatic
       RNOK.MoveFirst

                txtNextofKinEmployeeNumber.Value = RNOK("EmpNumber")
                txtNextOfKinName.Value = RNOK("NextOfKinName")
                txtNextOfKinSurname.Value = RNOK("NextOfKinSurname")
                txtContactNumber.Value = RNOK("NextofKinContactNumber")
                txtContactAddressLine1.Value = RNOK("NextofKinAddress")
                txtNextofKinCity.Value = RNOK("NextofKinCity")
                txtCellNumber.Value = RNOK("NextofKinCellNumber")
    End If
End Sub

我想要的是当我选择 0001-John Doe 时，它​​应该从我的数据库中获取数据并填充我的文本框。

Answer 1

代码正在尝试匹配连接的字符串 0001-john doe EmpNumber 字段值 0001 .选项:

将列表框 RowSource 设置为多列

Do Until rsst.EOF
With Me.lbxNextOfKinEmployeeNumber
    .ColumnCount = 2
    .ColumnWidths = "0;2"
    .AddItem rsst(0) & ";" & rsst(0) & " - " & rsst(1) & " " & rsst(2)
End With
rsst.MoveNext
Loop

如果用户更喜欢键入名称，请不要在连接字符串中包含 EmpNumber。姓氏在前可能更合适: .AddItem rsst(0) & ";" & rsst(2) & ", " & rsst(1)可能要对记录集进行排序: ORDER BY EmpSurname

从连接的字符串中提取 EmpNumberLeft(Me.lbxNextOfKinEmployeeNumber, 4)

使用 LIKE 和通配符WHERE '" & Me.lbxNextOfKinEmployeeNumber & "' LIKE [EmpNumber] & '*'"