Results 1 to 7 of 7
  1. #1
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169

    Update new record when leaving form

    I have two forms that a user can enter data for the same records. One form is a summary form with just minimal information and on this form I have a control button that takes the user to another form that allows more details to be entered for that record. I have the following codes that can take the user back and forth from the summary form to the detail form. These codes are working well if the user edits and existing record...that is, when the record is updated on one form and the user switches to the other form, the user can see their updates. I am having a problem with new records, however. I would like the user to be able to enter some data in the summary form for a new record and then be able to go to the detail form to add more data. Is there a way to get the data to update in the summary form on a new record so that when the user goes to the detail form the new record shows up. Here are the codes I am using to go from one form to the other. frmMainTEST = the summary form and frmMain = the detail form.
    Code:
    Private Sub Command206_Click()
    On Error GoTo Err_Command206_Click
        Dim stDocName As String
        Dim stLinkCriteria As String
        stDocName = "frmMain"
     
        stLinkCriteria = "[QuestID]=" & Me![QuestID]
        DoCmd.OpenForm stDocName, , , stLinkCriteria
        DoCmd.Close acForm, "frmMainTEST"
    Exit_Command206_Click:
        Exit Sub
    Err_Command206_Click:
        MsgBox Err.Description
        Resume Exit_Command206_Click
    End Sub
    Code to go back to the frmMainTEST
    Code:
    Private Sub Command193_Click()
    'code courtesy of Paul Baldy - http://www.baldyweb.com/Bookmark.htm
    Dim rs As Object
    Dim lngBookmark As Long
    'set a variable to the current record
    lngBookmark = Me.QuestID
    'open the new form
    DoCmd.OpenForm "frmMainTEST"
    DoCmd.Close acForm, "frmMain"
    'take it to the selected record
    Set rs = Forms!frmMainTEST.RecordsetClone
    rs.FindFirst "QuestID = " & lngBookmark
    Forms!frmMainTEST.Bookmark = rs.Bookmark
    Set rs = Nothing
    End Sub


  2. #2
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    Try saving the Record before switching Forms:

    Code:
    RunCommand acCmdSaveRecord

  3. #3
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169

    Thumbs up

    That fixed the issue...thanks so much for your prompt reply!

  4. #4
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    Quote Originally Posted by Rawb View Post
    Try saving the Record before switching Forms:

    Code:
    RunCommand acCmdSaveRecord
    Just as a side note: You really shouldn't need to run this command if you have an autonumber field in the table. I always use an autonumber in my data tables and have never had to use this command.

  5. #5
    jpkeller55 is offline Competent Performer
    Windows XP Access 2003
    Join Date
    Sep 2010
    Posts
    169
    Thanks for info PK... I do have autonumber fields in my tables but it was not updating the record on new records but the RunCommand acCmdSaveRecord helped fix my issue. Thanks again!

  6. #6
    pkstormy's Avatar
    pkstormy is offline Access/SQL Server Expert
    Windows XP Access 2003
    Join Date
    Mar 2010
    Location
    Madison
    Posts
    682
    That still doesn't sound right. Again, I've never ever had to use the RunCommand acCmdSaveRecord to fix an issue with records not updating (although I do utilize the Refresh command when needed.)

    If it works though, I'm all for a solution to the problem.

  7. #7
    Rawb is offline Expert
    Windows XP Access 2000
    Join Date
    Dec 2009
    Location
    Somewhere
    Posts
    875
    It depends on how your Forms are set up. If, on the Form where you've created the new Record, your Focus leaves that Record, it always saves any changes then.

    JP's problem was that the Focus wasn't leaving the Record (and thus causing Access to save it) before the new Form was opened.

    I don't know much about the hows or whys, but it seems to me that it could even be a computer speed related issue: If you disk speed is high enough (or your processor speed low enough), Access may have time to save the Record before the new Form opens. If you have a slow disk (or a fast processor), then the new Form could be loading before Access can save the Record. . .

    You are correct in your original response though, if everything is working properly, you really shouldn't ever need to use the Save Record Command since Access normally tries to handle all that internally.
    Last edited by Rawb; 09-08-2010 at 06:38 AM. Reason: Spelling FTL :(

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Run Update Query on Current Record in Form
    By c3pse in forum Queries
    Replies: 3
    Last Post: 08-14-2010, 05:40 AM
  2. Replies: 0
    Last Post: 05-09-2010, 08:43 AM
  3. Replies: 0
    Last Post: 05-04-2010, 06:39 AM
  4. Replies: 3
    Last Post: 06-27-2009, 03:53 PM
  5. Replies: 9
    Last Post: 03-24-2009, 09:19 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums