CODEWITHSUNDEEP
ms-accesslistboxvba
GothamHunter
GothamHunter

Reputation: 37

Listbox records deselecting when running a DoCmd.Requery in Access VBA

In my app, users are selecting records from a listbox that they want to edit. Before they're able to edit, however, there is an additional query to make sure that info they want to change is correct. Below is the flow:

  1. User inputs an Item_Number and clicks a button to query the DB for all Location(s) that contain that Item_Number. These populate into a listbox.
  2. User selects the location(s) from the listbox that he/she wishes to edit
  3. User inputs a new Last_Cost and/or new Vendor_ID for the selected items and clicks to confirm edit
  4. In VBA, there is a query against another DB to ensure the new Vendor_ID is valid
  5. The flow continues

The issue is that when the VBA code for the requery

DoCmd.Requery (VendorNumbers)

executes, it deselects all of the selected locations in the listbox. I've been able to narrow the issue down to this exact section of the VBA code.

I'm wondering if there is a way to ensure that the selected records stay selected, or if there is a way to store which rows were selected so they can be referenced again after the requery code is ran?

Entirety of the code. It may look slightly confusing because I don't have anything noted with "Me."

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

Below is the VendorNumbers query:

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;

Again, this is just to verify that the Vendor_ID they input is valid, i.e. there are existing rows.

Upvotes: 0

Views: 592

Answers (1)

Newd
Newd

Reputation: 2185

I think that this may be the best option because I don't know if you will ever be able to stop the requery from causing a refresh. I would be lying to say that I tested out this SQL but this is the basic idea for how I would perform what you are looking for. Instead of requerying VendorNumbers you just basically make it and run it right in VBA. This is also assuming that EditItemsInExistingPlants is the form from where you are running this code, if not then just adjust appropriately. 

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

Upvotes: 1

Related Questions