Solved

Post Err.Number & Message into Memo Field

Posted on 2013-05-09
7
311 Views
Last Modified: 2013-05-10
What is the proper syntax to include the actual Error Msg into a Memo via an Update query.

Here is what I have so far:
        strSQL2 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = Err.Number & ' ( '& Err.Description & ')' " & _
                " WHERE tblAPList.ApNo= '" & gApNo & "'"
        curdb.Execute strSQL2

Getting syntax error msg
0
Comment
Question by:Karen Schaefer
  • 4
  • 2
7 Comments
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 39153865
strSQL2 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = Err.Number & '(' & " & Chr(34) & Err.Description & Chr(34) & ")" & _
                " WHERE tblAPList.ApNo= '" & gApNo & "'"
0
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 39153873
strSQL2 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
            Err.Number & "'" & Err.Description & "'" & _
                " WHERE tblAPList.ApNo= '" & gApNo & "'"
        curdb.Execute strSQL2
0
 

Author Comment

by:Karen Schaefer
ID: 39153874
error
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 39153888
strSQL2 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & Err.Number & " & '(' & " & Chr(34) & Err.Description & Chr(34) & ")" & _
                " WHERE tblAPList.ApNo= '" & gApNo & "'"
0
 

Author Comment

by:Karen Schaefer
ID: 39153926
Here is my code as is -   I could use your assistance in cleaning up my code.

To restate the purpose of my code is:

I am attempting to export several types of objects- based on a table contain the list of objects & object Type from 1 database into another -   The objects have been import into the ExportTool.mdb, Then repeat this process for a selected list of databases.

My current issues is

1.  The object does not exist in the receiving database, how do I modify my code to handle the import/export of this object.  ie. table is new to the receiving database I need to add it.

2.  Logging error handling - Update a table with the actual error number & message,  then allowing for the code to continue on thru the remaining items in the list of databases.

3. Clean my error handling code to allow for moving to the next item in list.  I found the code for

4. Compact & compile the receiving database after export has been processed.
Unsure how to implement this code:


' Purpose   :  Compile newly update external database
'---------------------------------------------------------------------------------------
'Shell """C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""" & " " & """Path Name of Database""" & " /x mCompile"

Public Function fCompile()
   On Error GoTo fCompile_Error

    RunCommand acCmdCompileAllModules
    DoCmd.Quit

   On Error GoTo 0
   Exit Function

fCompile_Error:

    MsgBox "Error " & Err.Number & " (" & Err.Description & ") in procedure fCompile of Module modExecuteExport"
End Function

Open in new window


Public Sub ExportDBObjects()
Dim curdb As Database
Dim gApNo As String
Dim rs As Recordset, rs1 As Recordset, rs3 As Recordset
Dim strsql As String, strSQL1 As String, strSQL2 As String, strSQL3 As String, strSQL4 As String
Dim qdf As DAO.QueryDef
Dim nObjName As String, nObjType As Integer

   On Error GoTo ExportDBObjects_Error
 
Set curdb = CurrentDb()
strsql = "Select APNo, ApDbms_Db from tblAPList WHERE Active = Yes"
 
Set rs = curdb.OpenRecordset(strsql)

    rs.MoveFirst
     Do Until rs.EOF
         gApNo = rs.Fields("apno").Value
         Debug.Print gApNo
         
         gAPFilePath = rs.Fields("apdbms_db").Value
         'Updates Version 6 and greater databases - previous versions < version 6 will not be updated
               ' Shell """C:\Program Files\Microsoft Office\OFFICE11\MSACCESS.EXE""" & " " & """ & gAPFilePath & """ & " /x mCompile"
         
         strSQL1 = "SELECT [RevMaj], [ChgMadeBy]" & _
                     " FROM TS_DB_Revisions IN '" & gAPFilePath & "'" & _
                     " WHERE ([RevMaj] Like '6')"
          
         Set rs1 = curdb.OpenRecordset(strSQL1)
                
                If rs1.RecordCount > 0 Then
                    
                    strSQL3 = "Select  ObjectName, ObjectType from tblExportList"
                    
                    Set rs3 = curdb.OpenRecordset(strSQL3)
                        rs3.MoveFirst
                            Do Until rs3.EOF
                                
                                nObjName = rs3.Fields("ObjectName")
                                nObjType = GetObjectTypeConstant(rs3.Fields("ObjectType"))
                                    
                                    'Replaces existing Database objects with revised versions.
                                    DoCmd.TransferDatabase acExport, "Microsoft Access", gAPFilePath, nObjType, nObjName, nObjName
                               
                               rs3.MoveNext
                            Loop
                End If
                
                strSQL2 = "INSERT INTO TS_DB_Revisions ( RevMaj, RevEnhancement, RevBugFix, Dt, [Desc], ChgMadeBy ) IN  '" & gAPFilePath & "'" & _
                                " SELECT Last(TS_DB_Revisions.RevMaj) AS LastOfRevMaj, Last(TS_DB_Revisions.RevEnhancement) AS LastOfRevEnhancement," & _
                                " Last(TS_DB_Revisions.RevBugFix) AS LastOfRevBugFix, Last(TS_DB_Revisions.Dt) AS LastDate," & _
                                " Last(TS_DB_Revisions.[Desc]) AS LastOfDesc, Last(TS_DB_Revisions.ChgMadeBy) AS LastOfChgMadeBy" & _
                                " FROM TS_DB_Revisions"
                curdb.Execute strSQL2
                
                strSQL2 = "UPDATE tblAPList SET tblAPList.Active = No" & _
                              " WHERE tblAPList.ApNo= '" & gApNo & "'"
                curdb.Execute strSQL2
                  
            
            rs.MoveNext
        Loop
            
   On Error GoTo 0
   Exit Sub

ExportDBObjects_Error:
    If Err.Number = 3024 Or Err.Number = 3011 Then
        strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
                    Err.Number & "'" & Err.Description & "'" & _
                        " WHERE tblAPList.ApNo= '" & gApNo & "'"
        curdb.Execute strSQL4
        
        Err.Clear
        Resume Next
   
'   ElseIf Err.Number = 3011 Then
'            DoCmd.TransferDatabase acExport, "Microsoft Access", gAPFilePath, nObjType, nObjName
'
'        Err.Clear
'        Resume Next
        
    Else
               strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
                    Err.Number & "'" & Err.Description & "'" & _
                        " WHERE tblAPList.ApNo= '" & gApNo & "'"
        curdb.Execute strSQL4
' MsgBox "Error " & Err.Number & " (" & Err.Description & _
            ") in procedure ExportDBObjects of Module modExecuteExport"
    End If
End Sub

Open in new window

0
 

Author Comment

by:Karen Schaefer
ID: 39156133
Still having issues with my error handling.  I want to be able to write to a log all error handling issues, and also if Error happens, then to clear the error and resume the code to continue looping thru the list.

Also how do Transfer database object when the object does not exists in the receiving database?

errmsg
ExportDBObjects_Error:
    If Err.Number = 3024 Or Err.Number = 3011 Then
        strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
                    Err.Number & "'" & Err.Description & "'" & _
                        " WHERE tblAPList.ApNo= '" & gApNo & "'"
        curdb.Execute strSQL4

        Err.Clear
        Resume Next

    Else
               strSQL4 = "UPDATE tblAPList SET Active = No, Failed = Yes, ErrMsg = " & _
                    Err.Number & "'" & Err.Description & "'" & _
                        " WHERE tblAPList.ApNo= '" & gApNo & "'"
        curdb.Execute strSQL4
        
        Err.Clear
        Resume Next
           
'    End If
End Sub

Open in new window

0
 

Author Closing Comment

by:Karen Schaefer
ID: 39156706
Solutions can be found on http://www.experts-exchange.com/Microsoft/Development/Q_28124674.html

Use Replace function around the data containing the quotes.
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article is a continuation or rather an extension from Cascading Combos (http://www.experts-exchange.com/A_5949.html) and builds on examples developed in detail there. It should be understandable alone, but I recommend reading the previous artic…
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…
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

895 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

13 Experts available now in Live!

Get 1:1 Help Now