Access表单CreateCruise_Click事件中Me.Refresh报错：CruiseID需输入值

我有一个包含巡航详情的表单Frm_tbl_Cruise_Form，其数据源为dbo_tbl_CruiseInformation表。创建新巡航时，表单接收数据后添加新记录并生成主键字段CruiseID。表单上的CreateCruise按钮执行如下VBA代码：

Private Sub CreateCruise_Click()
Dim db As Database
Dim crs As Recordset
Dim shp As Recordset
Dim legstring As String
Dim vessel As String
Dim year As String
Dim crsnum As String

Set db = CurrentDb()
Set crs = db.OpenRecordset("dbo_tbl_CruiseInformation", dbOpenDynaset)
Set shp = db.OpenRecordset("dbo_tlu_NoaaShips", dbOpenDynaset)

If IsNull(Leg) Then
    legstring = ""
Else
    legstring = "L" & Format(Leg, "0")
End If

shp.FindFirst ("[ID] = " & Vessel_Used)
vessel = shp!ShipCode
year = Format(Me!Date_Start, "YY")
crsnum = Format(Me!VesselNumber, "00")

crs.AddNew
crs!CruiseID = vessel & year & crsnum & legstring
crs!Vessel_Used = Me!Vessel_Used
crs!VesselNumber = crsnum
crs!Date_Start = Me!Date_Start
crs.Update

Me.Refresh

End Sub

但执行Me.Refresh时出现错误提示：“必须在'dbo_tbl_CruiseInformation.CruiseID'字段中输入值”。明明已通过记录集设置了CruiseID，若将Me.Refresh替换为Me!CruiseID = vessel & year & crsnum & legstring则能正常显示该值，请问为何会出现此问题？

• 核心原因：你通过独立的Recordset对象crs向表中插入了新记录，但此时表单本身的绑定记录集还停留在一条空白的新建记录上（Access表单默认会自动生成一条空白新记录等待用户输入）。执行Me.Refresh时，表单会尝试保存这条未填充CruiseID的空白记录，而CruiseID是主键不允许为空，因此触发错误。
• 替换赋值正常工作的逻辑：当执行Me!CruiseID = ...时，是直接给表单当前绑定的空白记录的CruiseID字段赋值，此时这条记录满足主键非空的要求，保存时不会出错，同时你之前通过crs插入的记录也会在刷新后显示在表单中。
• 两种优化解决方法：
  1. 直接操作表单绑定的记录集，避免独立记录集与表单记录集不一致的问题：

     Private Sub CreateCruise_Click()
     Dim shp As Recordset
     Dim legstring As String
     Dim vessel As String
     Dim year As String
     Dim crsnum As String
     
     Set shp = CurrentDb().OpenRecordset("dbo_tlu_NoaaShips", dbOpenDynaset)
     
     If IsNull(Leg) Then
         legstring = ""
     Else
         legstring = "L" & Format(Leg, "0")
     End If
     
     shp.FindFirst ("[ID] = " & Vessel_Used)
     vessel = shp!ShipCode
     year = Format(Me!Date_Start, "YY")
     crsnum = Format(Me!VesselNumber, "00")
     
     ' 直接操作表单绑定的记录集
     Me.Recordset.AddNew
     Me.Recordset!CruiseID = vessel & year & crsnum & legstring
     Me.Recordset!Vessel_Used = Me!Vessel_Used
     Me.Recordset!VesselNumber = crsnum
     Me.Recordset!Date_Start = Me!Date_Start
     Me.Recordset.Update
     ' 重新查询表单记录集以显示新记录
     Me.Requery
     End Sub
     

  2. 若坚持使用独立记录集，需先取消表单当前的空白新建记录，再执行刷新：

     ' 在Me.Refresh前添加以下代码
     If Me.NewRecord Then Me.Undo
     Me.Refresh
     

内容的提问来源于stack exchange，提问作者Susan Gottfried