General discussion

  • Creator
    Topic
  • #2129876

    Access 2000 Query–Very Important

    Locked

    by sameer_koranne ·

    I am developing a small package in Access 2000 with Front end and back end both as Access and I have a techical doubt in that which is explained as below
    While using the DoCmd.OpenQuery statement in VBA of access, to supress the messages access asks we use the SetWarnings command. This command suppresses all messages given while using Append query in VBA.
    But while appending any rows, it also suppresses Error messages. The senario is as under
    I have append query which appends some records in a table having Primary key, to avoid duplicates. But my append query, is trying to update some records which may create duplicates in the table. Since, table is indexed, duplicate records will not be appened to the table. So far so good.
    If am notusing DoCmd.SetWarning fascility to suppress the messages, it will ask me to update the records, say 100. When I click Yes, to allow the append, system will try to add 100 records to table. but because of duplication of code only 50 records ar updatable. Now system will flash another message saying it cannot add 50 records due to key violation errors. And will ask to click Tes/No button there. If we click Yes, error is supressed and no error is displayed. But if we Click NO then, for every record, it will display the type of error occured. This is fine.
    NOW, if I supress all messages i have with DoCmd.SetWarning , then all messages are supressed and I am not able to catch error there and ultimately, I am not able to detect the number of records affected by the save for that instance by the particular user.
    Is there any way we can have the number of rows appended OR Trap the error message while using DoCmd.SetWarnings??

    I will be highly thankful to you if you can solve my problem as early as possible

All Comments

  • Author
    Replies
    • #3649337

      Access 2000 Query–Very Important

      by hypersoniq mcse ·

      In reply to Access 2000 Query–Very Important

      DoCmd.SetWarning False before your RunSQL statement
      DoCmd.SetWarning True after your RunSQL statement.

      this type of problem is difficult to troubleshoot without seeing the full code, but hopefully the above helps.

    • #3493446

      Access 2000 Query–Very Important

      by joe kowalski ·

      In reply to Access 2000 Query–Very Important

      I am not sure what your are doing from a business perspective however it sounds like you appending and/or batching records to the table. Hypersoniq MCSE does help to answer some of your question but if you are also looking at trapping and/or loggingyour errors here is the approach which is typically used in VB for data transfer.

      Public Sub SomeSub()

      On Error Resume Next ‘Set error Handeling to

      DoCmd.SetWarning = False

      ‘Run SQL

      If Err.Number = 0 Then ‘Check for an error number –
      ‘Contine Process
      Else
      ‘Error has occured
      LogError
      End If

      End Sub

      Private Function GetErrorString() As String
      Dim strTemp As String

      With mudtError
      strTemp = strTemp & “Error Report – ” & vbCrLf
      strTemp = strTemp & Space$(4) & “Timestamp = ” & Date & “:” & Time & vbCrLf
      strTemp = strTemp & Space$(4) & “Code = ” & Err.Number & vbCrLf
      strTemp = strTemp & Space$(4) & ERR_SOURCE & Err.SOURCE & vbCrLf
      strTemp = strTemp & Space$(4) & ERR_DESCRIPTION & Err.Description & vbCrLf
      ‘Add any other usefull info
      End With

      GetErrorString = strTemp

      End Function

      Private Sub LogError()
      Dim intFile As Integer
      Dim strFilePath As String

      On Error GoTo ErrorHandler

      strFilePath = “‘Error Long Loacation”

      intFile = FreeFile

      Open strFilePath For Append As #intFile

      With mudtError
      Print #intFile, GetErrorString
      Print #intFile,
      End With

      Close #intFile

      End Sub

    • #3492938

      Access 2000 Query–Very Important

      by sameer_koranne ·

      In reply to Access 2000 Query–Very Important

      This question was auto closed due to inactivity

Viewing 2 reply threads