在Access VBA中运行DoCmd.Requery时取消选择列表框中的记录

Question

在我的应用程序中，用户正在从要编辑的列表框中选择记录。然而，在他们能够编辑之前，还有一个额外的查询来确保他们想要更改的信息是正确的。以下是流程：

1. 用户输入一个Item_Number，然后单击一个按钮来查询包含该Item_Number的所有位置的数据库。这些填充到列表框中。
2. 用户从他/她希望编辑
3. 用户列表框的位置（一个或多个）输入用于所选择的项的新Last_Cost和/或新VENDOR_ID并点击确认编辑
4. 在VBA中，有对另一个数据库的查询，以确保新VENDOR_ID有效
5. 流程继续

的问题是，当重新查询

DoCmd.Requery (VendorNumbers) 

VBA代码

执行时，它将取消选择列表框中的所有选定位置。我已经能够将问题缩小到VBA代码的确切部分。

我想知道是否有办法确保所选记录保持选定状态，或者如果有方法可以存储哪些行被选中，以便在重新运行代码后可以再次引用它们？

完整的代码。这看起来可能有点混乱，因为我没有用“我”来记录任何内容。

Private Sub EditInLocationsButton_Click() 
EditVendorIDFixed = Null 
EditVendorIDFixed = "    " & EditVendorIDInput 

DoCmd.Requery (VendorNumbers) 
If EditVendorIDFixed = "    " Or DCount("*", "VendorNumbers") > 0 Then 
    EditResponse = MsgBox("Are you sure you want to edit the selected items?", vbYesNo, "Edit Selected Items?") 
    If EditResponse = vbYes Then 
     'EditItemNumberResultsList.RowSource = "" 
     Dim i As Integer 
     For i = 0 To EditItemNumberResultsList.ListCount - 1 
      If EditItemNumberResultsList.Selected(i) Then 
       LastCost = EditItemNumberResultsList.Column(4, i) 
       EditID = EditItemNumberResultsList.Column(5, i) 
       VendorID = EditItemNumberResultsList.Column(6, i) 
       'MsgBox for verification purposes 
       MsgBox (LastCost & "-" & EditID & "-" & VendorID) 
       If EditLastCostInput = Null Then 
        LastCostforEditQuery = LastCost 
       Else 
        LastCostforEditQuery = EditLastCostInput 
       End If 

       If EditVendorIDInput = Null Then 
        VendorIDforEditQuery = VendorID 
       Else 
        VendorIDforEditQuery = EditVendorIDFixed 
       End If 
      End If 
      txtEditID = EditID 
      With DoCmd 
       .SetWarnings False 
       .OpenQuery "EditIminvlocRecords" 
       .SetWarnings True 
      End With 
     Next i 
     MsgBox ("The selected plants have been updated.") 
    End If 
Else 
    MsgBox ("Error: The requested Vendor ID does not exist.") 
End If 
End Sub 

下面是VendorNumbers查询：

SELECT dbo_apvenfil_sql.vend_no 
FROM dbo_apvenfil_sql 
WHERE (((dbo_apvenfil_sql.vend_no)=[Forms]![EditItemsInExistingPlants]![EditVendorIDFixed])) 
ORDER BY dbo_apvenfil_sql.vend_no; 

同样，这只是为了验证他们VENDOR_ID输入是有效的，即有现有行。

Answer 1

我认为这可能是最好的选择，因为我不知道你是否永远无法停止查询引起刷新。我会撒谎说，我测试了这个SQL，但这是我如何执行你正在寻找的基本想法。而不是重新查询VendorNumbers，你只需要在VBA中运行它。这也假设EditItemsInExistingPlants是从你运行这个代码的位置开始的形式，如果没有，那么就适当地进行调整。

Private Sub EditInLocationsButton_Click() 
    EditVendorIDFixed = Null 
    EditVendorIDFixed = "    " & EditVendorIDInput 

    Dim db   As DAO.Database 
    Dim rs   As DAO.Recordset 
    Dim strSQLQuery As String 

    strSQLQuery = "SELECT dbo_apvenfil_sql.vend_no " & _ 
        "FROM dbo_apvenfil_sql " & _ 
        "WHERE (((dbo_apvenfil_sql.vend_no)= " & Me.EditVendorIDFixed & ")) " & _ 
        "ORDER BY dbo_apvenfil_sql.vend_no" 

    Set db = CurrentDb 
    Set rs = db.OpenRecordset(strSQLQuery, dbOpenDynaset) 

    If EditVendorIDFixed = "    " Or rs.Count > 0 Then 
     EditResponse = MsgBox("Are you sure you want to edit the selected items?", vbYesNo, "Edit Selected Items?") 
     If EditResponse = vbYes Then 

      'EditItemNumberResultsList.RowSource = "" 
      Dim i As Integer 
      For i = 0 To EditItemNumberResultsList.ListCount - 1 
       If EditItemNumberResultsList.Selected(i) Then 
        LastCost = EditItemNumberResultsList.Column(4, i) 
        EditID = EditItemNumberResultsList.Column(5, i) 
        VendorID = EditItemNumberResultsList.Column(6, i) 
        'MsgBox for verification purposes 
        MsgBox (LastCost & "-" & EditID & "-" & VendorID) 
        If EditLastCostInput = Null Then 
         LastCostforEditQuery = LastCost 
        Else 
         LastCostforEditQuery = EditLastCostInput 
        End If 

        If EditVendorIDInput = Null Then 
         VendorIDforEditQuery = VendorID 
        Else 
         VendorIDforEditQuery = EditVendorIDFixed 
        End If 
       End If 
       txtEditID = EditID 
       With DoCmd 
        .SetWarnings False 
        .OpenQuery "EditIminvlocRecords" 
        .SetWarnings True 
       End With 
      Next i 
      MsgBox ("The selected plants have been updated.") 
     End If 
    Else 
     MsgBox ("Error: The requested Vendor ID does not exist.") 
    End If 
End Sub