的MS Access如何更新当前行，移动到下一个记录，而不是第一 [英] MS Access how to Update current row, move to next record, not first

问题描述

我有一个更新的当前记录的值，这将使该记录不再一次重新查询的形式显示在页脚中一个命令按钮的连续形式。我希望用户能够点击该按钮，一旦记录被更新，移动到下一条记录，不是第一次因为是默认的行为。我有code，我倒觉得应该工作，但没有，它一直可以追溯到第一个记录在表格上。

 私人小组cmdCloseReq_Click（）

昏暗请求ID作为字符串
请求ID = Me.txtReqID
昏暗首先作为记录
昏暗strBookmark作为整数

设置RST = Me.RecordsetClone
rst.MoveNext
如果没有rst.EOF然后如果不是最终的文件，
  strBookmark = rst.Bookmark'......救下记录的书签
  DIM在cmd作为新ADODB.Command
  用CMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText =spUpdateLOG_ReqCompleteDate
    .Parameters（@请求ID）=请求ID
    。执行
  结束与
Me.Requery
Me.Bookmark = strBookmark

结束如果
第一个设置=什么

结束小组
 

OK，我发现根据刘若英的帖子的解决方案。我抓住下一个记录的主键，做一个更新则requesry后，我找到下一个记录，并设置书签来表示。这里是code：

 私人小组cmdCloseReq_Click（）

昏暗请求ID作为字符串
请求ID = Me.txtReqID
昏暗的第一个作为新ADODB.Recordset
昏暗strBookmark作为字符串

设置RST = Me.RecordsetClone

随着RST
 .Find[请求ID] ='＆放大器;请求ID和放大器; '
 .MoveNext
 strBookmark = rst.Fields（0）

结束与

如果没有rst.EOF然后如果不是最终的文件，
      ......救下记录的书签

  DIM在cmd作为新ADODB.Command

  用CMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText =spUpdateLOG_ReqCompleteDate
    .Parameters（@请求ID）=请求ID
    。执行
  结束与'...删除记录

  Me.Requery

 设置RST = Me.RecordsetClone
 随着RST
      .Find[REQID] =放大器; strBookmark
    Me.Bookmark = .Bookmark

结束与

其他
  用CMD
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText =spUpdateLOG_ReqCompleteDate
    .Parameters（@请求ID）=请求ID
    。执行
  结束与'...删除记录
Me.Requery

结束如果
第一个设置=什么
 

解决方案

我记得，书签重新查询后失效。如果你有一个主键，你可以更好地抓住一个，并重新查询后，当前记录移动到pviously获得主键$ P $

I have a continuous form with a command button in the footer that updates the current record with a value that will make the record no longer show in the form once requeried. I want the user to be able to click the button and once the record is updated, move to the next record, not the first as is the default behaviour. I have code that I would think should work but doesn't, it keeps going back to the first record on the form.

Private Sub cmdCloseReq_Click()

Dim ReqID As String
ReqID = Me.txtReqID
Dim rst As Recordset
Dim strBookmark As Integer

Set rst = Me.RecordsetClone
rst.MoveNext
If Not rst.EOF Then                 ' if not end-of-file
  strBookmark = rst.Bookmark      ' ...save the next record's bookmark
  Dim cmd As New ADODB.Command
  With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "spUpdateLOG_ReqCompleteDate"
    .Parameters("@ReqID") = ReqID
    .Execute
  End With                 
Me.Requery
Me.Bookmark = strBookmark

End If
Set rst = Nothing   

End Sub

OK, I found a solution based on rene's post. I grab the next records primary key, do an update then after the requesry I find the next record and set the bookmark to that. Here is the code:

Private Sub cmdCloseReq_Click()

Dim ReqID As String
ReqID = Me.txtReqID
Dim rst As New ADODB.Recordset
Dim strBookmark As String

Set rst = Me.RecordsetClone

With rst
 .Find "[ReqID] = '" & ReqID & "'"
 .MoveNext
 strBookmark = rst.Fields(0)

End With

If Not rst.EOF Then                 ' if not end-of-file
      ' ...save the next record's bookmark

  Dim cmd As New ADODB.Command

  With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "spUpdateLOG_ReqCompleteDate"
    .Parameters("@ReqID") = ReqID
    .Execute
  End With                 ' ...delete the record

  Me.Requery

 Set rst = Me.RecordsetClone
 With rst
      .Find "[reqID]= " & strBookmark
    Me.Bookmark = .Bookmark

End With

Else
  With cmd
    .ActiveConnection = CurrentProject.Connection
    .CommandType = adCmdStoredProc
    .CommandText = "spUpdateLOG_ReqCompleteDate"
    .Parameters("@ReqID") = ReqID
    .Execute
  End With                 ' ...delete the record
Me.Requery

End If
Set rst = Nothing 

解决方案

I recall that Bookmarks are invalidated after a Requery. If you have a primary key you can better grab that one and after requery move the current record to the previously obtained primary key

这篇关于的MS Access如何更新当前行，移动到下一个记录，而不是第一的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！