Results 1 to 11 of 11
  1. #1
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    This is more of a philosophical inquiry although I have a real problem.

    I have a form that is quite complicated with a number of subforms and a large number of controls. More than once in the past couple of years, as clients have asked for customizations, after I complete the changes, when I reopen that form, I get the dreaded "Access has to close..." error and I've got corruption.

    This has just happened again. The real problem is, although I create a new database, import, turn of Track Autocorrect, Compile, compact and repair, etc, etc I don't seem to be able to get the problem to go away.

    I've more or less come to the conclusion that perhaps I need to rebuild the form from scratch. My question is really about best practices at this point.

    The database has about 170 queries, 60 forms(incl subforms) and 70 reports (incl subreports). The form that seems to be causing the problem is the main client form from which all of the database reports, etc derive their statistics. The general problem seems to be that if I don't muck with it, it is fine, but if I alter it - in this case I added a single command button to call another form - it gets corrupted.

    Any suggestions about how to deal with this would be greatly appreciated.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    I'd aim towards drastically simplifying the main form, perhaps by replacing it with a series of linked forms. I'd create those from scratch, instead of copying bits and pieces from the problem form, to avoid copying the corruption, wherever it may lurk.

  3. #3
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts
    The size of your database probably is not an issue - we run an app at several clients where whe have about 200 tables, 325 queries, 170 forms and subforms, and 210 reports and subreports, and it all hangs together. However our most complex forms can be problematic, and in two cases we have had to rebuild from scratch after two or three years of development. These forms in their worst case may have 100 or more controls and several hundred lines of VBA behind them. The forms also have one or more tab controls with up to 8 or 10 tabs, and a dozen or more subforms, many of which have their own tab control. So it's not entirely surprising that things go south once in a while. I think there is a upper limit on how many times you can edit and add controls to a form which I've seen documented somewhere but don't remember where. One trick you might try is saving the database to text and then loading it from text. I have seen that fix some corruption problems with forms.
    Wendell

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    [quote name='WendellB' post='800014' date='26-Oct-2009 22:40']I think there is a upper limit on how many times you can edit and add controls to a form which I've seen documented somewhere but don't remember where.[/quote]
    From Access specifications:

    [attachment=86198:x.png]

    This means that once you have added 754 controls/sections in total to a form or report, you can't add any more, even if you delete most or all of the existing ones.
    Attached Images Attached Images
    • File Type: png x.png (1.4 KB, 1 views)

  5. #5
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Many things can effect bigger forms.

    Compile often if vba code is one I was advised.

    Another is to turn off name auto correct which has always been flakey

    see http://allenbrowne.com/bug-03.html

    If you have a lot of code try a decompile every now and again
    Andrew

  6. #6
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='WendellB' post='800014' date='26-Oct-2009 17:40']I think there is a upper limit on how many times you can edit and add controls to a form which I've seen documented somewhere but don't remember where. One trick you might try is saving the database to text and then loading it from text. I have seen that fix some corruption problems with forms.[/quote]

    Can you explain saving the database to text, Wendell?

    This form has definitely been edited a lot. My guess is that it has reached the end of its useful life.

  7. #7
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='AndrewKKWalker' post='800026' date='26-Oct-2009 18:06']Many things can effect bigger forms.

    Compile often if vba code is one I was advised.

    Another is to turn off name auto correct which has always been flakey

    see http://allenbrowne.com/bug-03.html

    If you have a lot of code try a decompile every now and again[/quote]

    Thanks, Andrew. I know Allen's site and have run decompiles more than once. I always have name auto correct off.

  8. #8
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    [quote name='HansV' post='800006' date='26-Oct-2009 17:15']I'd aim towards drastically simplifying the main form, perhaps by replacing it with a series of linked forms. I'd create those from scratch, instead of copying bits and pieces from the problem form, to avoid copying the corruption, wherever it may lurk.[/quote]
    Thanks Hans. There is a lot of code in the forms. Could I copy it to a text file or a VBA storage program and then copy it back out?

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Copying code to a text file is safe - you won't copy any of the hidden stuff. (In fact, that's what the "code cleaners" for Word and Excel do - copy all code to text files, delete the modules, then create them again from the text files)

  10. #10
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    [quote name='HansV' post='800038' date='26-Oct-2009 22:48']Copying code to a text file is safe - you won't copy any of the hidden stuff. (In fact, that's what the "code cleaners" for Word and Excel do - copy all code to text files, delete the modules, then create them again from the text files)[/quote]

    Wendell may have meant use the vbe editor

    then somethink like

    SaveAsText acForm, "damaged form", "C:\Somewhere\damaged form name.txt"

    then delete real form

    compact database

    then in vbe

    LoadFromText acForm, "damaged form", "C:\Somewhere\damaged form name.txt"

    ..Always have a backup first
    Andrew

  11. #11
    4 Star Lounger
    Join Date
    Dec 2002
    Location
    Kitchener, Ontario, Canada
    Posts
    421
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks Wendell and Andrew for the suggestion and syntax about Save as Text. I ran this just now and the current corruption seems to have gone away. I will still probably rebuild the forms, but for the time being, I think I have something I can work with.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •