1

I've inherited some Access VBA code and there are controls on a form (such as a listbox named lstOrderID, mentioned below) which have no RowSource property set (an empty string). I look in the code and find statements like this in various places:

  • Forms!frm_Customer.lstOrderID = rstCust!OrderID ' set from a record set
  • Forms!frm_Customer.lstOrderID.Requery
  • Me.lstOrderID = Me.lstOrderID.ItemData(0) ' set to first item in self

But nowhere in the code is lstOrderID.RowSource being set.

How can Requery be called on a listbox that has no RowSource?

How can a listbox be set to a single value (rstCust!OrderID) from a record set, unless this is a list of values (although the debugger shows an integer in lstOrderID.Value)?

Here is more code:

Dim rstCust As Recordset
Set db = CurrentDb
Set rstCust = db.OpenRecordset("SELECT * FROM Orders WHERE CustID=" & ID & _
                "AND Datetaken =Date() " & _
                "AND VendorID='" & Forms!frm_Customer.cboVendorID & "'")

Forms!frm_Customer.lstOrderID = rstCust!OrderID

rstCust.Close
db.Close

Another section:

Dim rstCust As Recordset
Dim blStatus As Boolean
Dim strSql As String
Set db = CurrentDb      

strSql = "SELECT Orders.OrderID " & _
            "FROM Orders " & _
        "WHERE (((Orders.DateTaken)=#" & Date & "#) " & _
        "AND ((Orders.VendorID)='" & Forms!frm_Customer.cboVendorID & "') " & _
        "AND ((Orders.CustID)=" & ID & "));"

Set rstCust = db.OpenRecordset(strSql)
Forms!frm_Customer.lstOrderID = rstCust!OrderID

Forms!frm_Customer.lstOrderID.Requery
Forms!frm_Customer.lstOrderID = rstCust!OrderID

rstCust.Close
db.Close

Also this:

Me.lstOrderID.Requery
Me.lstOrderID = Me.lstOrderID.ItemData(0)
udog
  • 1,490
  • 2
  • 17
  • 30
  • Seems to me there is a form - Frm_customer that has a an object, probably a list, that has the name lstorderID in it ? I guess i don't know what your asking. – Holmes IV Jun 29 '15 at 16:16
  • That's right, form frm_Customer has listbox lstOrderID – udog Jun 29 '15 at 16:17
  • Is there any options showing in the listbox at all? Or is it just simply a blank square? – Newd Jun 29 '15 at 16:25
  • Blank, even after the assignments made. – udog Jun 29 '15 at 16:27
  • can you show the whole section of code? not really enough code to go on. – Gene Jun 29 '15 at 16:42
  • Added the code where the assignments happen. – udog Jun 29 '15 at 16:46
  • 1
    What do you know about the original author? Those `db.Close` statements suggest someone who doesn't understand Access basics very well. You *can* `Requery` a list box which doesn't have a `RowSource` ... meaning Access won't throw a compile or run-time error ... but I don't see why that would be useful. You can also set that list box's `Value`, but without a `RowSource` I don't see why a list box is useful --- you could use a text box instead. OTOH, perhaps those choices make sense for reasons we can't see. Can you share a copy of the database? – HansUp Jun 29 '15 at 16:54
  • The client tells me the author was learning (from scratch) on the job. – udog Jun 29 '15 at 16:58
  • Aha! Well, on the bright side, it should require more billable hours to clean up the mess. :-) – HansUp Jun 29 '15 at 17:00

0 Answers0