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.

 

[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.

 

[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!

 

[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.

 

[JMongi]

And I appreciate having multiple viewpoints on the best way to not only approach a problem but structure the environment of said problem. It helps me improve my overall knowledge and it's always good to have multiple approaches.

 

[Isaac]

And I appreciate having multiple viewpoints on the best way to not only approach a problem but structure the environment of said problem. It helps me improve my overall knowledge and it's always good to have multiple approaches.

Well said. No need to apologize, it's very, very healthy in tech learning to get multiple viewpoints. I always used to say, when I used UtterAccess.com mostly to ask questions, I learned the most when people went on long soapboxes or several people disagreed--that way I got 2 or 3 times as much information!

 

[JMongi]

The irony is that I'm usually the one on the soapbox. Either that or frustrating people by answering their yes or no question with a paragraph of text!

I'm well aware of my foibles. I guess the good news is that its made me pretty good at disseminating information to newbies once I understand the topic myself. Gets me teased sometimes though....by my wife!