Results 1 to 8 of 8
  1. #1
    Yiipie is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7

    Submission of data to a table / query through a form upon click of button - Dump if not clicked

    Hi all,

    I'm sure this has definetly been asked before, however I'm struggling to find an answer that I fully understand and can incorporate into my own system. So apologies in advance if this is a duplicate or similar to other threads.

    I have multiple forms on my system, some create new records, others update existing records, so on and so forth. These are all 'bound' forms.



    After research i believe i understand the theory of .dirty records however I have no clue how to implement it.

    I require a form to only write the data to the table / query if a save button is clicked. Any other action (Closing the form, switching to another record, etc) then dumps the data inputted.

    My table also uses some Autonumbering as ID's, It is important for me to retain this function (As for bound forms).

    I undertstand i will have to implement any solution onto all forms, thats not a major issue as I've spent enough time slowly developing the system and adapting it over the last few weeks. I'm not too clued up on SQL anymore, so i really would need that explained in a step by step process if proposed, however this would not be the best of solutions for me.

    See attached Zipped version of my system (All items are hidden)... Cardinal HUB.zip

  2. #2
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    What exactly is your database about? It is often helpful if you provide readers with an overview of your business n imple terms so we understand the context of your issue.

    I have multiple forms on my system
    that may well be, but forms and queries etc are part of HOW you have attempted to automate the what.

    I undertstand i will have to implement any solution onto all forms
    [possibly, but not necessarily. There may be other options for HOW once readers understand WHAT.

    I'm not trying to be difficult. But put yourself in a reader's position, we know nothing of you, your environment, your experience, and nothing about your business and related facts, nor explicitly what you are trying to accomplish in any detail.

    You will need these facts to build an effective, maintainable database; and readers need more info to respond with focused ideas.

    Good luck with your project.

  3. #3
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    Access, being a Windows application, is tightly coupled to the OS and its fundamental design is that all entered data is automatically saved. Imagine the frustration/confusion if one had to explicitly write code / have a command button to save data where people would forget to press it. Imagine having to have a save button for every time you entered data into an excel cell.

    This differs from the web world where everything is a 'send' and 'get' instruction and the interface cannot be tightly coupled to the remote database - and so the Save button is much more needed and common.

    All this is to explain why whenever a client says they want a Save button - I talk them out of it. Essentially they would be spending money to undo an embedded feature fundamentally designed into the product by Microsoft. So your best path is to not do this.

    Having said all that; a form in design view of course has event properties. Fundamentally to take out all data in a newly started record so it is notsaved when form closes: Me.Undo But of course now you have to manage a logic toggle with the Save button to not perform that instruction. So you have to wrap that with If/Then depending on the Save event/value. But again - there can be a lot of 'gotchas' down the road on this concept and the true best practices is to educate your users and stay within the Microsoft mainstream on this.


  4. #4
    Bulzie is online now VIP
    Windows 7 64bit Access 2007
    Join Date
    Nov 2015
    Posts
    1,463
    Sounds like you need to make the forms unbound(nothing in the control source on the fields). Then after they enter the data and click the ADD/Save button you then write the data to the table. This would go in the OnClick event of the button:

    Dim db as Database, rs1 as Recordseet
    Set db = Currentdb
    Set rs1 = db.OpenRecordset("YourTable")

    rs1.AddNew
    rs1!FieldA = Me.FormFieldA
    rs1!FieldB = Me.FormFieldB
    rs1.Update

    You could also use an "Docmd.RunSql Insert ..." command maybe to add the record.

  5. #5
    Yiipie is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7
    Firstly thank you for the responses.

    Orange,
    The system will be used by one, maybe two people within the entire company (One being myself). The purpose of the system is to provide a document control system, a user (Myself) can create a new document, in doing so the information inputted would use the relationship to the Procedure system to assign it to one. (Title, issue date, approved by, document descriptions are assigned, aswell as the link to the procedure)

    From this i have created an 'update log', that uses the the document number as a foriegn key, update description, revision number, approved by etc are all inputted and saved.

    There are then forms in place to allow a user to view the data.

    There are aspects about how it all currently works that i dont like, however i have a deadline to meet and need to present something of an acceptable standard). There are similar systems that run along side one another, Internal Document control, External Docuemnt control, Procedure control. Each section has a update log. Due to ISO accreditations, our firm must log all document changes (Yes, i know sharepoint databases have this available, however it isn't adequate for our needs).

    I understand your not tryiong to be difficult, I completely understan, the more information the better quality answers can be provided.

    NTC,

    I understand for the aspect of not loosing data that removing this function could hinder and frustrate a user, however i need to reduce the factor of incorrect information been saved by accident. My issue is that i dont fully understand these properties. Even though i do create backups, I am a novice and I'm not 100% sure how I've actually developed the system as far as i have (I think theres been a lot of luck with my trial and error method).

    Bulzie,

    What you've explained sounds like something that would be worth trying, however as I've said above. I have no clue what I'm doing when messing with those properties. An unbound form may create a cascade effect to how I've configured the rest of the system and I've already lost enough sleep getting it to this stage. (I have studied SQL at a higher level however I've changed trade since then and havent kept my skills on point in that sense).

  6. #6
    NTC is offline VIP
    Windows 10 Access 2013
    Join Date
    Nov 2009
    Posts
    2,392
    One might suggest whether the issue is "incorrect information been saved by accident" or more accurately "incorrect information being entered in the first place". Rather than focusing on the Save / Undo function perhaps it is more wise to focus on the data entry accuracy up front with field check logic.

    One could, as Bulzie is suggesting, have a staging table whereby all is entered and checked in a permanent though temp table - and then trigger a Write (Append Query) to write that record into the permanent table.

  7. #7
    Yiipie is offline Novice
    Windows 10 Access 2013 64bit
    Join Date
    Mar 2017
    Posts
    7
    I believe validating the information entered may create even more of an issue. The problem is that the majority of the data is unique and can not really be validated.

    I've now come to the decision to leave the system as is, as i will be the primary user, i will just have to take caution when inputting the information and ensure that i don't mess up (Untill i'm more up to scratch with how to implement such a method. The bonus is that I have access the back end of the system (Tables etc) so if needs must i can delete the incorrect rows and compact and repair the database again (I know its a backwards move, but for now its a workable solution)

    I will however keep in mind the unbound forms option and the implementation of the SQL coding to write the data.

    Thank you for your assistance though, its highly appreciated, I'll browse the other threads to try resolve some other issues i want to address in the mean time.

  8. #8
    orange's Avatar
    orange is online now Moderator
    Windows 10 Access 2010 32bit
    Join Date
    Sep 2009
    Location
    Ottawa, Ontario, Canada; West Palm Beach FL
    Posts
    16,716
    The problem is that the majority of the data is unique and can not really be validated.
    I doubt that this is a driving factor in any decision.
    You might want to describe the business process that you want to automate with this database.
    I don't think you have your requirements defined to the degree necessary for you or anyone to develop a database/application.

    Here is a free data model that shows generic document management database structure.
    It is generic and shows the things generally related to document management. your situation may need ore, or less than shown. It is meant as a starting point.

    If you are writing or organizing standard operating procedures, the standard formats, terminology and meaning will be critical.

    Have you identified the steps in the process? The expected outputs and inputs of each step?
    You may get some good ideas and techniques for analyzing your process(es) by watching some of the free "nuggets" from BA-Experts on youtube.

    Good luck with your project.
    Last edited by orange; 03-08-2017 at 03:14 PM.

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

Similar Threads

  1. Replies: 2
    Last Post: 01-09-2017, 04:56 PM
  2. Data dump to load form faster with subreport totals
    By aellistechsupport in forum Forms
    Replies: 8
    Last Post: 01-15-2016, 05:06 PM
  3. Replies: 12
    Last Post: 11-11-2014, 02:10 PM
  4. Replies: 5
    Last Post: 05-20-2014, 11:51 AM
  5. Replies: 1
    Last Post: 01-29-2010, 11:30 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