Excel用户表单-基于组合框选择填充文本框

Question

我正在开发一个小项目，但是我对vba的了解还是有点有限，所以我想请求您的帮助。

项目是excel，数据库在访问中:

表1有3个字段:Brand, Model和Port

Apple/iPhone XR/lightning三星/Galaxy A42/usb-c小米/红米/usb-c

userform中的代码:

Private Sub UserForm_Initialize()
Call Brand
End Sub

Private Sub comboBrand_Change()

Set rs = New ADODB.Recordset
Module1.ConnectBD  
rs.Open "SELECT * FROM Table1", Conexao, adOpenKeyset, adLockReadOnly

txtModel.Value = rs(2)
If Not rs Is Nothing Then

rs.Close
Set rs = Nothing

End If

Module1.DisconnectBD
End Sub

Sub Brand()
Set rs = New ADODB.Recordset
Module1.ConnectBD

rs.Open "SELECT DISTINCT brand FROM Table1 ORDER BY brand ASC", Conexao, adOpenKeyset, adLockReadOnly

Do Until rs.EOF

comboBrand.AddItem rs!Brand

rs.MoveNext

Loop

If Not rs Is Nothing Then

rs.Close
Set rs = Nothing

End If

Module1.DisconnectBD
End Sub

我想要的是，每次我改变的值在ComboBox，文本框模型更新根据表;但我能得到的唯一值总是第一行，即"iPhone XR"，无论我在组合框中输入什么。我知道我必须引用组合框中选择的内容，但我做不到;你能帮忙吗?

Answer 1

经过几次尝试，我终于找到了解决方案:

Private Sub comboBrand_Change()
Set rs = New ADODB.Recordset
Module1.ConnectBD
rs.Open "SELECT * FROM Table1 WHERE brand ='" & UserForm1.comboBrand.Text & "'", Conexao, adOpenKeyset, adLockReadOnly

If rs.RecordCount > 0 Then

UserForm1.txtModel = rs!Model
UserForm1.txtPort = rs!Port
UserForm1.txtID = rs!ID

Else

MsgBox "Not found!", vbInformation, "Brand"

End If

If Not rs Is Nothing Then

rs.Close
Set rs = Nothing

End If
Module1.DisconnectBD
Exit Sub
End Sub