[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Bookmark Help

Posted on 2013-11-14
3
Medium Priority
?
226 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:Harry Batt
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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:Harry Batt
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

Enroll in October's Free Course of the Month

Do you work with and analyze data? Enroll in October's Course of the Month for 7+ hours of SQL training, allowing you to quickly and efficiently store or retrieve data. It's free for Premium Members, Team Accounts, and Qualified Experts!

Question has a verified solution.

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

This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
The viewer will learn how to use the =DISCRINV command to create a discrete random variable, use this command to model a set of probabilities and outcomes in a Monte Carlo simulation, and learn how to find the standard deviation of a set of probabil…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

650 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