Results 1 to 10 of 10

Records get Deleted Unexpectedly

  1. #1
    joym is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    54

    Exclamation Records get Deleted Unexpectedly

    I created a db on MS Access for recording faults.
    I am facing issues when I save a faults lodged using a form. I have noticed that when creating the faults everything is recorded as it should be. However, if you close the db and later open and fill new information on the form it often it deletes or over writes a couple of the existing records.
    I did some sample records. I created 5 records which stored correctly in the table.
    Click image for larger version. 

Name:	1.jpg 
Views:	22 
Size:	29.1 KB 
ID:	28332
    I closed the db and reopened it and checked the records were as it should be. I closed the database once more and opened it and without checking the table started entering more data.
    Click image for larger version. 

Name:	2.jpg 
Views:	22 
Size:	38.9 KB 
ID:	28333
    Notice that the 1st record created by Joy TT#1 has been deleted or overwritten. In the above picture. I did another set of 5 records after closing the db and opening it. Notice again TT#1 Created by Tom has been deleted or overwritten.
    Click image for larger version. 

Name:	3.jpg 
Views:	22 
Size:	47.6 KB 
ID:	28336
    On the 3rd round of testing TT#2 and TT#3 created by Joy were deleted or overwritten.

    I am using a macro on MS Access 2013

    Click image for larger version. 

Name:	6.jpg 
Views:	22 
Size:	23.3 KB 
ID:	28334
    Attached is the Db file and user Manual. This is the first Database i have built on so there are bound to be errors and if any improvements can be done, i am open to suggestions.
    Attached Files Attached Files
    Last edited by joym; 04-20-2017 at 01:47 PM.

  2. #2
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    42,597
    If you want to provide db for analysis, can attach file. Follow instructions at bottom of my post.
    To provide db: Make copy, remove confidential data, run compact & repair, zip w/Windows Compression if over 500KB (2MB zip allowed), attach to post. Attachment Manager is below the Advanced post editor window, click Go Advanced below the Quick Reply window.
    If any suggestion resolves your issue, please use the Thread Tools dropdown above first post and mark as Solved!

    Debug!Debug!Debug! http://www.cpearson.com/excel/debug.htm

  3. #3
    joym is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    54
    Quote Originally Posted by June7 View Post
    If you want to provide db for analysis, can attach file. Follow instructions at bottom of my post.
    I have uploaded the db and included the user manual might be of help with the db.

  4. #4
    Bob Fitz is offline MS Community Contributor
    Windows 7 32bit Access 2013
    Join Date
    May 2011
    Location
    Essex UK
    Posts
    1,881
    Quote Originally Posted by joym View Post
    I have uploaded the db and included the user manual might be of help with the db.
    I can not see you upload
    If this helped, please click the star at the bottom left of this posting and add to my reputation. Many thanks.
    Not everyone can access an accdb, you may reach a broader audience by attaching in mdb format.

    Bob Fitzpatrick

    I have never failed. I have found a thousand ways that do not work!

    Microsoft Community Contributor Award (2011)



  5. #5
    davegri is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    809
    In the form load event of tt_form, you're setting all the textboxes to a zero length string (= ""). That's blanking out the first record in the table each time the form loads. Then you type in new data and the first record is replaced by that info. Take all that code out.
    If you only want to be able to enter new data on the from and not view pre-existing data, open the form in data-entry mode.
    Code:
    Option Compare Database
    Private Sub btn_TT_Click()
        DoCmd.OpenForm "TT Form", , , , acFormAdd
        DoCmd.Close acForm, Me.Name
    End Sub
    And you should add "Option Explicit" after "Option Comapre Database" in all your code. It will show that you have several undefined variables in your code that need to be fixed.
    Last edited by davegri; 04-21-2017 at 07:45 AM.

  6. #6
    joym is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    54
    Quote Originally Posted by Bob Fitz View Post
    I can not see you upload
    It is attached right at the botton .

    Click image for larger version. 

Name:	Untitled.jpg 
Views:	13 
Size:	158.4 KB 
ID:	28345

  7. #7
    joym is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    54
    Quote Originally Posted by davegri View Post
    In the form load event of tt_form, you're setting all the textboxes to a zero length string (= ""). That's blanking out the first record in the table each time the form loads. Then you type in new data and the first record is replaced by that into. Take all that code out.
    I did that on load so that all fields are empty when opened

    If you only want to be able to enter new data on the from and not view pre-existing data, open the form in data-entry mode.
    Code:
    Option Compare Database
    Private Sub btn_TT_Click()
        DoCmd.OpenForm "TT Form", , , , acFormAdd
        DoCmd.Close acForm, Me.Name
    End Sub
    [/QUOTE]
    Quote Originally Posted by davegri View Post
    And you should add "Option Explicit" after "Option Compare Database" in all your code. It will show that you have several undefined variables in your code that need to be fixed.
    I will try your suggestion and hope if works. I created a new db with a table with 1 field to enter a number and bound form with the ms access records operation add new record to save the input to the table. The same issue occurred if i closed the form and opened it the first record got deleted.

    I am using MS Office Professional Plus Access 2013 64bit.

  8. #8
    davegri is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    809
    The same issue occurred if i closed the form and opened it the first record got deleted.
    Yes. When you open a form with a recordsource, the first record is normally displayed. Trouble is, you blanked it out with the Form_Load event. THAT'S the problem. See post #5 again.

  9. #9
    joym is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Feb 2017
    Posts
    54
    Quote Originally Posted by davegri View Post
    Yes. When you open a form with a recordsource, the first record is normally displayed. Trouble is, you blanked it out with the Form_Load event. THAT'S the problem. See post #5 again.
    i understand that this only works when i open the TT form using the TT Button on the Main Menu Form. If i directly open the TT Form then it pulls the 1 record from the table and if i enter anything it will over write the existing one. How can i avoid this on the TT form if i open the TT form directly.

    And Thank you very much for the help. appreciate it

  10. #10
    davegri is offline Expert
    Windows 10 Access 2016
    Join Date
    May 2012
    Location
    Denver
    Posts
    809
    See post #5 again. DON'T BLANK OUT THE RECORD. T H A T * I S * T H E * P R O B L E M.
    Then if you want to enter a new record, hit the Lodge Fault button for a blank form to fill out.
    Last edited by davegri; 04-21-2017 at 07:48 AM.

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

Similar Threads

  1. Records being deleted
    By RikkiHolland in forum Access
    Replies: 1
    Last Post: 03-07-2017, 01:47 PM
  2. Deleted records not actually deleted
    By soco3594 in forum Forms
    Replies: 6
    Last Post: 11-17-2015, 06:18 PM
  3. Tracking deleted records
    By data808 in forum Access
    Replies: 3
    Last Post: 03-30-2014, 01:34 AM
  4. ##deleted records##
    By FJM in forum Access
    Replies: 5
    Last Post: 10-23-2013, 07:08 AM
  5. Records unexpectedly locked
    By always404 in forum Forms
    Replies: 9
    Last Post: 01-31-2013, 04:05 PM

Tags for this Thread

Posting Permissions

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