Solved

Multiple errors in deleting a record in MS Access database

Posted on 2010-09-15
22
564 Views
Last Modified: 2013-11-28
In my attached Access database, I have a listbox on frmPatient that I populate with a new Patient, as their info is entered. Listbox is supposed to show only those patients who's records have NOT yet been printed. Once the patient record has been printed, the patient name is marked as such & is automatically deleted from the listbox. However, eversince I implemented the listbox feature to navigate unPrinted records, I frequently run into the following 2 issues.
Go to the Main form (switchboard), click on Patients & then walk through the following procedure:
1) Open the Patients Window fresh, w/ multiple names already in the List Box
2) click a name (the corresponding record must include hospital data, I think); the name becomes outlined
3) click Delete button
4) this creates one of two types of error:
      a) "Update or CancelUpdate without AddNew or Edit"
      b) "MS Access has encountered a problem and needs to close"

 The interesting thing is that the record DOES get deleted, but doesnt reflect so on the form. Maybe, this might have something to do with the Me.Requery & Me.Refresh that I have at the end of the DELETE button code. Upon commenting those 2, I am get "Update or CancelUpdate without AddNew or Edit". Database attached, help is highly appreciated. Please make a copy of the DB, before attempting ...in case it crashes, you dont have to redownload.
TEST-for-Marty.zip
0
Comment
Question by:datasolutionz
  • 13
  • 8
22 Comments
 
LVL 7

Expert Comment

by:shaydie
ID: 33684322
Hi,

I must not be following something correctly.. I can't find the listbox you are talking about.
0
 
LVL 6

Expert Comment

by:UsamaFoad
ID: 33684335
I think this is part of the solution, you need to check if the record was deleted frist.
I'll test it again.
Private Sub CheckIfInLstBox()



Dim i As Integer

For i = 0 To lstUnPrintedRecs.ListCount - 1

If lstUnPrintedRecs.Column(0, i) <> "#Deleted" Then

    If Me.PK_Patient = CLng(lstUnPrintedRecs.Column(0, i)) Then

        lstUnPrintedRecs.Selected(i) = True

    End If

End If

Next



End Sub

Open in new window

0
 

Author Comment

by:datasolutionz
ID: 33684481
shaydie - From the Main switchboard, click on PATIENTS. On the PATIENTS form (frmPatient), look in the bottom right corner...it says UNPRINTED FORMS right above the listbox.

Usama - Adding the "#Deleted" did not work, I tested it. Access breaks down & has to restart
0
 
LVL 7

Expert Comment

by:shaydie
ID: 33684791
Ahhh.. I see :)

Well the only problem I had with it was when we run into the #Deleted record from the listbox as well. Usama's suggestion I would think should have worked thoug.. Maybe rather than add something in the CheckIfinlistbox sub.. maybe just try requerying the listbox before requerying the form in the cmdDelete click event..

Add 'lstUnPrintedRecs.Requery' right before requering the form and see if that works.. It seems to work fine for me.. if not there may be something else going on that I can't recreate. If it doesn't work.. can you step through the code and see where it fails?

Private Sub cmdDelete_Click()
On Error GoTo Err_cmdDelete_Click

Dim intAnswer As Integer

intAnswer = MsgBox("Are you sure you want to DELETE this record?", vbYesNo, "Delete?")
If intAnswer = vbNo Then
    MsgBox "Delete cancelled", vbInformation, "Cancelled"
    Exit Sub
End If
   


    DoCmd.DoMenuItem acFormBar, acEditMenu, 8, , acMenuVer70
    DoCmd.DoMenuItem acFormBar, acEditMenu, 6, , acMenuVer70
lstUnPrintedRecs.Requery
Me.Requery
Me.Refresh

Exit_cmdDelete_Click:
    Exit Sub

Err_cmdDelete_Click:
    MsgBox Err.Description
    Resume Exit_cmdDelete_Click
   
0
 

Author Comment

by:datasolutionz
ID: 33686566
shaydie - It seems like it breaks at different places, but usually in the code behind the DELETE button, the culprit lines are probably one or more of the following:
Me.Refresh
lstUnPrintedRecs.Requery
Me.Requery
Me.Refresh
Also, the error varies depending on which record in the listbox is selected, usually if the last record is selected, works just fine...no issues. Please step through the code (put a break point on OnCurrent event of frmPatient) & see if you can point to anything. I am sure it is something quite small...but crucial.
0
 
LVL 7

Expert Comment

by:shaydie
ID: 33687520
Hi again,

So you did add the 'lstUnPrintedRecs.Requery' line and it still errors/crashes?
after adding this line it works fine and I have no issues deleting a record.

Try it from the db I am uploading.. do you still have errors?
TEST2.zip
0
 

Author Comment

by:datasolutionz
ID: 33688995
I am still getting the "Update or CancelUpdate without AddNew or Edit" error on deleting records in the listbox. I can select the record in the listbox, click on DELETE & it seems like everything works, however, after you click anywhere else on the form, you get the message of "Update or CancelUpdate....etc". Remember, the objective is to be able to select a record FROM THE LISTBOX & delete that record. There are no issues with deleting records that are not in the listbox (i.e. records that have been printed).
0
 

Author Comment

by:datasolutionz
ID: 33692937
I am increasing the # of points alloted. While I appreciate everyone's efforts, I was really hoping DatabaseMX would take notice of this post!
0
 
LVL 7

Expert Comment

by:shaydie
ID: 33696765
That is strange.. like I said I have no issues after deleting a record selected from the listbox. I can't seem to recreate the error even clicking somewhere else on the form. I have stepped through your code and don't see anywhere you are updating any data in the events that follow the requery. My question to you is.. how are you using this database. Is this the actual database as is or does the data live either in a BE database or SQL server and you added the tables in to make it standalone? Do multiple users share the database and how?
0
 

Author Comment

by:datasolutionz
ID: 33701877
It is standalone on a local pc. I am quite surprised you aren't able to recreate the situation.  Like I said, it seems like the delete works, but if you click elsewhere, you will get the addnew.....
0
 

Author Comment

by:datasolutionz
ID: 33714034
shaydie - this is a standalone on a local PC, it is the actual database & it does not live anywhere else, single user. So you are confirming that after you select a record from the listbox and delete it, you are able to navigate within the application without any issues? Could it be due to some setting on my Access installation or some reference/s?
0
IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 7

Expert Comment

by:shaydie
ID: 33718286
Hmmm.... very puzzling.. Yes, I have no issues whatsoever after deleting a record selected from the listbox.

The "MS Access has encountered a problem and needs to close"  would lead me to think there may be a problem with your installation or some kind of corruption in the db, something along those lines.. the  "Update or CancelUpdate without AddNew or Edit" would lead me to believe you are manipulating a recordset.. which I don't see happening in any event that follows deleting the record. Since it is standalone db I can't see where the issue is coming from. As a test run the db I have just uploaded (in Test.zip.) I made a couple minor changes, decompiled, recompiled and ran compact/repair several times. See if you still have the same issue.
TEST.zip
0
 

Author Comment

by:datasolutionz
ID: 33719398
same issue, but I made some minor progress. Took your code did the following:

1. Added the IF condition If lstUnPrintedRecs.Column(0, i) <> "#Deleted" Then.... to sub "DeSelectlstUnPrintedRecs"
2. Modified the DELETE code by commenting Me.Requery & Me.Refresh.

Now, the 2nd error ("MS Access has encountered a problem and needs to close") seems to be gone, but the 1st error ""Update or CancelUpdate without AddNew or Edit" seems to be consistent. My gut feeling is that it has something to do with application trying to save a Record or Recordset, after the DELETION occurs. Can we get in touch via phone/email?
0
 

Author Comment

by:datasolutionz
ID: 33720011
Just to ensure you understand, the DELETE actually happens & the selected name/record disappears from the lstUnPrintedRecs, but if you click on anywhere other in the form, on any control, you get the "Update or CancelUpdate....".
Also worth noting is that the error does NOT happen if the error being deleted is the LAST one appearing in lstUnPrintedRecs. To replicate, add about 3 or 4 new records (just first name & last name), scroll to the very last record added (should be the last record in the database also) via the navigation buttons & click the Delete button...no error encountered.
0
 

Author Comment

by:datasolutionz
ID: 33720369
Correction to 1st line in 2nd paragraph above:
*** Also worth noting is that the error does NOT happen if the RECORD being deleted is the LAST one appearing.....
0
 
LVL 7

Expert Comment

by:shaydie
ID: 33720855
Okay.. so.. I have not been receiving any errors when deleting, but I have been using Access 2007. So I had a thought and I tried it on another computer using Access 2000. I was able to recreate the error’s you mention in that version. The requery in the cmdDelete click event is redundant.. Form_current and all the subs called from there run when the record is deleted and again when you requery. Basically doing that everything runs twice. So I would remove the me.requery line from cmdDelete click event anyway. What I changed that seems to have fixed the 'Update or CancelUpdate without AddNew or Edit' error (at least here for me) is to have the listbox requery in the form current event before the subs that select and deselect list items run. After adding this line it seems to work fine.

Add lstUnPrintedRecs.Requery before  DeSelectlstUnPrintedRecs in the Form_Current event
Like this:

    DropDownsVisible
    Me.lstUnPrintedRecs.Requery
    DeSelectlstUnPrintedRecs
    CheckIfInLstBox

Try that and see if that fixes the problem for you as well.
0
 

Author Comment

by:datasolutionz
ID: 33722696
Great, can't wait to try it tomorrow morning. Will let you know.
0
 

Author Comment

by:datasolutionz
ID: 33728981
shaydie - thanks for your guidance. The solution did not work exactly as outlined, however, with some tweaks n modifications, I finally have it working. HOWEVER, hold your breath (lol), when a record form the listbox gets deleted, the word "#DELETED" appears and remains in its place...until you click in the listbox. This is when it seems that the listbox has requeried itself ...even though the Me.lstUnPrintedRecs.Requery is the OnCurrent code (as you advised). If I step through the code (F8), the problem doesnt happen, ie, I can see the "#DELETED" disappearing after the requery. So it works fine if stepping through the code but not otherwise. My latest DB is attached....please step through & let me know if you can figure this out.
TEST09212010.zip
0
 

Author Comment

by:datasolutionz
ID: 33729010
i tried placing DoEvents before Me.lstUnPrintedRecs.Requery, but that did not work,
0
 
LVL 7

Expert Comment

by:shaydie
ID: 33730352
Funny.. there was no hanging on #DELETED# in my version, but your's there sure is. I looked to see what difference there was. I already had a requery on the listbox in the cmdDelete event.. and then added one to form current. So I added that in to your version and it seems to work fine. I don't know exactly why you would need to requery the listbox twice.. but it seems to solve the problem of removing #DELETED#.

Here is the version you uploaded most recently with the additon of the listbox requery in the cmdDelete click event. Give it a try and see if that works for you..
Edited-TeleRad-DB-09162010.zip
0
 
LVL 7

Accepted Solution

by:
shaydie earned 250 total points
ID: 33758335
Were you able to give that a try? Did it work for you?
0
 

Author Comment

by:datasolutionz
ID: 33759735
Yes, it did. Sorry, I was preoccupied with some personal issues. Thanks for your patience & persistance!
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…

758 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now