访问运行时错误 - "-2147352567 (80020009)"：子窗体

Question

我创建了一个表单，可以在其中使用子窗体将数据从查询加载到文本框中，以便一次编辑一个数据并更新数据库。这通常工作正常，除了我有一个运行时错误，不时随机出现 - '-2147352567 （80020009）'：您输入的值对此字段无效。

如果我停止运行它并尝试再次运行它，它通常可以正常工作，直到出现相同的运行时错误。这是非常不一致的。

    Private Sub btn_SelectAgency_Click()
If Not (Me.qryAgencyWithoutMileagesub.Form.Recordset.EOF And Me.qryAgencyWithoutMileagesub.Form.Recordset.BOF) Then
'get data to textbox control
    With Me.qryAgencyWithoutMileagesub.Form.Recordset
        Me.txt_AgencyID = .Fields("Agency ID")    
        Me.txt_AgencyName = .Fields("Agency Name")
        Me.txt_Address1 = .Fields("Address 1")
        Me.txt_Address2 = .Fields("Address 2")
        Me.txt_City = .Fields("City")
        Me.txt_Postcode = .Fields("Postcode")
        Me.txt_AgencyMileage = .Fields("Mileage")
        Me.txt_AgencyID.Tag = .Fields("Agency ID")
    End With
End If
Me.txt_AgencyMileage = ""
End Sub

Private Sub btn_Update_Click()
If Me.txt_AgencyMileage = "" Then
    MsgBox "No mileage added, add now"
    Cancel = True
Else
    CurrentDb.Execute "UPDATE EstateAgent_tbl SET EstateAgent_AgentMileage = '" & Me.txt_AgencyMileage & "' where EstateAgent_AgentID=" & Me.txt_AgencyID.Tag
    Me.txt_AgencyID = ""
    Me.txt_AgencyName = ""
    Me.txt_Address1 = ""
    Me.txt_Address2 = ""
    Me.txt_City = ""
    Me.txt_Postcode = ""
    Me.txt_AgencyMileage = ""
End If
qryAgencyWithoutMileagesub.Form.Requery
    If Me.qryAgencyWithoutMileagesub.Form.Recordset.RecordCount = 0 Then
        MsgBox "No agencies without mileage"
        DoCmd.Close
    End If
End Sub

错误在此行

Me.txt_AgencyID = .Fields("Agency ID")          

我将不胜感激对此的任何帮助，谢谢:)

Answer 1

Dim rs As DAO.Recordset    
Set rs = Me.qryAgencyWithoutMileagesub.Form.RecordsetClone
    'get data to textbox control
With rs
    If .RecordCount > 0 Then
        Me.txt_AgencyID = .Fields("Agency ID")    
        Me.txt_AgencyName = .Fields("Agency Name")
        Me.txt_Address1 = .Fields("Address 1")
        Me.txt_Address2 = .Fields("Address 2")
        Me.txt_City = .Fields("City")
        Me.txt_Postcode = .Fields("Postcode")
        Me.txt_AgencyMileage = .Fields("Mileage")
        Me.txt_AgencyID.Tag = .Fields("Agency ID")
    End If
End With
Set rs = Nothing

DoCmd.RefreshRecord 
DoCmd.RunCommand acCmdUndo

"你可以试试。这对我有用！

Answer 2

尝试排除空值：

If Not IsNull(.Fields("Agency ID").Value) Then        
    Me.txt_AgencyID.Value = .Fields("Agency ID").Value
End If

您也可以尝试使用记录集克隆：

Dim rs As DAO.Recordset
Set rs = Me.qryAgencyWithoutMileagesub.Form.RecordsetClone
        'get data to textbox control
    With rs
        If .RecordCount > 0 Then
            Me.txt_AgencyID = .Fields("Agency ID")    
            Me.txt_AgencyName = .Fields("Agency Name")
            Me.txt_Address1 = .Fields("Address 1")
            Me.txt_Address2 = .Fields("Address 2")
            Me.txt_City = .Fields("City")
            Me.txt_Postcode = .Fields("Postcode")
            Me.txt_AgencyMileage = .Fields("Mileage")
            Me.txt_AgencyID.Tag = .Fields("Agency ID")
        End If
    End With
Set rs = Nothing