如何在打开表单时提示输入多个字符串?

Question

在我的 Access DB 中，我制作了一个表单，要求用户在打开时输入。用户可以输入多个项 ID(数字(，用逗号分隔，查询返回一个包含所有相应项的表。

我的 ID 现在也包含字母(不是我的决定！(，所以我需要将输入类型更改为字符串。有什么方法可以使用以下 VBA 代码的 linbes 像以前一样工作，但使用字符串？

Private Sub Form_Open(Cancel As Integer)
Dim m As String
m = InputBox("Please enter itemIDs, seperated by commas", "itemID")
If m <> "" Then
Me.Filter = "itemID in (" & m & ")"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

感谢您的帮助！干杯！

Answer 1

如果用引号将过滤器括起来，您的代码将起作用。所以：

Private Sub Form_Open(Cancel As Integer)
Dim m As String
m = InputBox("Please enter itemIDs, seperated by commas", "itemID")
If m <> "" Then
Me.Filter = "itemID in ('" & m & "')" 'Notice the single quotes
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

但是，麻烦就变成了多个itemID将无法正确过滤，因为您需要用引号将它们全部括起来。 喜欢：

Me.Filter = "itemID in ('ID001', 'ID002', 'etc')"

因此，如果用户输入包含讨厌的昏迷，则需要首先添加额外的引号。
您可以执行以下操作：

Private Sub Form_Open(Cancel As Integer)
Dim m As String
m = InputBox("Please enter itemIDs, seperated by commas", "itemID")
If m <> "" Then
'as a quick example, if m contains comas we surround them by quotes
If InStr(1, m, ",") <> 0 Then m = Replace(m, ",", "','")
'however this would only work if the user inputs the ids like "id1,id2"
'it fails to account for a space after the comas like "id1, id2" 
Me.Filter = "itemID in ('" & m & "')"
Me.FilterOn = True
Else
Me.FilterOn = False
End If
End Sub

希望这为您指明了正确的方向！