Editing mode on sub-form won't allow changes to newly saved records.

[marcus101]

Hello:

Here's my problem:

I have a form/sub-form with two table links, one to Names (the master form) and one to Addresses (sub-form).

I have an "editing mode" button which allows edits and additions to the field data in the sub form, like so:

in On_Click event code for EDIT MODE button:

Me.AllowEdits = True
Me.AllowAdditions = True

Here's my problem - when I go and edit an EXISTING address and then SAVE it, everything works OK.

I'm using a simple button with a save function created automatically by the button wizard in the forms Design View.

But if I go back to Viewing MODE (similar button), which basically sets these two values to FALSE, everything works OK up to that point.

But if I RETURN or go back to EDIT MODE to change the SAME record, then all of my normally editable fields in the sub form are blanked out, and non-editable, as if they are no longer enabled, though I am not setting those properties via code.

Obviously I'm missing out on something here. Is there something more I need to do to save correctly?

Tried using Me.Refresh and the problem still crops up.

Maybe I need to include DataEntry = True/False as well?

Maybe I need to tie edits to the master/source form/table
more strictly in order to prevent this problem?

The weird thing is, closing and re-opening the form from scratch works fine too, so it must be a fairly minor item I'm missing.

Any suggestions or ideas?

Thanks.

marcus101



marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[gol4]

just one question I am abit unclear on. Are the allow/disallow edit buttons located on the subform or the main form?

 

[marcus101]

Woops. Missing some important info about my problem there.

Good point, gol4. Sorry about that

My "Edit Addresses" button is on the subform itself. Because I have many addresses for each Name, I was hoping to split out this function so I can simply edit the Names section on the Main form and then use the existing Address (there is always at least one so there is no problems displaying it at least) section to either Add or Change records as required.

BTW, the form merely edits existing records. I have an entirely different form to create new ones because of limitations caused by the link between tables and that works just fine.

The editing form (at the subform level, anyway) seems to be creating a temporary second record when saving, because sometimes when I switch between Editing and Viewing (again, both buttons are on the subform itself) sometimes I see grayed out fields and NO information, even though there is actually a saved record in that table, and is also accessible, indirectly, through the previous and next record buttons on the subform.

Very strange behaviour.

One thing I DID notice is that REMOVING the "AllowAdditions" setting is an absolute NO-NO at the subform level. This seems to imply that editing requires Additions support to be turned on, which seems a bit strange.

Hope this helps clarify things a little. I wanted to know if I missed anything obvious.

At the moment, I'm kind of leaning towards forcing a close and re-open of the form upon save because that seems to work fine technically in principle, though I know that is just a very kludgy workaround to the problem, it's just a temp solution.

I'd much prefer to tighten up and FIX the problem if possible assuming I can get some understanding as to WHY I'm not able to just save the darn record and make changes to editing modes once that happens.

Perhaps I need to save the record at the master form level as well, or instead of, at the sub-form level?

Seems relatively straightforward in theory, but not so much in practice, I guess.

All suggestions welcome.

marcus101



marcus101
Access/SQL/XML Developer
Ottawa, Canada

 

[gol4]

I must say your problem seems to be rather unique.
you state

because of limitations caused by the link between tables

Is it possible due to relationships between tables that once you make a change your recordset is no longer updatable.

is the recordsource of your subform based on a query that is an updatable recordset.

One other thought is me.subform.requery

 

[marcus101]

gol4:

I guess uniqueness depends on your perspective.

What I'm doing I don't think is very complex but okay, whatever.

I'm not using a query for my recordset, I'm simply taking it directly from the Tables themselves, both Names and Addresses.

They are linked through the Names ID, with a cascading update/delete enabled in the Relationships Window.

I'm guessing that your suggestion would be to use an updateable query as my recordsource for this recordset, though I don't know how recordsetclone would respond to this - presumably it would just connect to that perfectly OK.

I NEED recordset clone to work in order to count the number of names and addresses in both parts of the form.

I have an OnCurrent event in both forms which works just great for that.

I've tried changing settings for DataEntry (True or False) on the subform when switching from Viewing to Editing modes, but that doesn't seem to work - the fields are still locked as if not enabled or updateable - it has been suggested in other threads that this MAY cause an extra record to be created as part of that functionality in certain cases, though I don't think it applies here.

Possible Solutions being Attempted:

1. One possible solution may be to use the AfterUpdate event to force the fields to become re-enabled and not locked, though I've tried variations of this before without success.

2. The most likely answer will probably be creating a raw SQL query containing the fields for both tables and using that as the recordsource for the form and the sub-form, maybe it is just as simple as that.

3. Failing that, my only workaround is to forcibly close and re-open the form, linking to the current record and filtering to that when it re-opens, clearing the restriction of not being able to update and fooling Access that way.

Option #3 seems like a rather long winded way to just do some editing of records. Maybe using queries as recordset sources is the better "across the board" answer for these types of forms.

marcus101

PS: BTW, shouldn't there be some sort of semi-comprehensive sub-form FAQ here by now? Just wondering.


marcus101
Access/SQL/XML Developer
Ottawa, Canada