VBA code works in Access 2007 but doesn't work in Access 2010. Any ideas?

My wife wrote the following code and it used to work fine for her when her organization used Access 2007. They just updated to Access 2010 and it no longer works. I am not familiar with Access at all but I suggested I'd present it to Stack to see if you guys can see anything straight off that won't work in Access 2010. Thanks in advance for any insights.

Private Sub Originating_Zone_AfterUpdate()

Dim EscortDB As DAO.Database

Dim rstBldgs As DAO.Recordset

Set EscortDB = CurrentDb()
Set rstBldgs = EscortDB.OpenRecordset("SELECT BuildingName FROM" & _
" ZoneBldgLookup WHERE ZoneLocation = '" & _
Forms!DateID!EscortIDSubform.Form.[Originating Zone] & _
"' ORDER BY BuildingName", [dbOpenDynaset])


rstBldgs.MoveLast

rstBldgs.MoveFirst

Do Until rstBldgs.EOF
Forms!DateID!EscortIDSubform.Form.[Pick Up Location].AddItem rstBldgs!BuildingName
rstBldgs.MoveNext
Loop

rstBldgs.Close


End Sub

Update: She got it working using the following code. Thanks for your help!

Private Sub Originating_Zone_AfterUpdate()

Dim sBuildList As String

sBuildList = ("SELECT B开发者_开发百科uildingName FROM" & _
" ZoneBldgLookup WHERE ZoneLocation = '" & _
Forms!DateID!EscortIDSubform.Form.[Originating Zone] & _
"' ORDER BY BuildingName")

Forms!DateID!EscortIDSubform.Form.[Pick Up Location].RowSource = sBuildList
Forms!DateID!EscortIDSubform.Form.[Pick Up Location].Requery

End Sub

---

It's terrible code. Populating a dropdown list or listbox by walking a recordset and .AddItem is terribly inefficient. The whole thing can be done without code by simply assigning a SQL string to the Rowsource property of the combobox/listbox.

Now, clearly, the list changes based on the choices in the control to which this AfterUpdate event is attached, but all that means is that you assign the Rowsource in this event. Probably, all the above code can be replace with this:

  Forms!DateID!EscortIDSubform.Form.[Pick Up Location].Rowsource = "SELECT BuildingName FROM" & _
     " ZoneBldgLookup WHERE ZoneLocation = '" & _
     Forms!DateID!EscortIDSubform.Form.[Originating Zone] & _
     "' ORDER BY BuildingName"

I can't say what's wrong with the code not working (I suspect there's a sandbox mode/macro security issue going on), but it's way more lines of code than are needed.

---

In addition to @David-W-Fenton's suggestions, I think you should use a string variable to hold the SELECT statement. Then you can Debug.Print it to the Immediate Window, copy it to a new query (in SQL View), and make sure it actually returns rows.

Dim strSql As String
strSql = "SELECT BuildingName FROM" & _
    " ZoneBldgLookup WHERE ZoneLocation = '" & _
    Forms!DateID!EscortIDSubform.Form.[Originating Zone] & _
    "' ORDER BY BuildingName"
Debug.Print strSql
Forms!DateID!EscortIDSubform.Form.[Pick Up Location].Rowsource = strSql

Also if this is code in the module of a form named DateID, you can replace Forms!DateID with the keyword Me (which is shorthand for "this form" ... the form which contains the code you're running). That's not dramatically shorter, but Me will not need to be changed if the form is ever re-named. Still not a big deal ... just one less detail you won't have to fiddle with down the road.