Results 1 to 14 of 14
  1. #1
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179

    Only recording data when a submit button is clicked?


    I'm building a large and complex form for my coworkers to enter data. By default Access will store the data into the corresponding table even if they only partially fill out the form. I can't make every field required as some of them will not be, but I do not want Access saving any of the data into the table until they click on a Submit button that I will add to the form. Is there a way to do this?

    Thanks for your time and help,

    Keith

  2. #2
    kd2017 is offline Well, I tried at least.
    Windows 10 Access 2016
    Join Date
    Jul 2017
    Posts
    641
    How complex are we talking?

    Do the users need to have the capability of saving their progress and coming back later?

  3. #3
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Right now it is about 45 different fields. Some they enter data into, so pull data from other tables depending on selections they have made in the form. I don't think they will need to have the ability to save their progress and come back later.

    Ideally, their data would not be saved until they hit the Submit button and if they tried to close the form before hitting that button, Access would give them a popup warning saying if they do not finish the form and submit it, that all data will be lost. The pop up warning might be beyond both Access and my capabilities, so that part might just have to rely on user training.

  4. #4
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,438
    You could put code in the form's BeforeUpdate event to carry out validation and cancel the update and undo the changes that were about to be saved.
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  5. #5
    Ajax is online now VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    8,262
    if your form includes a (linked) subform, the main form needs to be saved before you can start completing the subform. If this is the case you will need a different approach using either unbound forms or better disconnected recordsets or possibly make use of the vba transactions capability (begintrans/committrans)

  6. #6
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Quote Originally Posted by Bob Fitz View Post
    You could put code in the form's BeforeUpdate event to carry out validation and cancel the update and undo the changes that were about to be saved.
    I'm not familiar with validation code, but that sounds like something that might work. I'll have to think about how that process would go.

  7. #7
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Quote Originally Posted by Ajax View Post
    if your form includes a (linked) subform, the main form needs to be saved before you can start completing the subform. If this is the case you will need a different approach using either unbound forms or better disconnected recordsets or possibly make use of the vba transactions capability (begintrans/committrans)
    Right now my form does not contain a subform and I don't foresee adding that in the future to this one.

  8. #8
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,438
    Quote Originally Posted by Etoimos View Post
    I'm not familiar with validation code, but that sounds like something that might work. I'll have to think about how that process would go.
    Perhaps create a boolean variable in the forms module which would be set to true only when the "Submit" button is clicked but for it runs whatever code you use now. In the form's BeforeUpdate event you would use an If/Then statement to test if the boolean variable value is true and if not cancel the update with Cancel = True followed be Me.Undo to undo the attempted changes. The form's BeforeUpdate event fires however the form is closed.
    Post back if you get stuck.
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  9. #9
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Quote Originally Posted by Bob Fitz View Post
    Perhaps create a boolean variable in the forms module which would be set to true only when the "Submit" button is clicked but for it runs whatever code you use now. In the form's BeforeUpdate event you would use an If/Then statement to test if the boolean variable value is true and if not cancel the update with Cancel = True followed be Me.Undo to undo the attempted changes. The form's BeforeUpdate event fires however the form is closed.
    Post back if you get stuck.
    Thanks, I'm going to have to chew on that one for a bit. lol An Access master, I am not.

  10. #10
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,438
    Quote Originally Posted by Etoimos View Post
    Thanks, I'm going to have to chew on that one for a bit. lol An Access master, I am not.
    Neither am I, but I don't think it would be difficult to do. I'm off work and at home at the moment due to our "Stay at home" policy so I have time on my hands. I'll give it a try and post back when I've had a little play
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  11. #11
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Quote Originally Posted by Bob Fitz View Post
    Neither am I, but I don't think it would be difficult to do. I'm off work and at home at the moment due to our "Stay at home" policy so I have time on my hands. I'll give it a try and post back when I've had a little play
    Thanks. I've not used Access since about 1998 or so. I'm lucky enough to be able to work from home, but my normal work cannot be done from home, so I'm trying to find "value added alternatives" that I can do from home and be able to charge. This database is what I'm working on. It has turned out to be a good project and it is a huge time sink since I have to teach myself how to do everything and it will actually make work much better when we can go back into the office/lab. As such, I really appreciate all the help I'm getting.

  12. #12
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,438
    Quote Originally Posted by Etoimos View Post
    Thanks. I've not used Access since about 1998 or so. I'm lucky enough to be able to work from home, but my normal work cannot be done from home, so I'm trying to find "value added alternatives" that I can do from home and be able to charge. This database is what I'm working on. It has turned out to be a good project and it is a huge time sink since I have to teach myself how to do everything and it will actually make work much better when we can go back into the office/lab. As such, I really appreciate all the help I'm getting.
    Ok. Always pleased to help if I can.
    I still think that what you want to implement should be quite simple. Have a look at the attached db for a simple example of how I think it could be done.
    Attached Files Attached Files
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  13. #13
    Etoimos is offline Competent Performer
    Windows 7 64bit Access 2016
    Join Date
    Mar 2020
    Posts
    179
    Thanks for post the example Bob. I'll have a go a messing with that.

  14. #14
    Bob Fitz is offline Access Developer
    Windows 10 Access 2016
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    2,438
    Quote Originally Posted by Etoimos View Post
    Thanks for post the example Bob. I'll have a go a messing with that.
    You're welcome. Let us know how you get on
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



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

Similar Threads

  1. Create Button to Submit Data
    By mnewton367 in forum Programming
    Replies: 20
    Last Post: 07-24-2015, 01:10 AM
  2. Replies: 11
    Last Post: 04-29-2015, 01:39 PM
  3. Replies: 5
    Last Post: 01-09-2015, 03:58 PM
  4. Replies: 4
    Last Post: 09-07-2014, 11:41 AM
  5. Replies: 4
    Last Post: 05-16-2014, 12:32 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 - Senior Forums