Results 1 to 10 of 10
  1. #1
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Message locating the source of the error. (Access 2000)

    I am appending and updating tables from different sources.Can i find a method to locate the source where the error occurs? For example the first source is called DepotSalzburg, with a constant for the path DSa, rtc.My question is, how shall i know that the error has occcured with the DepotSalzburg and not with the next depots? I need some message to indicate that the error has occured with this depot.The errors occur when the tables are not the same,and somebopdy has changed them
    erroneously.
    Below are parts of my functions. These functions are the result of the advices of Hans from the present Forum and work excellent.I just want to make my work easier and pinpoint immdeiately the guilty location.

    Public Function Collect()
    CollectOne (DSa)
    CollectOne (DVa)
    CollectOne (DBl)
    CollectOne (DHa)
    CollectOne (DPl)
    CollectOne (DTa)
    CollectOne (DTr)
    CollectOne (DRs)
    CollectOne (DSz)
    CollectOne (dbs)
    End Function

    Public Sub CollectOne(strDatabaseName As String)
    If Dir(strDatabaseName, vbNormal) <> "" Then
    ImportAllTables (strDatabaseName)
    ProcessTables
    Kill strDatabaseName
    Else
    DoCmd.Beep
    End If
    End Sub


    Public Function ProcessTables()
    AppendToTable "products1", "products", "productID"
    UpdateTable "products1", "products", "ProductID"
    End Function

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message locating the source of the error. (Access 2000)

    Try adding an error handler to CollectOne:

    Public Sub CollectOne(strDatabaseName As String)
    On Error GoTo ErrHandler

    If Dir(strDatabaseName, vbNormal) <> "" Then
    ImportAllTables strDatabaseName
    ProcessTables
    Kill strDatabaseName
    Else
    DoCmd.Beep
    End If
    Exit Sub

    ErrHandler:
    MsgBox "An error occurred while processing " & strDatabaseName, vbExclamation
    End Sub

  3. #3
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message locating the source of the error. (Access 2000)

    Thank you for your reply.The error handle somehow didnt give the message.Do you think i may try with find unmatched query ? Because the error sometimes is that the products in the table product1 are less than in table products.So if the sql shows unmatched proucts to give up the warning that in the database there is a problem and to proceed further.My idea of the function is the following:
    Public Sub CollectOne(strDatabaseName As String)
    If Dir(strDatabaseName, vbNormal) <> "" Then
    ImportAllTables (strDatabaseName)
    'find unmatched products :
    Dim sql As String
    sql = " SELECT products.Productid, products1.Productid FROM products LEFT JOIN products1 ON products.Productid = products1.Productid " & _
    " WHERE (((products1.Productid) Is Null))"

    Then somehow to write that if the sql contains such product, to give the warning

    ProcessTables
    Kill strDatabaseName
    Else
    DoCmd.Beep
    End If
    End Sub


    What is your opinion ?

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message locating the source of the error. (Access 2000)

    I'm not sure I understand what you are doing, but you could do the following:
    - Use the Find Unmatched Query Wizard to create the query you describe.
    - Name it (for example) qryUnmatched.
    - Use DCount in your code to check if there are unmatched records:

    If DCount("*", "qryUnmatched") > 0 Then
    MsgBox "There are unmatched records", vbInformation
    Else
    ...
    End If

  5. #5
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message locating the source of the error. (Access 2000)

    Thank you.IMy function shows run time error 3078: "Microsoft Jet database engine cannot find input table or query "qrynmatched".Make sure it exists": :
    Dim qryUnmatched As String
    qryUnmatched = " SELECT products.Productid, products1.Productid FROM products LEFT JOIN products1 ON products.Productid = products1.Productid " & _
    " WHERE (((products1.Productid) Is Null))"
    If DCount("*", "qryUnmatched") > 0 Then
    MsgBox "There are unmatched records",

    vbInformation
    Else
    DoCmd.Beep
    End If
    Also,my query shows for example that the unmatched number of the product is number 463.Could it also be shown in the message?

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message locating the source of the error. (Access 2000)

    You should create qryUnmatched in the interface, not in code. The Find Unmatched Query Wizard is the easiest way to do so.

    You can remove the lines

    Dim qryUnmatched As String
    qryUnmatched = " SELECT products.Productid, products1.Productid FROM products LEFT JOIN products1 ON products.Productid = products1.Productid " & _
    " WHERE (((products1.Productid) Is Null))"

    They make no sense.

    You could use the following:

    Dim lngCount As Long
    lngCount = DCount("*", "qryUnmatched")
    If lngCount > 0 Then
    MsgBox "There are " & lngCount & " unmatched records."
    End If

  7. #7
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message locating the source of the error. (Access 2000)

    The code you have suggest works fine.After the message i get another message from Access which i want to avoid :
    "The changes you requested to the table were not sucessful because they would create duplicate values in the index,primary key or relationship.Change the data in the fields that contain duplicate data"
    I do not need this message, since the message from my code is more inormative.
    Secondly, i would like to know the source of the error.Maybe i could use the second column of the table products. The table products1 consists of 2 columns: productid and branch.For example,if the table products1 is from Salzburg it will consist of :
    productid branch7
    1 6
    For example if the the table products1 is from Frankfurth it will consists of :
    productid branch6
    1` 5
    Could i use in my message box also the number of the branch ?

  8. #8
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message locating the source of the error. (Access 2000)

    If you find that there are unmatched records (using DCount), you should display a MsgBox and exit the procedure (Exit Sub).

    You already know the strDatabaseName when the error occurs, so you don't need the branch number too.

  9. #9
    3 Star Lounger
    Join Date
    Jul 2001
    Posts
    280
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Message locating the source of the error. (Access 2000)

    Dear Hans,
    Thak you very much for all your precious advices and recommendation.With your help in each step, i have finally reached the working code which is:
    If Dir(strDatabaseName, vbNormal) <> "" Then
    On Error GoTo ErrHandler
    ImportAllTables (strDatabaseName)
    ErrHandler:
    MsgBox "There is an error in " & strDatabaseName, vbExclamation
    CheckUnmatchedProducts
    Exit Sub


    Public Function CheckUnmatchedProducts()
    Dim lngCount As Long
    lngCount = DCount("*", "qryUnmatched")
    If lngCount > 0 Then
    MsgBox "There are " & lngCount & " unmatched records "

    End If
    End Function
    I receive at first the message that there is a mistake in the database under question and the second message says how many records are not matched.Which is OK and i am very much pleased with that.
    Do you think it is possible to unify the two messages into one, saying that the error is so many unmatched records in the table products ?

  10. #10
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Message locating the source of the error. (Access 2000)

    You'd have to put the code of CheckUnmatchedProducts in the calling procedure itself:

    If Dir(strDatabaseName, vbNormal) <> "" Then
    On Error GoTo ErrHandler
    ImportAllTables strDatabaseName
    End If
    Exit Sub
    ErrHandler:
    Dim lngCount As Long
    lngCount = DCount("*", "qryUnmatched")
    If lngCount > 0 Then
    MsgBox "There are " & lngCount & " unmatched records in " & strDatabaseName, vbExclamation
    End If
    End Sub

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •