LinkMasterFields设置致rs.Seek参数错误，DAO更新Access记录求助

原代码的核心问题 #

• 使用dbAppendOnly参数打开记录集时，仅支持新增记录，无法编辑现有记录，也不能执行Seek操作
• rs.Seek用法错误：该方法仅适用于表类型记录集（dbOpenTable），且必须先设置记录集的Index属性，语法为rs.Seek "<>", 键值，而非直接拼接条件字符串
• 逻辑混乱：先执行rs.Edit再移动记录指针，导致编辑的并非目标记录；刚追加的记录无需额外查找，直接操作当前记录即可

修正后的DAO实现代码 #

如果坚持使用DAO，正确的实现流程分为两种场景：

' 声明变量
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim frm As Access.Form

' 赋值变量
Set frm = Forms![NICKELL JOBTKT 2B]
Set db = CurrentDb

' 打开可读写的动态集记录集（移除dbAppendOnly限制）
Set rs = db.OpenRecordset("TO BE SCHEDULED", dbOpenDynaset)

' --- 场景1：追加新记录并填充字段 ---
rs.AddNew
rs!JOB = Me.JobNumber ' 先写入关联的Job编号
rs!FOOTAGE = frm("Order QTY")
rs!RIP_QTY = frm("RIP_FTG")
rs!["FTG/MLDR"] = frm("MLDR FTG")
rs!P = frm("P")
rs!SCRAP = frm("SCRAP%")
rs!["FT/FCTR"] = frm("BD FTG FACTOR")

' 处理FTGE字段逻辑
If frm("SPECIES") = "MDF" Then
    rs!FTGE = "NMC"
Else
    rs!FTGE = frm("Field139")
End If
rs.Update ' 保存新记录

' --- 场景2：更新已存在的对应Job记录（替换上面的AddNew代码块）---
' rs.FindFirst "JOB = " & Me.JobNumber
' If Not rs.NoMatch Then
'     rs.Edit
'     rs!FOOTAGE = frm("Order QTY")
'     rs!RIP_QTY = frm("RIP_FTG")
'     rs!["FTG/MLDR"] = frm("MLDR FTG")
'     rs!P = frm("P")
'     rs!SCRAP = frm("SCRAP%")
'     rs!["FT/FCTR"] = frm("BD FTG FACTOR")
'     
'     If frm("SPECIES") = "MDF" Then
'         rs!FTGE = "NMC"
'     Else
'         rs!FTGE = frm("Field139")
'     End If
'     rs.Update
' Else
'     MsgBox "未找到对应Job的记录"
' End If

' 清理对象
rs.Close
Set rs = Nothing
Set db = Nothing

MsgBox "操作完成"

更简便的SQL实现方法 #

表单控件可直接在SQL语句中引用，无需担心计算字段的问题，直接用INSERT或UPDATE语句即可：

1. 追加新记录 #

Dim sqlStr As String

' 注意：若控件为文本类型，需用单引号包裹值（如'" & frm("控件名") & "'），以下示例假设为数值类型
sqlStr = "INSERT INTO [TO BE SCHEDULED] (JOB, FOOTAGE, RIP_QTY, [FTG/MLDR], P, SCRAP, [FT/FCTR], FTGE) " & _
         "VALUES (" & Me.JobNumber & ", " & frm("Order QTY") & ", " & frm("RIP_FTG") & ", " & _
         frm("MLDR FTG") & ", " & frm("P") & ", " & frm("SCRAP%") & ", " & frm("BD FTG FACTOR") & ", " & _
         IIf(frm("SPECIES") = "MDF", "'NMC'", frm("Field139")) & ")"

CurrentDb.Execute sqlStr, dbFailOnError
MsgBox "记录已成功追加"

2. 更新已存在的记录 #

Dim sqlStr As String

sqlStr = "UPDATE [TO BE SCHEDULED] SET " & _
         "FOOTAGE = " & frm("Order QTY") & ", " & _
         "RIP_QTY = " & frm("RIP_FTG") & ", " & _
         "[FTG/MLDR] = " & frm("MLDR FTG") & ", " & _
         "P = " & frm("P") & ", " & _
         "SCRAP = " & frm("SCRAP%") & ", " & _
         "[FT/FCTR] = " & frm("BD FTG FACTOR") & ", " & _
         "FTGE = " & IIf(frm("SPECIES") = "MDF", "'NMC'", frm("Field139")) & " " & _
         "WHERE JOB = " & Me.JobNumber

CurrentDb.Execute sqlStr, dbFailOnError
MsgBox "记录已成功更新"

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