Results 1 to 10 of 10
  1. #1
    New Lounger
    Join Date
    Feb 2004
    Location
    Gibson Island, Maryland, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Code and/or Settings Change Spontaneously (2003)

    I am having two separate, but similar, problems with a database I wrote for a client.

    In the first case, I have a form with a subform that has data entry mode set to "Yes". Periodically, the subform seems to randomly decide to switch to data entry "No" which makes it inoperable. Sometimes it seems to happen after the subform is re-queried, but it doesn't ALWAYS happen then. Going in, re-setting the property to "Yes" and saving the form fixes the immediate problem, but I would like to know what could be causing it to happen in the first place and how to keep it from happening again.

    The other issue involves a function which runs every time a new record created with the previously mentioned subform is saved. Twice this week the user has gotten the error message "Compile Error Syntax Error" with a line in the function highlighted and a random character inserted at the start of the line which should read "DoCmd.RunSQL strSQL". Removing the offending character and saving the function fixes the problem. The user probably adds 50 or so new records a day so it is happening fairly infrequently, but it is still annoying When it happens, something also seems to reset the "Confirm Action Queries" property under Options to "Yes" even though I have it set to "No".

    None of this seems to happen on my computer which makes troubleshooting a real chore. The user is running Win XP Pro and Office 2003 and the database is in Access 2003 with a front-end/back-end structure. Auto-correct is off.

    Has anyone seen anything similar to this? My client is incredibly understanding, but this is starting to drive both of us batty! Thanks in advance.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code and/or Settings Change Spontaneously (2003)

    Do you allow the Properties window to be displayed in all views? This is a property of forms, in the Other tab; you should set it to No for all forms, otherwise the end user can change the properties accidentally.

    You should have error handling in all your code, to prevent the Debug option from being displayed.

    From your description, it seems that you haven't applied user-level security to the database. Securing the database correctly would make it impossible for the end user to apply design changes, whether intentionally or accidentally. See for example the replies to <post#=393450>post 393450</post#> if you want to read up on user-level security.

  3. #3
    New Lounger
    Join Date
    Feb 2004
    Location
    Gibson Island, Maryland, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code and/or Settings Change Spontaneously (2003)

    Thanks for the quick response, Hans. Just as it was coming in I got a call from the user who thinks he may be causing the VBA change problem by hitting a wrong shortcut key combination. I had never run into this problem before, but now I will go back and add the suggested error handling.

    So far as the form is concerned, I do Allow Design Changes in Design View Only so I don't think the user is inadvertently changing the property. Data security has never been a big concern with this database, but I can see where it might have some advantages so far as maintaining the structural integrity of the front-end as well. I'll give it a try and see if that calms things down. Always something new to learn <img src=/S/dizzy.gif border=0 alt=dizzy width=15 height=15>

  4. #4
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code and/or Settings Change Spontaneously (2003)

    What if you give an MDE database to the user ?
    Francois

  5. #5
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code and/or Settings Change Spontaneously (2003)

    At the least, you should select Tools | Startup, and clear the Special Access Keys check box. That will prevent the user from accidentally using Alt+F11 to activate the Visual Basic Editor, and from halting VBA code.

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Code and/or Settings Change Spontaneously (2003)

    That's an excellent suggestion.

    (Note: in Access 2003, it is only possible to create an MDE from a database in Access 2003 format. You can also create and edit databases in Access 2000 and Access 2002 format, but not create an MDE from them.)

  7. #7
    New Lounger
    Join Date
    Feb 2004
    Location
    Gibson Island, Maryland, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code and/or Settings Change Spontaneously (2003)

    That might work. I need to check if one of the label reports is designed to allow the user to pick which query to use as its Record Source, but I think I could set up something to do that programatically. Thanks for the suggestion!

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,624
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Code and/or Settings Change Spontaneously (2003)

    <hr>Note: in Access 2003, it is only possible to create an MDE from a database in Access 2003 format. . . .<hr>
    That is true, but you can connect to an Access 2000 back-end from an Access 2003 front-end, so it should work OK. There may be challenges in changing the report data source in an MDE however.
    Wendell

  9. #9
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    Re: Code and/or Settings Change Spontaneously (2003)

    Helenmary,

    I have the same problem in one of our databases where one of the data entry subforms data entry settings seems to randomly change to no and the users can't input any data. If corrected, it seems to work for a while then switches back again. I have never figured this problem out. As a work around, since only one other person besides me can input this particular data, we input the data directly to the table when the data entry decides to stop working until I can re-set it. Just to let you know that you are not the only one with this problem. This database is not split as a front end / back end as yours. Typically when odd things occur, I delete the form / subform and re-build them but have not in this instance (yet).

    I will keep watching your post to see if anyone comes up with a solution.
    Regards,

    Gary
    (It's been a while!)

  10. #10
    New Lounger
    Join Date
    Feb 2004
    Location
    Gibson Island, Maryland, USA
    Posts
    6
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Code and/or Settings Change Spontaneously (2003)

    There was another thread with a similar problem that suggested adding "Me.DataEntry=True" to the code behind the Current event for the subform. I just hate fixing a problem without knowing why it WAS a problem in the first place.

Posting Permissions

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