Form Dirty Problem - Which Property?

[gray]

Hi All

WinXPPro (SP2)
Access 2002 (SP3) and Access 2007

I have a mainform with a number of tabs on it. On the 1st tab there is a subform.

The main and sub forms both have a recordsource of :-

SELECT * FROM Tracks;

I syncronise bookmarks in the current events of main and subforms to ensure they both are 'looking' at the same record in the 'Tracks' table.

I have a custom Edit button on the mainform which, when clicked, modifies the rowsources of several comboboxes on my subform in order to populate them with choices. I do this with SQL.. (eg. SELECT * FROM Supplier_names) and a requery.

However, this appears to make the subform 'Dirty' and it gets locked preventing me from clicking into any of the comboboxes.

To lose this issue and because no 'real' updates have been done yet, I have tried to use 'Dirty=False' on the Subform and a 'Cancel=VBTrue' in the Subform's Beforeupdate. This works happily but when control is returned to the Main form module it immediately crashes with

3270 Property Not Found.

Infuriatingly, it does not tell me which property! How do I fnd out which property it thinks it's looking for please?

Thanks

 

[llkhoutx]

The main form and sub form having the same recordsource is very bad practice and may cause the record locking problem.

There is never a reason to do what you are doing.

 

[boblarson]

The main form and sub form having the same recordsource is very bad practice and may cause the record locking problem.

There is never a reason to do what you are doing.

I agree. Why would you have the same record source for a main form/subform?

 

[HiTechCoach]

Hi All

WinXPPro (SP2)
Access 2002 (SP3) and Access 2007

I have a mainform with a number of tabs on it. On the 1st tab there is a subform.

The main and sub forms both have a recordsource of :-

SELECT * FROM Tracks;

I syncronise bookmarks in the current events of main and subforms to ensure they both are 'looking' at the same record in the 'Tracks' table.

I have a custom Edit button on the mainform which, when clicked, modifies the rowsources of several comboboxes on my subform in order to populate them with choices. I do this with SQL.. (eg. SELECT * FROM Supplier_names) and a requery.

However, this appears to make the subform 'Dirty' and it gets locked preventing me from clicking into any of the comboboxes.

To lose this issue and because no 'real' updates have been done yet, I have tried to use 'Dirty=False' on the Subform and a 'Cancel=VBTrue' in the Subform's Beforeupdate. This works happily but when control is returned to the Main form module it immediately crashes with

3270 Property Not Found.

Infuriatingly, it does not tell me which property! How do I fnd out which property it thinks it's looking for please?

Thanks

I have read this post several times over the past few days and I am also baffled with your design. I agree with the other replies.

I can not image why you would have a parent form and sub form with the same record source.

After several days pondering you post, the only reason I could come up with is that you might want to do this so that you can use the parent form as a way to link the sub form's together. The first sub form links backwards to the parent form. When the parent form is in sync with the first sub form then the other sub forms can link to the parent for to show the related records for the first sub form. Is this what you are attempting?

 

[gray]

Hi All

Yes, HiTechCoach, that's exactly it.... to synchronise the mainform (where I have custom action buttons) with several subforms. My "principal" subform holds the editable fields for the main record.

The real essence of my problem is that I want to use custom New/Edit/Delete/Cancel and Save buttons. I've been trying to come up with a model for many months and been thru' dozens of design iterations but I am thwarted each time. Curiously, durng my research, I have seen a ton of other people wanting this facility and the fact that Access provides custom-buttons out-of-the-box surely means it is possible?

It seems to me that the 'native' mode Access uses (whereby records are committed almost immediately) is too risky for financial records .. a user catches a key by accident and before you know it, tax records do not reconcile. I prefer users to have to take at least two actions e.g. click 'edit record', make changes in and then click 'save' or 'cancel'.

With your advice, I have re-engineered my design to have a separate table for the main form and I now bind the main and all subforms to their respective tables. I use master/child links to keep them in step.

I still have the problem though.

I have tried using Cancel=true in the subforms' beforeupdate events but these events are not fired when focus goes to the main form (to click Cancel for example).. the changes to subform records ARE committed however? Using the beforeupdate events of the controls prevents any actions at all.

On previous projects I have gone to enormous lengths to use temporary tables and dynamically alter form recordsources. Sadly, that method opens up a whole new set of problems though )

Is there a truly definitive way of preventing a table update?

UPDATE:
I've added referential integrity on my tables and now the subforms' beforeupdates do fire! The problem I now have is that with Cancel=True in my subform beforeupdate event, I am prevented from clicking my Save or Cancel buttons on the main form. I've a horrible feeling I'm going around in circles

Thanks for your time on this...

 

[dfenton]

It seems to me that the 'native' mode Access uses (whereby records are committed almost immediately) is too risky for financial records .. a user catches a key by accident and before you know it, tax records do not reconcile.

That indicates an incomplete design. That is, if erroneous data can be entered via a "fat finger," it means your app is not adequately validating input. There are several levels to that:

1. referential integrity

2. field-level validation rules

3. judicious choice of the controls used to edit data

4. event-based code to validate that data is consistent before it is committed.

All of these things are required to insure that the data entered is "good data" -- and that's true even if you went to editing entirely in unbound forms (something I'd recommend against).

Last of all, if you don't like the "Access way" maybe you should use something else?

 

[llkhoutx]

To be absolutely safe on updates, use unbound subforms.Then prompt user when Save is clicked to be certain that record is to be saved. A double check may even be appropriate.

Note - populate unbound subforms on the event of your choice with DAO code; similarily save subform record(s) with DAO code.

 

[The_Doc_Man]

Having just fought off a couple of these with bound forms and wanting to do confirmed "are you sure" type operations, I took this approach on every bound form. (Don't know what to tell you about subforms...)

First, all forms are marked to .DataEntry is false. (This is OK because .AllowAdditions can still be true.)

Next, Form_Current looks at the current record to see what it has for a Prime Key. If the PK is null or 0, I check for some flags in the declarations area of the class module - ClkCreate, ClkCommit, ClkRemove - three functions I am protecting. The button routines for COMMIT, CREATE, and REMOVE set their corresponding buttons and clear the other buttons.

The next factor is that Form_BeforeUpdate won't fire unless an update is going to occur. In that code, for which there IS a Cancel parameter, I check for the commit button flag. Cancel is set to TRUE if I'm about to update without having gone through the COMMIT button. Similar concepts apply to Form_Delete and the Remove flag, and Form_BeforeInsert and the Create flag.

When I try to insert a record and should not have done so, I can get there two different ways. First, by simply navigating to the implied blank record after the last "real" record - which I trap in Form_Current via two factors: the PK being impossible and ClkCreate=FALSE. If the PK is valid or the flag is TRUE, I allow it. The other way to get there is to use the Icon Bar or Navigation bar that is the triangle-asterisk symbol to "go to a new record." (Do not pass Go, do not collect....) There, again Form_Current "smells the rat" and stops the implied create. In both cases, when I am where I should not be, I fire off a Me.Recordset.MoveLast, which on a bound form takes me away from the implied blank "new" record.

Another trap I had to set was in Form_Unload (which precedes Form_Close) I had to check whether I was closing a dirty form. You can cancel an unload, so I stopped that one, too. If you improperly tried to navigate to another valid record when on a dirty one, you fire the Form_BeforeUpdate, so if you have that blocked off for the other situation I mentioned, you have it blocked for this one as well.

The second line of defense is that in the button click routines, before I will allow any of the "magic" flags to be set, I'll do a MsgBox with an OK / Cancel option and if they cancel, nothing happens.

I have buttons for Close, Commit, Cancel, Create, and Remove (plus a few others), but the trick I use is that on dirty forms, I disable and make invisible the buttons for Close, Create, and Remove - and instead bring up Commit and Cancel. When the form is clean, you guessed it - Commit and Cancel go away but the other three show up.

I know that's a lot to digest. It didn't happen overnight. But with a little bit of persistence, it can happen for you.

By the way, SCRUPULOUSLY avoid setting up anything to change the form's .Allowxxxx properties. Allow anything at all - but use the buttons and form events to block things you don't want. If you muddle about with the properties of the form's .Allowxxxx dynamically, you can trigger oddball events that can even change what controls have focus and such craziness as that.

If you are going to do this kind of thing a lot, build a prototype form that does this sort of stuff on a "fake" record for development purposes, then copy the prototype every time you need a bound form.

The thing about unbound forms is that you cannot tell when they are going to fire an event other than Close, Resize, or the like. Nothing to update, nothing to insert, nothing to delete - and therefore no events for those actions. In a way that is good, in a way it sucks. Because then you have to "roll your own" subroutines to control what is going on. I don't recommend it for the faint of heart, but if you have to do it to get where you need to get, well... you gotta do what you gotta do, as my cousin Bubba always said.

 

[llkhoutx]

Using unbound subforms eliminates all the issues and VBA code raised by The_Doc_Man.

 

[dfenton]

Using unbound subforms eliminates all the issues and VBA code raised by The_Doc_Man.

It also eliminates 90% of the advantages of Access as a development environment.

 

[boblarson]

It also eliminates 90% of the advantages of Access as a development environment.

Now that is a true statement.

 

[llkhoutx]

There's a big difference is doing a job right and getting the right job done.

 

[boblarson]

There's a big difference is doing a job right and getting the right job done.

And if you are going to eliminate 90% of the benefits of Access as a frontend, why not use another tool which is better suited; VB6, VB.NET, C#, etc.

In fact, VB.NET 2008 would probably be more efficient up against Access if you are not using bound forms.

 

[The_Doc_Man]

I had to cheat on the issue of bound sub-forms. If you change focus at the wrong moment, you update the parent form, which kills everything I was trying to control. So I cheated the other way - the sub-forms aren't subforms. They are unbound controls in the main form. (Takes up the same amount of room on the screen in my case...)

Until the parent is created, you cannot show the dependent items. Once the parent is defined, you can enter values in the unbound forms. I check another flag when you are about to navigate so that the form APPEARS to still be dirty - even though unbound controls don't dirty up the form. I do my own updates. Pain in the patootie, but it only happens on one form in the project and I have learned to live with it. That is the ONE factor of parent/child forms I detest - automatic update of parent when you dare to select the child form. I even have disabled the child a couple of times, but somehow the event slipped through. Precisely because it was a once-only thing, I let it slide until I can revisit and do this problem "right."

 

[gray]

Hi All

Thanks very much for all your replies (esp Doc Man, thanks for such a comprehensive answer).

Just to pickup on the subforms vs main forms question -

1. Unfortunately I reached the limit for the number of controls on a single form (or at least I think I did) so I split my form into main and subs. As it happens I pruned a number of controls later but it was too big a job to go back to one form by then.

2. I wanted to be able to present my principal records in either form or datasheet view. As you probably know, custom buttons are not displayed in datasheet view so they have to reside on a main form.

For anyone else out there implementing this model I eventually decided to use extra forms so now when the user clicks New or Edit, I present them with 'pop-up' standalone forms. These simply have a Cancel, Save or Exit button. The Save button also requeries the main form to keep it up to date.

Rgds

 

[dfenton]

If you exceed the number of allowable controls, it suggest you have a major schema design error.