Access 2007: DLookup for Autofill not returning values

Question

Developing a database for visually impaired persons scheduling donation pickups (uses an audio reader for fields). Have a simple table " Truck_Routes" with customer ID, street address, suite, company, city, state, zip, contact name, contact phone number, and several donation information fields.

Trying to develop a form that auto fills: suite, company, city, state, zip, contact name, and contact phone number based on an existing combo box: cboAddress ....(street address). Spent hours on the web emulating other examples, but can't get vba to work.

Street Address - combo box with "After_Update event procedure"

Private Sub cboAddress_AfterUpdate()
  PopulateFields
End Sub

Private Sub PopulateFields()
  Me.Suite = DLookup("Suite", "Truck_Routes", "Street Address=" & Me.[cboAddress & "'"])
  Me.Company = DLookup("Company", "Truck_Routes", "Street Address=" & Me.[cboAddress & "'"])
  Me.City = DLookup("City ", "Truck_Routes", "Street Address=" & Me.[cboAddress & "'"])
  Me.State = DLookup("State ", "Truck_Routes", "Street Address=" & Me.[cboAddress & "'"])
  Me.zip = DLookup("zip ", "Truck_Routes", "Street Address=" & Me.[cboAddress & "'"])
  Me.ContactName = DLookup("ContactName ", "Truck_Routes", "Street Address=" & Me.[cboAddress & "'"])
  Me.ContactPHone = DLookup("ContactPhone ", "Truck_Routes", "Street Address=" & Me.[cboAddress & "'"])
End Sub

Fields in form are navigated using "tab" key, audio reader reads the field. Default value for each field is the name of the field so it could be read to the operator. Any thoughts/recommendations?

PaulFrancis PaulFrancis · Accepted Answer · 2015-02-04T11:15:12

You have your DLookup a bit off, the square brackets are outside the quotes. Also they are supposed to be enclosed inside single quotes, as they are Text types. I have fixed them for you.

Private Sub cboAddress_AfterUpdate()
    PopulateFields
End Sub

Private Sub PopulateFields()
    Me.Suite = DLookup("Suite", "Truck_Routes", "[Street Address] = '" & Me.cboAddress & "'")
    Me.Company = DLookup("Company", "Truck_Routes", "[Street Address] = '" & Me.cboAddress & "'")
    Me.City = DLookup("City ", "Truck_Routes", "[Street Address] = '" & Me.cboAddress & "'")
    Me.State = DLookup("State ", "Truck_Routes", "[Street Address] = '" & Me.cboAddress & "'")
    Me.zip = DLookup("zip ", "Truck_Routes", "[Street Address] = '" & Me.cboAddress & "'")
    Me.ContactName = DLookup("ContactName ", "Truck_Routes", "[Street Address] = '" & Me.cboAddress & "'")
    Me.ContactPHone = DLookup("ContactPhone ", "Truck_Routes", "[Street Address] = '" & Me.cboAddress & "'")
End Sub

On a side note, the Domain function is rather expensive piece of operation. It is a simplified SQL. So every time you use a DLookup, you are performing a READ to the table in the above example you are querying the table 7 times, in one single AfterUpdate. This could be massively reduced if you were to use a RecordSet object.

Something like,

Private Sub cboAddress_AfterUpdate()
    PopulateFields
End Sub

Private Sub PopulateFields()
    Dim rsObj As DAO.Recordset

    Set rsObj = CurrentDB.OpenRecordset("SELECT Suite, Company, City, State, zip, ContactName, ContactPhone " & _
                                    "FROM Truck_Routes WHERE [Street Address] = '" & Me.cboAddress & "'")

    If Not rsObj.EOF Then
        Me.Suite = rsObj.Fields("Suite")
        Me.Company = rsObj.Fields("Company")
        Me.City = rsObj.Fields("City")
        Me.State = rsObj.Fields("State")
        Me.zip = rsObj.Fields("zip")
        Me.ContactName = rsObj.Fields("ContactName")
        Me.ContactPHone = rsObj.Fields("ContactPhone")
    Else
        MsgBox "No Information matched."
    End If

    Set rsObj = Nothing
End Sub

There are also other advantages like,

Checking if the Recordset returns more than one row using the RecordCount property of the RecordSet object.
This not only reduces the hit on the Database, this code also cleans up after itself unlike a Domain function.

Hope this helps !

Access 2007: DLookup for Autofill not returning values

1 Answers