代码之家 › 专栏 › 技术社区 › Michael

Access 2007:DLookup for Autofill不返回值

ms-access-2007 vba ms-access

Michael · 技术社区 · 9 年前

为安排募捐的视障人士开发数据库(使用现场音频阅读器)。有一个简单的表“Truck_Routes”,其中包含客户ID、街道地址、套房、公司、城市、州、邮政编码、联系人姓名、联系电话号码和几个捐赠信息字段。

试图开发一个自动填写的表单:套房、公司、城市、州、邮编、联系人姓名和基于现有组合框的联系电话号码:cboAddress。。。。(街道地址)。花了几个小时在网上模仿其他例子,但无法让vba发挥作用。

街道地址-带有“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

表单中的字段使用“tab”键导航,音频读取器读取字段。每个字段的默认值是字段的名称,以便操作员可以读取该字段。有什么想法/建议吗?

1 回复 | 直到 9 年前

PaulFrancis 9 年前

您的DLookup稍有偏差,方括号在引号之外。此外,它们应该被括在单引号内,因为它们是文本类型。我已经为你修好了。

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

另一方面,Domain函数是一项相当昂贵的操作。它是一个简化的SQL。因此,每次使用DLookup时,都会对表执行READ。在上面的示例中,您会在一次AfterUpdate中查询表7次。如果使用RecordSet对象,这可能会大大减少。

比如,

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

还有其他优点,

使用 记录计数 属性。
这不仅减少了对数据库的影响,而且与域函数不同,这段代码还会自行清理。

希望这有帮助!