CODEWITHSUNDEEP
sqlvbams-accesslistboxms-access-forms
Alex Silverman
Alex Silverman

Reputation: 203

Access VBA's Listbox.Requery command clears ListItems

I have an Access form where I want users to select an AwardNumber. When they select the award, the associated documents for that award show up in either the Incomplete or the Complete listbox. Then I want them to be able to move items from one list to the other. The ListBoxes are single select.

enter image description here

Here's the backend:
The Incomplete ListBox (says Unbound in design) has 3 columns. The Bound column is 1, but the 1st and 3rd columns have 0 width and are thus hidden. The listbox also has the following RowSource:
SELECT DocTbl.DocID, DocTbl.DocTitle, DocTbl.DocLink FROM DocTbl WHERE ((DocTbl.Complete <> Yes) AND (DocTbl.AwardNumber = Forms!Select_Doc_To_Code!Award_CBox.Text)) ORDER BY DocTbl.DocID

(The Complete ListBox has the same, except instead of DocTbl.Complete <> Yes, it has DocTbl.Complete = Yes.)

When they select the AwardNumber in the ComboBox, there's some VBA that runs the following to populate the lists, which works as expected:
Me.Incompletes.Requery Me.Completes.Requery

The two buttons trigger code to "move" the items. Here's the code for the top button that's meant to "move" the document from the Incompletes to the Completes listbox (i.e. by setting the doc's "Complete" value to Yes and then requerying the listboxes). The indicated lines are where things go wrong:

Sub Move_To_Complete_Click()
  docID = Incompletes.Column(0, Incompletes.ListIndex)

  Set Tbl = CurrentDb.TableRefs("DocTbl")
  Set db = CurrentDb

  db.Execute = "UPDATE DocTbl " & _
  " SET DocTbl.Complete = Yes " & _
  " WHERE DocTbl.DocID = " & docID

---->Me.Incompletes.Requery
---->Me.Completes.Requery
End Sub

In other words, the UPDATE query works (I see the updated data in the table), but when I try to requery both ListBoxes to refresh them, all the items disappear from both!. Further, if I select another Award from the ComboBox, and then reselect the same Award, the lists populate correctly again, just by running the same .Requery commands!

I've tried Me.Refresh, Me.Recalc. I've also tried setting the RowSource again. I wonder if it's requerying too early; I've put breakpoints and DoEvents, so I don't think it's a timing thing, but maybe more of a code-needs-to-stop-running-for-requery-to-work. Which makes no sense because requery is code.

I know there are other ways to do this (I've tried and failed with those), but I'd really like to know why THIS way isn't working.

Thanks in advance!!

Upvotes: 0

Views: 1355

Answers (1)

Erik A
Erik A

Reputation: 32642

You're using the .Text property, and like DataWriter suggests, you shouldn't.

The .Text property is only available when a field has focus (see MSDN).

Because you're calling it in the OnClick event for the Move_To_Complete button, that button has focus, and so the .Text property is unavailable.

Two workarounds:

  1. Use Award_CBox.SetFocus before you're requerying
  2. Use Forms!Select_Doc_To_Code!Award_CBox.Value to use the value property. Note that this assumes the bound column is the same as the column being displayed.

Upvotes: 2

Related Questions