vba 访问运行时错误 - '-2147352567 (80020009)'：子表单
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/41718872/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Access Run time error - '-2147352567 (80020009)': subform
提问by Amy
I've created a form where I can use a subform to load data from a query into textboxes so I can edit the data one at a time and update the database. This usually works fine except I have a run time error that randomly appears now and then - '-2147352567 (80020009)': The value you entered isn't valid for this field.'
我创建了一个表单，我可以在其中使用子表单将查询中的数据加载到文本框中，这样我就可以一次编辑一个数据并更新数据库。这通常工作正常，除非我有一个随机出现的运行时错误 - '-2147352567 (80020009)'：您输入的值对该字段无效。
If I stop running it and try running it again it usually works fine until the same run-time error appears. It's very inconsistent.
如果我停止运行它并再次尝试运行它，它通常可以正常工作，直到出现相同的运行时错误。这是非常不一致的。
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
The error is on this line
错误在这一行
Me.txt_AgencyID = .Fields("Agency ID")
I would appreciate any help with this, thank you :)
我很感激这方面的任何帮助，谢谢:)
回答by Gustav
Try to exclude Nullvalues:
尝试排除Null值：
If Not IsNull(.Fields("Agency ID").Value) Then
Me.txt_AgencyID.Value = .Fields("Agency ID").Value
End If
You may also try using the RecordsetClone:
您也可以尝试使用 RecordsetClone：
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
回答by user2516521
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
'You can it try. That worked for me!
'你可以试试。那对我有用！
