Results 1 to 11 of 11
  1. #1
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Here's another one.
    I have a form with a custom shortcut menu that runs from a Listbox right click.
    When I click on the listbox it is fine.
    If I right click I get the Error

    The Macro or Function Set to the Before Update or Validation Rule Property for this field
    is preventing ...... From Saving the Data in the Field.

    If I click on OK then the Pop Up Menu Appears, BUT does NOT run properly.
    If I then click on a different Row in the list box, and try again I get my custom menu OK and it works fine.
    (Sometimes I might have to do this more than once)

    After that it appears to work ok, although sometimes it goes back into the loop again.

    The form has no recordset although obviously the list boxes do.
    There are NO validation rules in place.
    There are NO Before Update events running anywhere.

    This behavior does NOT happen IF I run the same database and form using Access 2000,
    But does when I use Access 2003.

    Indeed it appears to be intermittent, although NO records are being changed.

    Anyone have any ideas as to what might be the cause?
    Andrew

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    This error message is hard to troubleshoot - it often has an entirely different cause than the wording suggests. I think we'd need to see (a stripped down and zipped copy of) the database.

  3. #3
    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='796907' date='08-Oct-2009 12:08']This error message is hard to troubleshoot - it often has an entirely different cause than the wording suggests. I think we'd need to see (a stripped down and zipped copy of) the database.[/quote]

    THAT WOULD BE PRETTY MUCH IMPOSSIBLE unfortunately.
    This form is just one from a very large and complex Access Project that links into a SQL Server 2005 database.
    The behaviour is only recent and only since we started using 2003.
    There have been no changes coding or otherwise made to the form in the last 6 months and it has been working just fine.
    The whole application has been running happily for a couple of years with no issues
    with Access 2000.

    I suspect that it is something to do with the way that the menu's are called up in 2003 but have no way of being sure.
    They seemed to change a way a lot of the events fired and also the order in 2003.

    The message also pops up before any code is started.


    I am going to try a couple of things first.

    1. Do a DECOMPILE and then COMPILE to see if it is a VBA Module issue
    2. If NO Joy I might start a NEW Project and then try a complete Import
    3. If that fails I'll test the right menus in 2007 to see if they still function
    (since that is the only part of Custom menus that MS left in 2007)

    And if that fails I will then try removing bits of code to see if it makes any difference

    Lucky Me.
    Andrew

  4. #4
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    It is entirely possible that ADP based Access database events have changed from 2000 to 2003. They were first offered in 2000, and enhanced significantly in 2002. However there is not much change from 2002 to 2003 to 2007. I suspect that your issue probably has to do with the data behind your list box, and may be a timing issue. Unfortunately, error handling in ADPs is very much dependant on SQL Server where record processing is concerned, and in this case you have a later version of SQL Server connected to an earlier version of Access. As Hans suggests, I doubt the error message is really describing what is going on. We generally do not use Projects because of these kinds of issues and instead use MDBs with ODBC linked tables.
    Wendell

  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
    [quote name='WendellB' post='796986' date='08-Oct-2009 19:45']It is entirely possible that ADP based Access database events have changed from 2000 to 2003. They were first offered in 2000, and enhanced significantly in 2002. However there is not much change from 2002 to 2003 to 2007. I suspect that your issue probably has to do with the data behind your list box, and may be a timing issue. Unfortunately, error handling in ADPs is very much dependant on SQL Server where record processing is concerned, and in this case you have a later version of SQL Server connected to an earlier version of Access. As Hans suggests, I doubt the error message is really describing what is going on. We generally do not use Projects because of these kinds of issues and instead use MDBs with ODBC linked tables.[/quote]

    I think you are correct about the timing issue.
    I tried putting a do events into one of the procedures that runs prior to the list box being used.

    The problem is probably due to the fact that most of the form has dynamically generated data filling it.
    Nothing is bound at source.
    The list box in question is dynamically populated from the selection made in another list box.
    This too is initially populated from the selection in a combo box. and the content of the initial combo box is initially populated from login details stored and checked when the form opens.
    The data sources themselves are either dynamically generated SQL or more commonly Stored Procedures or Table Functions on SQL SERVER, so possible that timeout issues do occur.
    I might try a test on a faster server to see if there is any behavior change. If there is it might point the finger at timing rather than event sequence.
    What is a pity is that Access 2000 built way before SQL 2005, coexists quite happily and causes no issues, whereas a later release of Access appears to have more issues with SQL 2005.
    Indeed our (my) present experience is that a lot of things are broken by 2003 with projects.
    I am off to try the test in 2007 to see if the improved SQL functionality there makes any difference.

    I would look at migration to 2007, if it was not for the total lack of support for any custom menus other than right mouse shortcut menus.
    Maybe when Office 2010 is released with hopefully a better facility for customising the ribbon.
    In particular VB interaction.

    If not I might have to bite the bullet and look to a fully web enabled version.
    However, that is a bucket of work for a system that works 95% of the time.

    If I find the cause I'll post it back here in case anyone else encounters similar issues.

    One of the reasons for using the project rather than linked tables is speed.
    We found that performance was increased quite significantly in the project.
    Even over routines that used an mdb and pass through queries.

    THANKS FOR SUGGESTIONS ANYWAY.
    Andrew

  6. #6
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    I have no idea of real cause and no complete solution.
    But....
    I am sure that this is a timing issue on adp.

    I have same functionality now ported into a version with Linked files (mdb).
    There are NO issues.

    I tested the adp on a faster server and less of an issue.
    Also root cause seemed to be

    If you select a row in the list box with normal mouse button and then after a slight pause
    about the same time it takes the tool tip to pop up, right click there are no issues.

    IF you try and select a row by right clicking and invoking the menu in one action then
    SOME of the time the error occurs (but not always).

    I also tried this in Access 2007 and the same issue exists, but I cannot get it to
    occur in 2000, and actually do not remember it happening with XP.
    Although only had one PC running XP and that was a long time ago.

    So no solution other than patience, but a good idea of the cause.
    Andrew

  7. #7
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    [quote name='AndrewKKWalker' post='797023' date='08-Oct-2009 15:09']I would look at migration to 2007, if it was not for the total lack of support for any custom menus other than right mouse shortcut menus.[/quote]
    Actually, we have successfully used custom toolbars in 2007, but we develop them in 2002 or 2003. However they always position directly below the ribbon, and can't be floated over a form as they could in older versions.

    One of the reasons for using the project rather than linked tables is speed.
    We found that performance was increased quite significantly in the project.
    Even over routines that used an mdb and pass through queries.
    I'm somewhat surprised by that. Our experience has been that if you tune the SQL Server to work optimally with forms and reports, as well as processes, there is virtually no difference between the performance of an MDB and an ADP. And the beauty of it is that you only have to work on the specific areas where you see performance issues. That combined with the fact that ADPs usually take significantly more development effort has steered us away from them. In addition, the last word from the Access Team was that they didn't expect to enhance the functionality of ADPs beyond what was in 2003/2007 suggests that they are low priority from Microsoft's perspective.
    Wendell

  8. #8
    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='WendellB' post='797145' date='09-Oct-2009 18:43']Actually, we have successfully used custom toolbars in 2007, but we develop them in 2002 or 2003. However they always position directly below the ribbon, and can't be floated over a form as they could in older versions.


    I'm somewhat surprised by that. Our experience has been that if you tune the SQL Server to work optimally with forms and reports, as well as processes, there is virtually no difference between the performance of an MDB and an ADP. And the beauty of it is that you only have to work on the specific areas where you see performance issues. That combined with the fact that ADPs usually take significantly more development effort has steered us away from them. In addition, the last word from the Access Team was that they didn't expect to enhance the functionality of ADPs beyond what was in 2003/2007 suggests that they are low priority from Microsoft's perspective.[/quote]

    I'll have a look at the custom toolbars again. However, I cannot get either Custom Toolbars OR Menus to appear anywhere when running under 2007.
    They are defined in the forms properties window but do not appear.
    Are there any settings I should enable to get them to work (or is this another adp issue)
    Also IF I can get them to appear, how can I totally lose the ribbon. Also the quick Access Toolbar and the Big Round Start Button.



    One of the reasons for the adp (which did take a lot of development), was that back in the days of Access 2000 when it all started, I seem to remember MS considering them to be the way to go.
    More latterly, especially with 2007, they appear to have done a total U Turn (no surprise there then) and reckon that linked tables is the way to go.

    However, the project is not for ever.
    The whole lot is being ported to web enabled application (sans Access), and as such a lot of the Project development work can be utilised because it all sits on the server ready for use by whatever means.
    Andrew

  9. #9
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts
    Does the ribbon display a category called Add-Ins? If so you may find your custom toolbars there.

    On turning off the ribbon, if you clear the Allow Built-in Toolbars checkbox under Access Options for the current database, that should hide the ribbon as well as the quick-access toolbar. I expect you've seen this, but just in case, Use existing custom toolbars and startup settings in Access 2007
    Wendell

  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='WendellB' post='797235' date='10-Oct-2009 14:31']Does the ribbon display a category called Add-Ins? If so you may find your custom toolbars there.

    On turning off the ribbon, if you clear the Allow Built-in Toolbars checkbox under Access Options for the current database, that should hide the ribbon as well as the quick-access toolbar. I expect you've seen this, but just in case, Use existing custom toolbars and startup settings in Access 2007[/quote]

    Thanks Wendell.
    I should have realised that. I was looking for some sort of Hide ribbon property.
    I assume that the Allow Built in Toolbars VBA toggle will still work then.

    I'll check it out.
    Andrew

  11. #11
    5 Star Lounger AndrewKKWalker's Avatar
    Join Date
    Apr 2001
    Location
    Cambridge, UK
    Posts
    1,020
    Thanks
    0
    Thanked 3 Times in 3 Posts
    Yep.
    Got it working OK.
    Not sure I like the layout of it though.
    Be nice to able to change the Add-Ins label for the ribbon, because that is not particularly use friendly
    I'll await the final version of Office 2010 with interest.
    Andrew

Posting Permissions

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