Results 1 to 6 of 6
  1. #1
    Join Date
    May 2019
    Posts
    65

    Using Workspace and updating a data in a form

    I'm working my way slowly through VBA in Access (2016) and am experimenting with adding a record, saving changes, and not saving changes.

    In the code below, I Open the form frmContacts, but when I run the code, the form is opened but the MsgBox immediately appears and there is no option to edit the data in the form.
    What am I missing hear?



    Thanks, JeffSub mynewworkspace()
    Dim myR As Recordset, myWS As Workspace
    Set myR = CurrentDb.OpenRecordset("Contacts")
    Set myWS = DBEngine.Workspaces(0)

    myWS.BeginTrans


    DoCmd.OpenForm ("frmContacts")
    myR.Edit
    myR.Update
    If MsgBox("Save Changes? ", vbQuestion + vbYesNo) = vbYes Then
    myWS.CommitTrans
    Else
    myWS.Rollback
    End If



    End Sub

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    Where are you calling mynewworkspace()?

    Why are you using recordset and transaction? Exactly what needs to 'roll back'? I don't see any data edits in code. What data edit would need to be saved for a form just opened? I.e., what you show doesn't make sense.

    If you want to validate data entered on form and allow for cancellation, use form BeforeUpdate event.

    Workspace is Access 2003 (mdb) object. AFAIK, support for Workspace ended with Access 2013.

    Please post code within CODE tags to retain indentation and readabilty.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  3. #3
    Join Date
    May 2019
    Posts
    65
    Quote Originally Posted by June7 View Post
    Where are you calling mynewworkspace()?

    Why are you using recordset and transaction? Exactly what needs to 'roll back'? I don't see any data edits in code. What data edit would need to be saved for a form just opened? I.e., what you show doesn't make sense.

    If you want to validate data entered on form and allow for cancellation, use form BeforeUpdate event.

    Workspace is Access 2003 (mdb) object. AFAIK, support for Workspace ended with Access 2013.

    Please post code within CODE tags to retain indentation and readabilty.
    Hi and thanks for responding.

    My code currently uses 99% Macros. To obtain some more flexibility, I want to try it using more VBA procedures. The code that I presented to you is stand-a-lone database for practice and has nothing to do with my current database program. The sub routine is just being called by RUNNING it. Essentially I want to open a form to add a new record, enter some values in the fields, be able to then save or not save the record. Also, if I select a current record, I want to save changes or exit without saving. I didn't realize that Workspace was not supported in 2019.
    I'm just looking for the best way to do that.


    Thanks, I always appreciate your help.

    Jeff

  4. #4
    June7's Avatar
    June7 is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    52,816
    I don't think transaction is pertinent to data entry by user, only when code is changing data and you don't show any code for managing data and it is not necessary to open a form for code to change data. So, if you want form to open and user to enter data then confirm whether or not to save, use BeforeUpdate event or maybe button Click with Undo command.

    I could be wrong about Workspace but I did read that somewhere.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  5. #5
    Join Date
    May 2019
    Posts
    65
    Thank you
    I will try that.

  6. #6
    accesstos's Avatar
    accesstos is offline Expert
    Windows XP Access 2007
    Join Date
    Dec 2018
    Location
    Greece
    Posts
    551
    Hi Jeff!

    I assume that you are looking for a way to open the form in modal mode.
    If so, set the "WindowMode" argument to acDialog as folows:

    Code:
    DoCmd.OpenForm "frmContacts", WindowMode:=acDialog
    For the rest of code, I don't have a comment.

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

Similar Threads

  1. foreign key not updating data from form
    By dave100 in forum Forms
    Replies: 1
    Last Post: 08-07-2013, 10:31 PM
  2. Using Rollback with a workspace
    By Perceptus in forum Programming
    Replies: 2
    Last Post: 03-21-2013, 12:44 PM
  3. Updating Data in a Form
    By asmith533 in forum Forms
    Replies: 1
    Last Post: 09-11-2012, 10:45 AM
  4. updating a form with new data
    By markjkubicki in forum Forms
    Replies: 6
    Last Post: 08-25-2010, 01:29 PM
  5. Chart Workspace Title
    By thestappa in forum Queries
    Replies: 0
    Last Post: 07-07-2010, 10:23 AM

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