Dirty Rotten Forms (1 Viewer)

[Thales750]

Making bound forms is all good. It alloys folks with no experience to build a functional database.

But it exhibits some pretty terrible attributes.

Namely controlling unsaved data.

What are some of the way you guys deal with this serious problem.

I now rarely use bound main forms. Obviously all of my subforms are bound, but I find more and more reason to keep my forms unbound. Now, it takes a little more code and error checking. I use DAO rs.AddNew; that way I never worry about dirty records.

Not so much for editing existing data but for adding new records.

Any thoughts?

 

[Galaxiom]

I have not found it to be a serious problem.

 

[Thales750]

I dont like have half saved data.

 

[nanscombe]

I tend to get around half saved data by:
a) Colouring mandatory fields in yellow (a visual clue for the users)
b) Testing for empty fields in the Form_BeforeUpdate() event

If they leave a field blank then I cancel the update, give them a pop-up warning and set focus on the unpopulated field(s).

They have two choices:
1) Fill in the blanks.
2) Abandon the record completely.

I say do, but it's coming up for 6 years since I actually did.

 

[Thales750]

Another part of it is that I have gone to a highly normalized system, and a serious reduction in parent daughter table structure, in favor of more link tables.

So adding a link become much easier because I automatically save PrimaryID using DAO, and it's always totaly reliable.

 

[Pat Hartman]

If you are not using bound forms, you are missing the entire point of Access and should probably switch to winforms which are easier to distribute. You need to learn about form events and when they fire. You actually have complete control over saving/not saving data if you use the correct event. The bottom line is the Form's BeforeUpdate event. NOTHING gets past it. It is the last event to fire before data gets saved so this is where you will need to put certain types of null and multi-field validations and any user prompts ("do you really want to save?") which I personally abhore and use only in limited circumstances. If you are constantly throwing up messages that the user has to respond to, he will soon learn to simply blow by them and then important messages will have lost their impact and the user will probably just blow past them also.

Many people (yourself included) get frustrated because they think they have to identify and trap all the actions that cause a record to be saved when in fact all you need to do is just sit in the BeforeUpdate event and let it all come to you. The only time a dirty record won't pass through the BeforeUpdate event is if you force Windows to terminate without saving.

 

[Galaxiom]

I expect the main complaint by those who don't like bound forms is the saving of a new record on a main form when moving the focus to a subform.

Their data model requires the subform information but if the user abandons this step they object to the main record already being saved.

Of course it is very simple to remove the main form record during validation of the subform but for some reason they object to this technique as though there is something obscene about having added a record and then removing it.

 

[Thales750]

If you are not using bound forms, you are missing the entire point of Access and should probably switch to winforms which are easier to distribute. You need to learn about form events and when they fire. You actually have complete control over saving/not saving data if you use the correct event. The bottom line is the Form's BeforeUpdate event. NOTHING gets past it. It is the last event to fire before data gets saved so this is where you will need to put certain types of null and multi-field validations and any user prompts ("do you really want to save?") which I personally abhore and use only in limited circumstances. If you are constantly throwing up messages that the user has to respond to, he will soon learn to simply blow by them and then important messages will have lost their impact and the user will probably just blow past them also.

Many people (yourself included) get frustrated because they think they have to identify and trap all the actions that cause a record to be saved when in fact all you need to do is just sit in the BeforeUpdate event and let it all come to you. The only time a dirty record won't pass through the BeforeUpdate event is if you force Windows to terminate without saving.

Actually I don’t think I need to trap all the actions. Sometime we simply add a default value so if we need to eliminate nulls, the users can simply be their normal lazy self. (I am one of those lazy users).


Fortunately we log all transaction records by time, location, and user, so management can train effectively.

More to the point; is eliminating the mother daughter relationship. Real world scenarios lend themselves to processes (separate from transactions and accounting) that are more flexible than the standard mother-daughter relationship.
The ability to store the last primary key is paramount. And if has to be 100% accurate under any circumstance. If it not available the system must abort that operation. Period.
There can be no orphaned records.
DAO is failsafe.
For Point of Sale, once again, records must be accurate against all possible conditions, including unplugging computers. Once again, either all or nothing. And “all” means no primary key until the time of saving the entire record, which with DAO takes about a thousandth of a second.
Finally the real point of this, you touch on.
Switching from Access.
That is going to be very difficult. For 17 years I have been using Access and my library is quite huge, we have entire modularized systems that fit more than 80% of the needs of small to midsized business. That’s a lot to scrap.

 

[Thales750]

We use bound forms for editing data, just less and less for data entry. Obviously subforms are always bound.

 

[Galaxiom]

Obviously subforms are always bound.

Why would this be so? I can't see any reason why a subform would have to be bound any more than a main form.

 

[Vassago]

The only time a dirty record won't pass through the BeforeUpdate event is if you force Windows to terminate without saving.

This right here is exactly why I had to move away from bound forms immediately on my main tables. It happened so often at my last company. It was very frustrating because I would have to go through the table and remove incomplete records often.

I actually eventually made a copy of the main table and used that bound to a form for entering new records. When the user was done, it would copy that record to the main table. It worked much better for my application, especially on a FE/BE interface.

 

[Thales750]

Why would this be so? I can't see any reason why a subform would have to be bound any more than a main form.

How would you use an unbound subform?

Remember I only advocate unbound forms for data entry or in main forms where the function of the main form is to act as a container for bound subforms.

Other than transaction type records; I rarely let user add new records directly to subforms, and never for core data. Generally we provide wizards for data entry of core data.
In the case of Point of Sale type systems, we do allow directly adding records to subforms. But these are stored in a temporary table and only migrated to the permanent tables when the transaction is complete. If any interruption occurs, including a timeout (credit card rules require unattended terminals to timeout) the system will delete all of the temporary records at the beginning of the next transaction. Otherwise they are deleted at the end of the current one.
It really boils down to the proliferation of linking tables in multiuser environments where getting a Max primary key is unreliable and multi-column keys are necessary and need to be managed absolutely.
The absolute nature of DAO routines’ ability to capture the primary key after the record is saved is very powerful.
So I develop a little procedure to make it easier.
1. I create a form bound to the data set
2. Use the “Add existing fields” button to drag all the controls to the form. This is cool because it insures all of the controls match the data types and configuration.
3. Then I unbound it.
4. Next I take a record from the table and copy and paste it into Excel
5. Then I copy the column headers from excel and special paste it into a vertical column. This becomes the DAO Rs! List of Fields
Easy as pie. A completely Failsafe Data Entry form.

 

[pbaldy]

Why would this be so? I can't see any reason why a subform would have to be bound any more than a main form.

You can't have multiple records in an unbound form or subform. Typically a subform would be displaying the many side of a relationship, thus potentially contain multiple records. That means it has to be bound, even if to a temp table.

 

[Galaxiom]

You can't have multiple records in an unbound form or subform.

OK. Not enough coffee this morning.

I was thinking of unbound subforms I have used but they only held single records (duplicating the single section of a Split Form).

 

[Pat Hartman]

@Vassago - It was very frustrating because I would have to go through the table and remove incomplete records often. - My comment regarding not going through the BeforeUpdate event would not result in incomplete records. It would result in the record not being saved at all. It was your users creating incomplete records and your code was either causing it indirectly or letting it happen. The most common cause of incomplete records is code in the form's Open, Load, Current or BeforeInsert events that dirties a record that the user later abandons. With proper validation in the BeforeUpdate event, these incomplete records would not be saved.

 

[Thales750]

@Vassago - It was very frustrating because I would have to go through the table and remove incomplete records often. - My comment regarding not going through the BeforeUpdate event would not result in incomplete records. It would result in the record not being saved at all. It was your users creating incomplete records and your code was either causing it indirectly or letting it happen. The most common cause of incomplete records is code in the form's Open, Load, Current or BeforeInsert events that dirties a record that the user later abandons. With proper validation in the BeforeUpdate event, these incomplete records would not be saved.

But it will still trigger the Autonumber. Plus what is the validation. The undo command needs to be used in conjunction with the docmd.delete record command.

To me, much cleaner to save the new record all at once with DAO.

 

[boblarson]

But it will still trigger the Autonumber. Plus what is the validation. The undo command needs to be used in conjunction with the docmd.delete record command.

Say WHAT??? The undo command does NOT need to be used in conjunction with a delete command. Don't know where you go that from but it certainly isn't true. All that is necessary is to issue the

Cancel = True
Me.Undo

which will undo the form. And as far as autonumbers go, here we go again. If you care what an autonumber's value is, you are using them INCORRECTLY.

To me, much cleaner to save the new record all at once with DAO.

And you are entitled to that opinion. I, myself, hardly ever use unbound forms. So, I am on the reverse spectrum. I like that Access does almost all of the work for me. So I don't toss it all and go with something where I have to manage it all. If going down that route, one might as well just write the app in VB.NET for example.

 

[Pat Hartman]

But it will still trigger the Autonumber

Yes because that is how autonumbers work. See Bob's comment.

what is the validation

Validation is code you write to ensure that the record is valid BEFORE you save it. Some validation can be done by the database engine and some you need to do in your own code. If a column is always required, you can define it as required in the table definition and Jet/ACE will NOT allow a record to be saved if the column is null. But things like date relationships need to be handled by you. You might have a start and end date in a record. Logically it makes no sense for the end date to preceed the start date and so you need to add code to ensure that it doesn't.

The undo command needs to be used in conjunction with the docmd.delete record command

I'm not sure what you are talking about here. If you do your validation in the form's BeforeUpdate event as you should, you can cancel the update and that will prevent the record from being saved. You rarely want to undo the record at this point. That would be most unfriendly unless you asked the user first.

 

[Thales750]

. If you care what an autonumber's value is, you are using them INCORRECTLY.
.

How is that?
We need to know them to create link records.
How is that wrong?

 

[Thales750]

Yes because that is how autonumbers work. See Bob's comment.
Validation is code you write to ensure that the record is valid BEFORE you save it. Some validation can be done by the database engine and some you need to do in your own code. If a column is always required, you can define it as required in the table definition and Jet/ACE will NOT allow a record to be saved if the column is null. But things like date relationships need to be handled by you. You might have a start and end date in a record. Logically it makes no sense for the end date to preceed the start date and so you need to add code to ensure that it doesn't.
I'm not sure what you are talking about here. If you do your validation in the form's BeforeUpdate event as you should, you can cancel the update and that will prevent the record from being saved. You rarely want to undo the record at this point. That would be most unfriendly unless you asked the user first.

I understood what validation code is. What I mean is; what is the procedure for insuring total accuracy, down to the one in a billion?

DAO procedures for adding a new record is accurate to at least one in a billion. Plus without any ambiguity you can record the primary key even if it is a composite key. Once again, completely without any chance of failure.

When you start to add millions of records and thousands of people doing it, anything short of errors per billion entries; is not acceptable.