VBA代码不返回任何值，也不会报错；用户定义的

huangapple
go评论116阅读模式
英文:

VBA Code does not return any value or either give an error; user-define

问题

这是我的代码：它应该返回在msaccess文本框列表中搜索字段的结果。搜索条件使用逗号分隔。

  1. Option Compare Database
  3. Private Sub Command7_Click()
  5. End Sub
  7. Public Sub btnSearch2_Click()
  8.     Dim ArrayList() As String
  9.     Dim strSQL As String
  10.     Dim i As Integer
  11.     Dim rstSearchResults As ADODB.Recordset
  12.     
  13.     ' 通过逗号拆分参数值并将它们存储在数组中
  14.     ArrayList = Split(Me.txtSearchCriteria2.Value, ",")
  15.     
  16.     ' 构建SQL查询
  17.     strSQL = "SELECT CSP_Data.Manufacturer_Part_Number, CSP_Data.CSP_Number, CSP_Data.Rec_Min_Qty, CSP_Data.CSP_Type " & _
  18.              "FROM CSP_Data " & _
  19.              "WHERE Manufacturer_Part_Number ILike '*" & Me.txtSearchCriteria2.Value & "*'"
  20.     
  21.     ' 创建一个新的记录集
  22.     Set rstSearchResults = New ADODB.Recordset
  23.     
  24.     ' 基于SQL查询打开记录集
  25.     rstSearchResults.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
  26.     
  27.     ' 将列表框绑定到记录集
  28.     Me.lstSearchResults2.RowSource = rstSearchResults
  29.     
  30.     ' 重新查询列表框以显示数据
  31.     Me.lstSearchResults2.Requery
  32.     
  33.     ' 清理
  34.     rstSearchResults.Close
  35.     Set rstSearchResults = Nothing
  36. End Sub

尝试从ChatGPT获取此代码的修复，但没有用。我仍然没有收到任何响应。

英文:

Here's my code: It should return the results from search filed in msaccess text box list. Search criteria is separated with " , "
I am trying to search multiple part numbers.

  1. Option Compare Database
  3. Private Sub Command7_Click()
  5. End Sub
  7. Public Sub btnSearch2_Click()
  8.     Dim ArrayList() As String
  9.     Dim strSQL As String
  10.     Dim i As Integer
  11.     Dim rstSearchResults As ADODB.Recordset
  12.     
  13.     ' Split the parameter values by comma and store them in an array
  14.     ArrayList = Split(Me.txtSearchCriteria2.Value, ",")
  15.     
  16.     ' Construct the SQL query
  17.     strSQL = "SELECT CSP_Data.Manufacturer_Part_Number, CSP_Data.CSP_Number, CSP_Data.Rec_Min_Qty, CSP_Data.CSP_Type " & _
  18.              "FROM CSP_Data " & _
  19.              "WHERE Manufacturer_Part_Number ILike '*" & Me.txtSearchCriteria2.Value & "*'"
  20.     
  21.     ' Create a new recordset
  22.     Set rstSearchResults = New ADODB.Recordset
  23.     
  24.     ' Open the recordset based on the SQL query
  25.     rstSearchResults.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
  26.     
  27.     ' Bind the list box to the recordset
  28.     Me.lstSearchResults2.RowSource = rstSearchResults
  29.     
  30.     ' Requery the list box to display the data
  31.     Me.lstSearchResults2.Requery
  32.     
  33.     ' Clean up
  34.     rstSearchResults.Close
  35.     Set rstSearchResults = Nothing
  36. End Sub

tried getting this code fixed from chatgpt but its not useful. i still dont get any response

答案1

得分: 1

你需要逐个添加where子句：

  1. Dim el, sep As String, arraylist, rstSearchResults 
  3. arraylist = Split(Me.txtSearchCriteria2.Value, ",")
  5. strSQL = " SELECT CSP_Data.Manufacturer_Part_Number, CSP_Data.CSP_Number, " & _
  6.          " CSP_Data.Rec_Min_Qty, CSP_Data.CSP_Type " & _
  7.          " FROM CSP_Data WHERE "
  9. For Each el In arraylist
  10.     If Len(Trim(el)) > 0 Then
  11.         strSQL = strSQL & sep & "Manufacturer_Part_Number Like '*" & Trim(el) & "*' "
  12.         sep = " or " '在第一次迭代后填充 or
  13.     End If
  14. Next el
  16. Set rstSearchResults = New ADODB.Recordset
  17. rstSearchResults.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly
英文:

You need to add where clauses one-by-one:

  1. Dim el, sep As String, arraylist, rstSearchResults 
  3. arraylist = Split(Me.txtSearchCriteria2.Value, ",")
  5. strSQL = " SELECT CSP_Data.Manufacturer_Part_Number, CSP_Data.CSP_Number, " & _
  6.          " CSP_Data.Rec_Min_Qty, CSP_Data.CSP_Type " & _
  7.          " FROM CSP_Data WHERE "
  8.          
  9. For Each el In arraylist
  10.     If Len(Trim(el)) > 0 Then
  11.         strSQL = strSQL & sep & "Manufacturer_Part_Number Like '*" & Trim(el) & "*' "
  12.         sep = " or " 'populate or after first iteration
  13.     End If
  14. Next el
  16. Set rstSearchResults = New ADODB.Recordset
  17. rstSearchResults.Open strSQL, CurrentProject.Connection, adOpenStatic, adLockReadOnly

huangapple
  • 本文由 发表于 2023年7月18日 05:27:22
  • 转载请务必保留本文链接：https://go.coder-hub.com/76708174.html
  • listbox
  • ms-access
  • search
  • vba
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定