Results 1 to 6 of 6
  1. #1
    stickhog is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    4

    Disappearing Data in Form

    Access 2010 14.0.7128.5000 (32-bit)



    I have a split database where the backend file with the data resides on a server. The frontend files (ACCDE) with the entry forms reside on individual user's computers, ~12 users. At most maybe 6-8 users at any one time. The backend database is password-protected and only I as the admin can open it directly. Clients are set up for record-level locking, although it appears that it's often forced to page-level locking unfortunately.

    A couple of the users have come across an intermittent issue that I have been unable to reproduce or to see happen myself. They will enter data into several fields for one record using the forms, these fields include Text, Memo, Date/Time, and a Multi-select Combo Box. As they're entering data and tabbing through the fields, the data in previously entered fields will suddenly blank. They have not changed to a different record yet to force a save, so all the data entered into the form is lost.

    This has happened to two different users, nobody else has seen it. One of those users has the frontend file on their computer. The other user was on a conference room computer and was therefore using the frontend file while it was on a server. I've attempted to reproduce this by opening two frontend files in two locations on my computer and trying to edit the same record. But it always seems to be protected by the record/page-level locking. Still, the way our database is set up, it's unlikely that anybody is trying to edit the same record at the same time. I'm not sure if this is a locking issue.

    Anyone have any ideas about the cause of the form blanking? I have noticed that an accidental Undo will completely erase a form if it hasn't been saved yet. But at this point I'm operating on the assumption that that wasn't the cause as one of the users that had this happen is pretty savvy. One of the times it occurred *may* have happened after data was entered into a number of text/memo fields, and then the multi-select combo box was opened to select items, which then caused the data to disappear.

    Thanks for any help.

  2. #2
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Quote Originally Posted by stickhog View Post
    ...Anyone have any ideas about the cause of the form blanking?...
    My only experience with this is when the client machine's memory is being taxed. Perhaps a look at the form's Recordset size is worth taking a look at. One approach might be to use subforms within your form and or use Me.Dirty = False in a couple of the control's After Update event.

  3. #3
    stickhog is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    4
    Sorry, I should've added that I'm an Access newbie, so I don't fully understand your recommendations.

    1) I believe when you're referring to the Recordset size you mean the number of records that a particular form is accessing. They're probably around 100 records. Each record has 23 fields, 6 of which are memos, the rest are either text, number, date/time, and one multi-select combo box. If memory is an issue, then it seems that I could try using a form that has all records as its recordset, which at this point is ~1300 records, as opposed to the forms the users have been working in that contain only a subset of all records. If that's the case, then I should be able to reproduce the problem they're having if I tried using the full recordset in a form?

    2) How might a subform be of benefit here?

    3) How would adding a couple Me.Dirty = False statements help? I'm not familiar with VBA or SQL.

    Thanks.

  4. #4
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    1) I believe when you're referring to the Recordset size you mean the number of records that a particular form is accessing. They're probably around 100 records. Each record has 23 fields, 6 of which are memos, the rest are either text, number, date/time, and one multi-select combo box. If memory is an issue, then it seems that I could try using a form that has all records as its recordset, which at this point is ~1300 records, as opposed to the forms the users have been working in that contain only a subset of all records. If that's the case, then I should be able to reproduce the problem they're having if I tried using the full recordset in a form?
    Data types like memo, text, and date are large in comparison to some other data types. 23 columns is not small. 100 records is not large at all.

    Retrieving more records into the form's recordset may help to reproduce the symptom. So increasing it from 100 to 1300 would require additional memory. To further test the memory theory you could try tabbing and editing data rapidly. The fact that only a couple users are experiencing the symptom may indicate someone is working hard. I usually budget extra money for power user's equipment. You could also have additional programs running in the background like, outlook, paint, internet explorer, etc. The more graphic heavy, the more memory required.



    2) How might a subform be of benefit here?
    My understanding is that subforms will be allocated their own memory because they are an additional object within the stack. Because Access is likely a 32bit system, you are not likely to see a huge benefit. However, it may be worth a try if you are running out of options. It is just something that I threw out there and would try myself.


    3) How would adding a couple Me.Dirty = False statements help? I'm not familiar with VBA or SQL.
    You can save a record from a form using VBA

    Code:
    If Me.Dirty = True then
    Me.dirty = false
    End if
    As a user makes changes to fields, their machine will store these changes in memory until the record is saved. Freeing memory as you go could be helpful. However, if the user depends on tools like Undo, they may be in for a surprise.

    The ultimate solution may be lots of VBA, restructuring the tables, or both.

  5. #5
    stickhog is offline Novice
    Windows 7 64bit Access 2010 32bit
    Join Date
    Sep 2014
    Posts
    4
    Thanks for the clarification.

    To be sure I understand, those 3 lines of VBA code say if the record is dirty, then save the changes made in this form up to this point? So then if I place that after say 1/3 and 2/3 of the way through the form, it'll force a save once the user passes those 1/3 and 2/3 entry points? But then that removes the ability to undo anything done before those save points?

    Aside from being unable to undo and maybe a performance hit, is there a problem with doing that after every control?

  6. #6
    ItsMe's Avatar
    ItsMe is offline Sometimes Helpful
    Windows 7 64bit Access 2010 32bit
    Join Date
    Aug 2013
    Posts
    7,862
    Those three lines of code check to see if the record has the ability to be saved, then, if it does, the record is saved. So , yes.

    There should not be a problem placing that VBA in every control's after update event, other than effectively disabling Undo.

    At least if the symptom goes away, you will know why the symptom existed in the first place.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Disappearing Changes in Navigation Form
    By rjgriffin46 in forum Access
    Replies: 1
    Last Post: 07-29-2014, 12:38 PM
  2. Chart and Data Disappearing in Design View
    By bigchicagobob in forum Reports
    Replies: 7
    Last Post: 04-07-2014, 11:33 AM
  3. LOGIN Form not disappearing
    By AussieAsh in forum Forms
    Replies: 7
    Last Post: 02-18-2013, 05:06 PM
  4. Disappearing data in combo box
    By Casey Sanders in forum Forms
    Replies: 1
    Last Post: 01-04-2013, 04:16 PM
  5. Replies: 4
    Last Post: 09-05-2012, 08:43 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums