Results 1 to 9 of 9
  1. #1
    Join Date
    Nov 2009
    Posts
    223

    Unanswered: Does the User want to Save the record?

    I think searching around the internet probably gives TOO MUCH information (haha).

    I want to include a “Save” button on my Form. This is because sometimes when a User fills in a Field, I think sometimes the current record (or current changes) may not be “saved” unless the User moves to another record using the record navigator???

    I have done my research but I am not sure which of the following is the correct or best VBA code to use? I presume the coding will go in the new Save buttons OnClick property?

    RunCommand acCmdSaveRecord


    If Me.Dirty Then Me.Dirty = False


    If Me.Dirty Then
    RunCommand acCmdSaveRecord
    End If
    DoCmd.Close


    Forms("MainFormName")("Works Card").Requery


    Forms("Form1").Refresh
    What would you attempt to do if you knew you would not fail?

  2. #2
    Join Date
    Nov 2009
    Posts
    223
    Interestingly, the Wizard gives the following code when asked to Save and Refresh - this should be the best code, yes?

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, acSaveRecord, , acMenuVer70

    DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70
    What would you attempt to do if you knew you would not fail?

  3. #3
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    I would use:
    Code:
    If Me.Dirty = True Then Me.Dirty = False
    Don't blindly trust Access Wizards, many of them were never updated or re-written and use a lot of obsolete or deprecated features. For instance, this is an excerpt from MSDN about Access 2003 (http://msdn.microsoft.com/en-us/libr...ice.11).aspx):
    Remarks

    Note In Microsoft Access 97, the DoMenuItem method was replaced by the RunCommand method. The DoMenuItem method is included in this version of Microsoft Access only for compatibility with previous versions. When you run existing Visual Basic code containing a DoMenuItem method, Microsoft Access will display the appropriate menu or toolbar command for Microsoft Access 2000. However, unlike the DoMenuItem action in a macro, a DoMenuItem method in Visual Basic code isn't converted to a RunCommand method when you convert a database created in a previous version of Microsoft Access.
    The note is still there in the 2007 version (DoMenuItem Method*[Access 2007 Developer Reference]), and probably in the 2010 too.
    Have a nice day!

  4. #4
    Join Date
    Nov 2009
    Posts
    223
    Thanks for your opinion, Sinndhho.

    Please woudl you confirm that the code goes in the OnClick property of the Save button?
    What would you attempt to do if you knew you would not fail?

  5. #5
    Join Date
    Nov 2009
    Posts
    223
    Yup, all that works fine - thank you.

    I actually changed it a bit myself coz the Save button will go on the same SubForm menu at the bottom of every page.

    If Me.Parent.Dirty = True Then Me.Parent.Dirty = False

    It is only a small matter but is there a code which will keep the Focus on the same Field after the Form has been saved??
    What would you attempt to do if you knew you would not fail?

  6. #6
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. for every control that's supposed to receive the focus back, add this on the line On Got Focus of the Event tab of the Properties window: =IHadTheFocus() (see the attached picture).

    2. Add a private member variable m_strLastControlName and a function IHadTheFocus() in the code of the form, like this:
    Code:
    Option Compare Database
    Option Explicit
    
    Private m_strLastControlName As String
    
    Private Function IHadTheFocus()
    
        m_strLastControlName = Me.ActiveControl.Name
        
    End Function
    3. In the event handler of a command button, you just need to add a single line of code to return the focus to the control that had it before the command button was clicked (provided that this former control called the IHadTheFocus() function, as explained earlier):
    Code:
    Private Sub Command1_Click()
        
        ' Do something when Command1 is clicked.
        '
        If Me.Dirty = True Then Me.Dirty = False
        
        ' Set the focus back to the previous control.
        '
        Me(m_strLastControlName).SetFocus
        
    End Sub
    Attached Thumbnails Attached Thumbnails Properties_Window.jpg  
    Have a nice day!

  7. #7
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    I can only wonder how you know about all these things??? but hey, I am glad that you do

    thanks for the info - i will be trying to add these codes soon.

    thank you.

    Red Devil
    What would you attempt to do if you knew you would not fail?

  8. #8
    Join Date
    Nov 2009
    Posts
    223
    Sinndho,

    Where do I go (or how do I) add the code to the Form??

    Option Compare Database
    Option Explicit

    Private m_strLastControlName As String

    Private Function IHadTheFocus()

    m_strLastControlName = Me.ActiveControl.Name

    End Function



    Thans,

    Red Devil
    What would you attempt to do if you knew you would not fail?

  9. #9
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Just open the class module of the form with the VBA Editor. In Access 2003 Open the form in design view, then in the View menu select Code.

    Usually the two lines:
    Code:
    Option Compare Database
    Option Explicit
    are already there. Do not duplicate them : this would cause a compilation error.
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •