Solved

Bookmark Help

Posted on 2013-11-14
3
221 Views
Last Modified: 2013-11-15
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
Comment
Question by:hbatt
  • 2
3 Comments
 
LVL 61

Expert Comment

by:mbizup
ID: 39649992
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
 
LVL 61

Accepted Solution

by:
mbizup earned 500 total points
ID: 39649999
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
 

Author Closing Comment

by:hbatt
ID: 39651572
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

My experience with Windows 10 over a one year period and suggestions for smooth operation
This article descibes how to create a connection between Excel and SAP and how to move data from Excel to SAP or the other way around.
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
Learn how to create and modify your own paragraph styles in Microsoft Word. This can be helpful when wanting to make consistently referenced styles throughout a document or template.

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question