Exception reporting after running a query

[MeU&Us]

hi
im using update queries, and append queries to update records from excel sheets, all i want is to view the result of successful query running.
i may need something like,
if the query runs successfully, ........
else
move unsuccessful records to a new table to follow up later, ... and the message box showing the error nature.
i need a vb code to implement this, any one pls help me...

 

[Guus2005]

Use the database object to count the number of affected records. It is not waterproof but at least it gives you a result.

Dim db as database
Dim lngRecordsAffected as long

set db = currentdb

db.Execute "Delete * from Table1"
lngRecordsAffected = db.RecordAffected

msgbox lngrecordsAffected & " records where deleted from the table", vbinformation, application.name

Enjoy!

 

[MeU&Us]

i am using this code to delete the records from table
pls tell me how to implement the above described code into it,,,,or any other code

Private Sub Command33_Click()
Dim iResponse As String
iResponse = MsgBox("You are about to delete all the Medicines record." & vbCrLf & "Currently there are " & DCount("*", "Drugs") & " Medicine records in the system." & vbCrLf & "Do you really want to proceed? ", vbYesNo + vbCritical + vbApplicationModal + vbDefaultButton1, "Attention!")
Select Case iResponse
Case vbYes:
DoCmd.TransferSpreadsheet acExport, 8, "Drugs", "C:\CFS\Backup\Backup_Medicine_Before_Deletion_" & Format(Now(), "dd-mm-yyyy_h.mm.ss AM/PM") & ".xls", True, ""
DoCmd.SetWarnings False
DoCmd.RunSQL "Delete [Drugs].* from [Drugs]"
Call MsgBox("All the Medicines have been deleted from system." & vbCrLf & "A Medicine pre deletion backup file has been created in C:\CFS\Backup\..... ", vbOKOnly + vbInformation + vbApplicationModal + vbDefaultButton1, "Operation Successful!")
Case vbNo:
' Enter your code here
End Select
End Sub

 

[Guus2005]

Private Sub Command33_Click()
   Dim iResponse As Integer
   dim db as database
   set db as currentdb

   on error resume next ' Catch the error in the code 

   iResponse = MsgBox("You are about to delete all the Medicines record." & vbCrLf & "Currently there are " & DCount("*", "Drugs") & " Medicine records in the system." & vbCrLf & "Do you really want to proceed? ", vbYesNo + vbCritical + vbApplicationModal + vbDefaultButton1, "Attention!")

   Select Case iResponse
   Case vbYes
      DoCmd.TransferSpreadsheet acExport, 8, "Drugs", "C:\CFS\Backup\Backup_Medicine_Before_Deletion _" & Format(Now(), "dd-mm-yyyy_h.mm.ss AM/PM") & ".xls", True, ""
      if err=0 then ' Caught no error. Transferspreadsheet was succesfull
          db.execute "Delete * from [Drugs]"
          if db.recordsaffected > 0 then ' Records where deleted
             MsgBox "All the Medicines have been deleted from system." & vbCrLf & "A Medicine pre deletion backup file has been created in C:\CFS\Backup\..... ", vbOKOnly + vbInformation + vbApplicationModal + vbDefaultButton1, "Operation Successful!"
         endif
      else ' Unable to backup data to be deleted, nothing deleted
         msgbox "Unable to backup data, delete aborted. Err:" & err, vbexclamation, application.name
      endif
   Case vbNo ' User pressed No, nothing will be deleted.
     ' No code here
   End Select
End Sub

You may need to debug this code.
The returnvalue from a message box is a number and not a string

Use the code tag: # when entering code in this forum.

HTH

 

[MeU&Us]

thanks, i ll give it a try,

 

[MeU&Us]

as far as delete and append query concerns, it works great, but for update query it doesnt show the desired result. it shows total update able records, but not the actual updated records.any ideas..........

 

[jdraw]

Hope this is useful, I found it while looking at a different post.

Re: Records affected

http://www.granite.ab.ca/access/currentdbrecordsaffected.htm

 

[MeU&Us]

this gives me the following error
object variable or with block variable not set

 

[Guus2005]

then you are missing an end if or an end with

HTH

 

[MeU&Us]

i am using this code, and it is giving me the error.......object variable or with block variable not set

Private Sub UpdateMedic()
Dim db As Database
If Dir("C:\CFS\Update\Update_Medicine.xls") = "" Then
Call MsgBox("Source File does not exist!" & vbCrLf & "Please Create a fresh Medicines backup, make changes to it and save it as C:\CFS\Update\Update_Medicine.xls, thee run this command again.", vbOKOnly + vbInformation + vbApplicationModal + vbDefaultButton1, "Operation Failed!")
Else
DoCmd.TransferSpreadsheet acImport, 8, "MedUpdate", "C:\CFS\Update\Update_Medicine.xls", True, ""
Dim iResponse As String
iResponse = MsgBox("You are about to update " & DCount("*", "MedUpdate") & " records to the Medicines." & vbCrLf & "There are already " & DCount("*", "Drugs") & " Medicine records in the system." & vbCrLf & "you really want to proceed? ", vbYesNo + vbCritical + vbApplicationModal + vbDefaultButton1, "Attention!")
Select Case iResponse
Case vbYes:
DoCmd.TransferSpreadsheet acExport, 8, "Drugs", "C:\CFS\Backup\Backup_Medicine_Before_Update_" & Format(Now(), "dd-mm-yyyy_h.mm.ss AM/PM") & ".xls", True, ""
DoCmd.SetWarnings False

db.Execute "UpdateMed", dbFailOnError

Call MsgBox(db.RecordsAffected & " Medicines Record have been Updated into the system." & vbCrLf & "A Medicines pre update backup file has been created in C:\CFS\Backup\..... ", vbOKOnly + vbInformation + vbApplicationModal + vbDefaultButton1, "Operation Successful!")
Case vbNo:
' Enter your code here
End Select
DoCmd.DeleteObject acTable, "MedUpdate"
End If

End Sub
-----------
any ideas

 

[Guus2005]

Use code tags(#) to make your code more readable.

Did you compile your code? (Debug|Compile ...)

HTH

 

[MeU&Us]

no... i just visited different forums to get to this code, and it works fine so far...
and how to Use code tags(#)???

 

[Guus2005]

Compile your code. It can run without compilation, it does it on the run. But sometimes skips a few lines that weren't executed.

To use the code tags: select the code and click the # icon

HTH

 

[MeU&Us]

i tried that too, no success.:-(

 

[Guus2005]

With code tags your code looks like this. This is readable.

Private Sub UpdateMedic()
    Dim db As Database
    Dim iResponse As Integer

    [COLOR="Red"]Set db = Currentdb[/COLOR]    

    If Dir("C:\CFS\Update\Update_Medicine.xls") = "" Then
        Call MsgBox("Source File does not exist!" & vbCrLf & "Please Create a fresh Medicines backup, make changes to it and save it as C:\CFS\Update\Update_Medicine.xls, thee run this command again.", vbOKOnly + vbInformation + vbApplicationModal + vbDefaultButton1, "Operation Failed!")
    Else
        DoCmd.TransferSpreadsheet acImport, 8, "MedUpdate", "C:\CFS\Update\Update_Medicine.xls", True, ""
        iResponse = MsgBox("You are about to update " & DCount("*", "MedUpdate") & " records to the Medicines." & vbCrLf & "There are already " & DCount("*", "Drugs") & " Medicine records in the system." & vbCrLf & "you really want to proceed? ", vbYesNo + vbCritical + vbApplicationModal + vbDefaultButton1, "Attention!")
        Select Case iResponse
        Case vbYes:
            DoCmd.TransferSpreadsheet acExport, 8, "Drugs", "C:\CFS\Backup\Backup_Medicine_Before_Update_" & Format(Now(), "dd-mm-yyyy_h.mm.ss AM/PM") & ".xls", True, ""
            DoCmd.SetWarnings False
            
            db.Execute "UpdateMed", dbFailOnError
        
            Call MsgBox(db.RecordsAffected & " Medicines Record have been Updated into the system." & vbCrLf & "A Medicines pre update backup file has been created in C:\CFS\Backup\..... ", vbOKOnly + vbInformation + vbApplicationModal + vbDefaultButton1, "Operation Successful!")
        Case vbNo:
            ' Enter your code here
        End Select
        
        DoCmd.DeleteObject acTable, "MedUpdate"
        
    End If

End Sub

When you get the error message, press control-break. It shows you where it finds the error. In this case you didn't instantiate the db object.


HTH

 

[MeU&Us]

same problem... the above code gives the number of records to be updated, not the actual updated records

 

[Guus2005]

That depends on the actual query.

What does "UpdateMed" look like?

 

[MeU&Us]

i am using this query.......

UPDATE Drugs INNER JOIN MedUpdate ON Drugs.GenericName=MedUpdate.GenericName SET Drugs.DrugID = [MedUpdate]![DrugID], Drugs.GenericName = [MedUpdate]![GenericName], Drugs.MedicineCategory = [MedUpdate]![MedicineCategory], Drugs.BrandName = [MedUpdate]![BrandName], Drugs.MainIndication = [MedUpdate]![MainIndication], Drugs.DosageForm = [MedUpdate]![DosageForm], Drugs.Strength = [MedUpdate]![Strength], Drugs.Frequency = [MedUpdate]![Frequency], Drugs.PharmacologyPharmacokinetics = [MedUpdate]![PharmacologyPharmacokinetics], Drugs.Contraindications = [MedUpdate]![Contraindications], Drugs.Precautions = [MedUpdate]![Precautions], Drugs.Adverseeffects = [MedUpdate]![Adverseeffects], Drugs.Interactions = [MedUpdate]![Interactions], Drugs.Instructionswarnings = [MedUpdate]![Instructionswarnings], Drugs.OtherInformation = [MedUpdate]![OtherInformation];

 

[Guus2005]

You are not trying to make it any easier do you?

UPDATE Drugs INNER JOIN MedUpdate
  ON Drugs.GenericName=MedUpdate.GenericName 
  SET Drugs.DrugID = [MedUpdate]![DrugID], 
        Drugs.GenericName = [MedUpdate]![GenericName],
        Drugs.MedicineCategory = [MedUpdate]![MedicineCategory],
        Drugs.BrandName = [MedUpdate]![BrandName],
        Drugs.MainIndication = [MedUpdate]![MainIndication],
        Drugs.DosageForm = [MedUpdate]![DosageForm], 
        Drugs.Strength = [MedUpdate]![Strength], 
        Drugs.Frequency = [MedUpdate]![Frequency],   
        Drugs.PharmacologyPharmacokinetics = [MedUpdate]![PharmacologyPharmacokinetics], 
        Drugs.Contraindications = [MedUpdate]![Contraindications], 
        Drugs.Precautions = [MedUpdate]![Precautions], 
        Drugs.Adverseeffects = [MedUpdate]![Adverseeffects], 
        Drugs.Interactions = [MedUpdate]![Interactions],
        Drugs.Instructionswarnings = [MedUpdate]![Instructionswarnings], 
        Drugs.OtherInformation = [MedUpdate]![OtherInformation];

Since you don't add a where clause every record where Drugs.GenericName=MedUpdate.GenericName is updated. Apparently this is the complete table.
So you are right when you say that the number of records affected is the complete table.

HTH