Access/VBA listbox and query issue

[gothamhunter]

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 a requery 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. Note that this requery is not the same query that populates the listbox.

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

I'll provide any additional info I can to assist you in assisting me further.

 

[gothamhunter]

I resolved this by putting the SQL query directly into the VBA rather than calling it. Was trying to avoid this but it doesn't seem to be avoidable.

 

[cabri]

You could separately store the results prior to the second query and then set them back afterwards.

 

[KLin]

https://msdn.microsoft.com/en-us/library/office/ff835338(v=office.15).aspx

Iterate through the listbox list checking if each value is selected. If it is, put the value in a table.

Then go through the list again and check if the value is in the table. If so, select it again.

 

[maluckey]

I resolved this by putting the SQL query directly into the VBA rather than calling it. Was trying to avoid this but it doesn't seem to be avoidable.

You mean as a sql string in the form? Also a good idea...as is cabri's idea

If your process requires you to requery, you could create a temp table on the fly and store the selected datafields there.

there's lots of methods to do what you're doing. I'm a fan of temp tables created locally on the users machine for this sort of thing. they take no extra server bandwidth and are dropped when you're done. Its not the most elgant, but there's not a lot of technical debt either.

Either way, glad you got it sorted out!

M