求教：循环更新记录问题

hbhscwm

在下面库中的KD窗体，保存时更新BMB的SJBID字段值，代码如下：

Private Sub bc_Click()
Dim SQL As String
Dim rst As ADODB.Recordset
Dim i As Integer
Dim j As Long

Set rst = New ADODB.Recordset
SQL = "select sjbid, kdid, rq, sj, xx_id, rs from sjb where kdid=forms!kd!kdid ORDER BY rq, sj"

rst.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable

If rst.RecordCount > 0 Then
    rst.MoveFirst

    For i = 1 To rst.RecordCount

    j = DMin("bmh", "bmb", "xx_id='" & rst!xx_id & "' and bmh is null")
'    CodeDb.Execute ""
    CodeDb.Execute "UPDATE bmb SET bmb.sjbID = '" & rst!sjbid & "'" & _
        " WHERE bmb.xx_id='" & rst!xx_id & "'and bmb.sjbID Is Null and" & _
        " bmb.bmh beteen '" & j & "'and '" & j & "'+'" & rst!Rs & "'-1"

    rst.MoveNext

    Next i
   
End If
   
rst.Close
Set rst = Nothing

End Sub


运行时提示：FROM子句错误

此代码如何写？恳请赐教

fan0217

rst.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdTable

使用的是SQL语句，就不能使用参数adCmdTable了

hbhscwm

感谢版主指教

andymark

SQL = "select sjbid, kdid, rq, sj, xx_id, rs from sjb where kdid=" & Forms!kd!kdID & " ORDER BY rq, sj"

rst.Open SQL, CurrentProject.Connection, adOpenKeyset, adLockOptimistic

只更正上面的代码

其他的不明你的用意,不知从何更改