[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 227
  • Last Modified:

Bookmark Help

I have a command button that sets a value to "TRUE" and which works fine with the exception that the form returns to the first record. How can I get the form to stay on the same record once the UPDATE SQL runs?

Thanks for your help.

If MsgBox("Are you sure you want to assign " & Me.lstCName & " as chair of the " & [lstComName] & " committee?", vbYesNo + vbQuestion, strAppName) = vbYes Then
        
        DoCmd.RunSQL "UPDATE tblCommitteeDetail SET tblCommitteeDetail.Chair = True WHERE [CommitteeID] = " & Me.CommitteeID.Value
        fDisplayPopup "Success", [lstCName] & " has been added as  chair of the " & [lstComName] & " committee.", 2
      
End If
End Sub
       

Open in new window

0
Harry Batt
Asked:
Harry Batt
  • 2
1 Solution
 
mbizupCommented:
Try this:

dim lngID as long
If MsgBox("Are you sure you want to assign " & Me.lstCName & " as chair of the " & [lstComName] & " committee?", vbYesNo + vbQuestion, strAppName) = vbYes Then
         lngID = Me.ID '*** Change this to the actual name of your PK or autonumber field
        DoCmd.RunSQL "UPDATE tblCommitteeDetail SET tblCommitteeDetail.Chair = True WHERE [CommitteeID] = " & Me.CommitteeID.Value
        fDisplayPopup "Success", [lstCName] & " has been added as  chair of the " & [lstComName] & " committee.", 2
       Me.RecordsetClone.FindFirst  "ID = " & lngID
       Me.Bookmark = Me.RecorsetClone.Bookmark
      
End If
End Sub

Open in new window

0
 
mbizupCommented:
This is a little cleaner, and handles no-match conditions:


dim lngID as long
dim rs as dao.recordset

If MsgBox("Are you sure you want to assign " & Me.lstCName & " as chair of the " & [lstComName] & " committee?", vbYesNo + vbQuestion, strAppName) = vbYes Then
         lngID = Me.ID '*** Change this to the actual name of your PK or autonumber field
        DoCmd.RunSQL "UPDATE tblCommitteeDetail SET tblCommitteeDetail.Chair = True WHERE [CommitteeID] = " & Me.CommitteeID.Value
        fDisplayPopup "Success", [lstCName] & " has been added as  chair of the " & [lstComName] & " committee.", 2

       set rs = Me.RecordsetClone
       rs.FindFirst  "ID = " & lngID
       if rs.NoMatch = False then Me.Bookmark =rs.Bookmark
       set rs = nothing
      
End If
End Sub

Open in new window

0
 
Harry BattDirector of DevelopmentAuthor Commented:
Thanks for your quick answer and I apologize for my slow response. I was finessing the code a bit so it would also remove someone as chair based on the caption of the command button.

This works perfectly!
0

Featured Post

Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now