How does Access handle the updates on my Forms?

[JMongi]

Does anyone have some nice breakdown on how Access handles/passes data between the form/query/tables? I've picked up a bit from reading different threads, etc. But, I would like to make sure I understand how data is moved/udpated so that I can better control it through my program.

Let me use an example. I have a "Master Project List" Form. It's a continuous form with an unbound label as a title and some column headers in the form header and my fields I want to list continuously in the detail section of the continuous form. It's a form list of the projects. Thus the title. Basic stuff.

In each row is a transparent button to call a child form that is a project specific form titled "Project Hours". It needs the ProjID to pull the right hour records. This form is a based on a custom query that pulls fields from various tables in conjunction with the main records that contain my hours. It is a continuous form as well so I can display all of the appropriate tracked hours. So far so good. Within THAT form is going to be a way to launch ANOTHER form that will be used to add another hour entry to the list. I don't think this idea is that foreign to many DB applications.

So, I want to implement a save button on this hour entry form. But to construct this save button appropriately I need to understand when Access wants to write data, how that flows through the forms and how a "cancel" button might be involved. I don't really want to do this via an unbound form so that's why I need a better understanding of how the data flows through access. Tied to that is the idea of an "X" button on these hour entry fields to delete an entry and a way to make one or more hour entries temporarily editable.

That's a lot to unpack from a single post. That's why I am looking more for general form update/data references and then if I run into snags as I develop this I'll post in a separate thread any issues.

 

[theDBguy]

Hi. Basically, if you have a bound form and start to edit the record, you will see a pencil icon on the left vertical bar. Once that pencil changes back to a right pointing arrow head, then the changes you made to the record is saved to the table, but not until then. Hope that makes sense...

 

[Ranman256]

You can connect the form directly to the table (via table or query) changes are immediate.
you don't have to use so many forms, but you can.

You can have a single rec master form with child form for user to add Hours to it directly.
Or in a more controlled way, open a single form,UNBOUND to the database. Let user enter data, then click SAVE to run an append query to add the form fields to the table.

 

[JMongi]

@theDBguy - That is the starting point. It's more about what triggers that change from pencil to arrow so-to-speak.
@Ranman256 - As I mentioned, I'm trying to avoid unbound forms for the time being.

 

[Isaac]

Another 'way' of envisioning it: Access saves data on many triggers (personally, much more than I'd like). When you close the form. When you go to another record. When you close the database. If you hit Tab too many times and depending on a form property that controls tab cycling.

My personal preference is to get rid of all of that counter-intuitive constant saving that Access does, and tie saves to explicit Save or Undo buttons. My personal opinion is that that matches users' expectations more precisely, since on websites records are not constantly being created and saved willy nilly, rather, you generally have to press things like "Save", "Continue", "Go", "Submit", "Next Page" [at least], and so on and so forth.

Here is one approach that works, that you might consider.

1. global variable, in a module where you store just globals: global blAllowSave as Boolean
2. when the form loads: blAllowSave=False
3. In the form's BeforeUpdate event: if blAllowSave = false then cancel=true 'this will prevent all the incessant saving that Access will try
4. In your form's Save button code: 1) all your validation code so the user is doing things right as per your rules, 2) blAllowSave=True, 3) Me.Dirty=False (triggers the actual save), 4) blAllowSave=False (toggle back to false)

With this simple combination of stuff, your data will only save when explicitly commanded to, and will silently override any other Save's that Access tries.

It's handy to have an Undo button too...since there are some situations where, if the form is dirty, but not saved, and the user wants to close the form (or go to another of your Tab control pages, for example), they'll need to Undo first....code= Me.Undo

That's my 2 cents, and what I often use.

 

[JMongi]

@Isaac - That is a great way to look at it. I did that in an Excel form that I wrote (that made me decide I wanted to push management to get me MS Access). I didn't think to apply the same idea in Access. That accomplishes everything that I was trying to do.

Why does Me.Dirty = False trigger the save? I would think it would be the other way around?
Also, how do you handle multiple forms being open at the same time with your global variable?

 

[theDBguy]

@theDBguy - That is the starting point. It's more about what triggers that change from pencil to arrow so-to-speak.

Some of the triggers include:

1. Closing the form
2. Moving to another record
3. Using the Save Record button on the Ribbon
4. Using code (macro or VBA)
5. Switching to Design View
6. Closing Access (or the application)

There may be others...

 

[Isaac]

Why does Me.Dirty = False trigger the save? I would think it would be the other way around?

Dirty is one of those weird properties where it's both read and write. Dirty means something is unsaved, so you can ask the code is Me.Dirty?
You can also tell the code Me.Dirty=false! but if really it was True, Access responds by "making it so"..........it makes it false, after first immediately saving it.
It is one of two major ways to save, the other one being DoCmd. I won't get into the debate here, there are many people on both sides. I only use Me.Dirty=False to save, that's just me.

For simultaneously open multiple forms I would just use multiple global variables, that was just a simplistic example.

 

[JMongi]

I guess I was just checking to make sure I understood that I would have to define multiple global variables up to the max number of forms open at any one time.

 

[Isaac]

I should add, I might not take that approach on linked child subforms, necessarily. There, it is a little more obvious to the user that they are typing detail lines into something, and whatever they type - that's what it will be unless they delete it out.

Where I take that approach is more on single-form situations, which I almost always separate by the way into a form for Viewing/Editing and one for Creating/Editing

 

[JMongi]

Ok. The more I thought it through I see your point (and it's what I'm doing anyway). So far I'd only need one global variable despite my many forms because they are broken up as you say.

 

[Pat Hartman]

Why does Me.Dirty = False trigger the save? I would think it would be the other way around?

I think it's counter intuitive also. When I first saw the expression, I thought the save was being cancelled. I have never encountered the bug in my 25 years of using Access so until I encounter the bug, I'll use the obvious command below.

In summary, anytime you leave a record but stay on the same form, the record is saved. That includes moving focus from a main form to a subform or vice versa as well as scrolling. Access also saves when a form is closed which by inference happens if you close the database. The situation where you have to be conscious of how Access works/doesn't work is when you are opening a different form/report from the one you are currently on. In this case, and it seems to be what you are asking about, immediately prior to the command to open the new form/report, save the current record:

DoCmd.RunCommand acCmdSaveRecord

This is the most direct, clearest way of specifically saving a record. It does one and only one thing. It saves the current record if it is dirty. It does not work in the form's beforeUpdate event because you are already into the save work flow and if you use it in an event that follows BeforeUpdate, you could put the form in a tight loop. Older versions of Access used to simply freeze and the screen would flicker as a result of the tight loop. Newer versions recognize the depth of the recursion and escape from the loop gracefully.

If you pull the plug or Access looses its connection to the BE because of a blip in the WiFi connection so you have to force Access to close, the dirty record is NOT saved.

Only ONE record on any Form including nested subforms can ever be dirty at one time. I don't generally show the record selector on single record forms but I do show it on continuous and DS views. So to confirm this make three nested forms and show the recordselector. It will show a right triangle for all non-dirty records but a pencil for the dirty record. Move focus from one subform to another and dirty the record you are on to see how the pencil always changes to a right triangle when focus moves to a different form. Also notice if you open a new form, the pencil does not automatically change to a right triangle. That's why you have to force the save yourself. If you don't force the save and the new form needs data from the open record, it will get the previous version if it references a query. You would only get the "dirty" version if you specifically referenced the open form controls. This would be poor practice and in some cases even cause errors since the data on the original form might not actually get saved.

Whether you understand form events or not, there is a logic to them and each event has a purpose and code should not be randomly placed in events. The original development team created each event for a specific reason and in most cases that means that there is only one "correct" event in which to place your code. The #1 most important thing to learn about once you decide you will write code is how events control what happens. The #2 is to find a list of VBA functions by type so you can see all the date functions and all the math functions together. Looking at an alpha list isn't helpful at all because you don't know what you're looking for.

The two most important form level events INMNSHO, are the form's BeforeUpdate and its Current events.

The BeforeUpdate event is like the flapper on a funnel. It ALWAYS runs immediately prior to a record being saved regardless of what precipitated the save and it can NEVER be bypassed unless you pull the plug in which case the record isn't saved. So, in order for a record to actually be saved it has to pass through this event. That is why I referred to it as the flapper at the bottom of a funnel. If you close the flapper, there is no way that record is being saved. You are in complete control if you want to prevent a record from being saved. Access may take it upon itself to initiate a save at a time you don't understand but you can ALWAYS stop the save with code in the BeforeUpdate event. For this reason, I put the majority of my validation code in this one event. There are some reasons for putting validation in other events but you are always safe with putting it here. To stop a record from being saved, you simply need to use a single line of code.

Cancel = True

I generally follow that with an error message and an Exit Sub so the user knows what the problem is. This leaves the record dirty and Access will refuse to close the form unless you use esc to clear any pending changes or fix the error so the BeforeUpdate event can execute without generating the Cancel command. If you want to undo the user's changes when you cancel use

Me.Undo

I don't recommend this though. It is really unfriendly to just erase a user's changes so I do it only in one case and that case is that the user does not have permission to update the record at all. So when he has update authority but has made a mistake, I leave the changes intact. By extension, this means that I don't have to worry about locking a form ahead of time. The forms can all be unlocked because I can stop the save if the user is not authorized assuming I have some sort of security login in place.

The Current event runs after the open and load events when Access places focus in the first record of the recordset. The Current event runs again each time you scroll to a different record. This event is used if you need to control hiding/showing controls for example. Say you only want to show a shipping address if it is different from the billing address. You would make that determination here. Sometimes you might want to lock individual controls so the user can't accidentally change them. You can control this with the BeforeUpdate event but maybe you want to give the user a visual clue by graying out the label.

In an Access form, every control has three properties that control which version of the data your code "sees"
Me.MyControl.Text = this event is only available in events where the focus is in the control being referenced. You would use it to examine each character as it was typed if you wanted to stop the typing after 6 characters for example or you wanted to prevent special characters. You could do this later in the control's BeforeUpdate event but that doesn't run until AFTER focus leaves the control.
Me.MyControl OR Me.MyControl.Value = This is the way we reference the "current" complete value that we are seeing on the form
Me.MyControl.OldValue = This is the previous or saved value. If you are on a new record, the OldValue will always be null. If you are on an existing record, the OldValue would be whatever is actually saved in the table.

 

[gemma-the-husky]

So, I want to implement a save button on this hour entry form. But to construct this save button appropriately I need to understand when Access wants to write data, how that flows through the forms and how a "cancel" button might be involved. I don't really want to do this via an unbound form so that's why I need a better understanding of how the data flows through access. Tied to that is the idea of an "X" button on these hour entry fields to delete an entry and a way to make one or
more hour entries temporarily editable.

I don't understand what you are trying to save? Can you explain please?

As @Pat Hartman said, it's actually really hard to not save data. The save happens in many ways, and users may well still be able to save things even if you close off some of the obvious ways. The only reliable way to have a save/not save, is to use an unbound form - then the save process is entirely down to the app designer, and it's much more complicated. Access is always trying to save data. It;s totally different to , say, Excel.

However, if you are trying to save the cumulative project position at a point in time, then that might not be a good idea either. The idea is that you can always calculate that data from first principles, and you need a really good reason to save the value. What if you save a value and then someone edits one of the records that contributed to the total, so that the total no longer matches the records? That's why you need to be very careful about saving calculated data. Even providing a "save" button needs to be treated with caution. It can imply to a user that if they don't click the "save" button, their work won't be saved, and that isn't the case. Even removing the X button to close a form can cause consternation, as it changes the default behaviour users come to expect.

 

[Pat Hartman]

The only reliable way to have a save/not save, is to use an unbound form -

I know #12 is long but it does explain how to be TOTALLY in control over whether or not a record gets saved. There is never a need to resort to an unbound form.

 

[gemma-the-husky]

Why does Me.Dirty = False trigger the save? I would think it would be the other way around?

I was never comfortable with using me.dirty = false, and always used runcommand accmdsaverecord.

However, I take this to mean - me.dirty is a property that means the record has been edited, and setting me.dirty to be false forces the record to be saved, and I am now comfortable that both do the same thing. I forget the construct precisely, but I recall years ago, users forced saves by invoking a menu item in code.

Note that with either of these constructs, you still need error handling. The record will still not be saved if a constraint has been broken - eg, a not null field is still null.

I appreciate the points made about not needing an unbound form. It's very rare for me to use an unbound form, and I will consider @Pat Hartman alternatives next time I think I need to.

 

[JMongi]

@ Pat Hartman - I was expecting a link to a reference, not THE reference Many Thanks!
@gemma-the-husky - I'm sorry if my post was unclear. Excel IS much easier to bottle up the saves since in practical terms it won't ever save unless you tell it to. I knew Access worked differently and wanted a fuller understanding before I continued to implement my design goals. I feel pretty confident about it now.

As far as what I'm trying to accomplish in this particular design goal. I'll try to break it down a bit. The overall job is to track project hours.
1. Shop personnel record their labor hours on written forms in the shop.
2. This part of the db will collect those labor hours for storage/future analysis.
3. The main form is a project list that shows cumulative hours, a simple toggle for active/archive to manage the list size and a launcher to open specific project hours form.
4. The specific project hours form is a list of tasks with hours that are part of that project.
5. This project form is where you add another task with hours by launching a task hour entry form.
6. Saving this task entry should close the task entry form and update the list on the project hours form.
7. The other function of the project hours form is to allow editing or deletion of previous tasks.

It's this last one that I assumed I would need to understand more fully the interplay of data/saving when more than one record is displayed/edited on my project hours form. Once I dive into it I'll probably post a different thread with any specific issues. I appreciate the more top level discussion in this thread. thanks!

 

[Pat Hartman]

ONE and only ONE record is ever "current" on any form and all its subforms. I thought I made that clear.

You never need to specifically save unless you are opening a form or report while leaving the active form open. In all other cases Access will always save when you leave a record. It doesn't in this case because technically, you haven't left the current record. It has no way to know what you are doing on the new form so you have to save the current record if the form/report you are opening references the data in the current form in any way.

I don't put specific save buttons on forms unless the user requests it unless, there is a need to save the current record but not leave it. The user can't save partially through a record because that would interfere with validation. The validation code in the form's BeforeUpdate event requires at a minimum that all required fields have been entered.

 

[Isaac]

It can imply to a user that if they don't click the "save" button, their work won't be saved, and that isn't the case

It is if you close off the other ways of saving by using my method, or at least that's certainly the intent. When I use the method I described, that's exactly the implication/message I want to send.

 

[Isaac]

I was never comfortable with using me.dirty = false, and always used runcommand accmdsaverecord

I'm just the opposite. Me. is clear and explicit and leaves no question. the Runcommand depends on the "active form", and IMO you should never use anything in either Excel or Access VBA that depends on what happens to be 'active' at the moment. setfocus is finicky at best and does not always hold. I've learned not to use Selections or Activations in vba the hard ways for sure.

I avoid anything in Access that is too context-dependent if i can, and that includes generally command menu commands.
I'm with this guy on the issue

 

[JMongi]

Whoops! Opened that can of worms with my poor word choice. By its very nature you can't have more than one record open at a time. I apologize for stating or implying otherwise. I meant to comment that the more intricate the UI, the more the developer has to be in tune with what's going on under the hood so that the final application runs as expected to meet both the design criteria as well as what the user is expecting. In my case, a bunch of general computer users who need a save and cancel button so that the data moves as they expect it to and don't know or care that is a DB environment that they are manipulating.