Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232

    Unanswered: Docmd.RunSQL error.

    Sorry to trouble you guys with possible an easy question.

    The issue I have is that I'm trying to write an error handler into a table.

    What I had so far was;

    DoCmd.RunSQL "INSERT INTO tblEventlog (EventDate, ErrorSource, " & _
    "ErrorHandler) VALUES (Now(), 'Exec Summary', " & err.Description & ");"

    It keeps throwing an error due to the err.Description.

    I've tried using a variable to manage this rather than the err.Description and also build the SQL statement into a string (called strHandler) and then had -

    DoCmd.RunSQL strHandler

    ...but this also throws the same error. Any chances you could help at all please??

    Thanks in advance.

    DaveO.

  2. #2
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Change this:

    " & err.Description & ");"

    to this
    '" & err.Description & "');"

    You need the add the single quotes in order for it to work
    Inspiration Through Fermentation

  3. #3
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    I also tried to this, but it threw an error still. If you;d like I can re-produce the error early tomorrow and post it.

    Not sure if it makes a difference but I'm using Access '97.

    Thanks for the quick response.

  4. #4
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Just for grins....
    What happens if you use Error instead of err.Description.
    Inspiration Through Fermentation

  5. #5
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Thanks for the idea, however;

    With Error.Description the error message reads,


    "Run-time error '424':

    Object required"

    Not sure what it wants though as the debug just bring up the whole SQL line.

    With the '" & Err.Description & "');" the error reads,

    "Run-time error '3075': Syntax error (missing operator) in query expression 'Microsoft Access can't find the object 'qryExec_Summar.'"

    This to me would suggest the SQL is being ignored as the code is wrong. The reason it can't find ''qryExec_Summar' is because I'm forcing the error to occur for the purposes of testing the code. The real query name is 'qryExec_Summary'.

    Any more ideas at all?

    Thanks for the help!

  6. #6
    Join Date
    Jul 2003
    Location
    Michigan
    Posts
    1,941
    Let's try a little different approach.
    Try inserting this in your code. Pay close attention to how your
    SQL string looks in your message box. That message makes it look like
    your missing a ' somewhere.

    ...
    strErr = Cstr(err.Description)
    strSql = "INSERT INTO tblEventlog (EventDate, ErrorSource, " & _
    "ErrorHandler) VALUES (Now(), 'Exec Summary', '" & strErr & "');"
    MsgBox strSql
    Docmd.RunSql strSql
    ...

    edit: I don't think the Now() looks right either, unless you are trying to insert the text "Now()". That should be "...VALUES (#" & Now() & "#, ...
    Last edited by RedNeckGeek; 02-04-05 at 08:23.
    Inspiration Through Fermentation

  7. #7
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Got it

    Your almost there RedNeckGeek,
    The problem occurs as the error message reads
    Microsoft Access can't find the object 'qryExec_Summar.'

    thus closing the quotes before the corrupted tablename and having a second set after the corrupted tablename

    Slight modification to RedNeckGeeks' code
    Code:
     strerr = CStr(Err.Description)
        'added this line to counteract ' in error string
        strerr = Replace(strerr, "'", "")
        strSQL = "INSERT INTO tblEventlog (EventDate, ErrorSource, " & _
        "ErrorHandler) VALUES (Now(), 'Exec Summary', '" & strerr & "');"
        MsgBox strSQL
        DoCmd.RunSQL strSQL

  8. #8
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    OK, now the problem is I can't use the replace function, it throws an error on compile.

    I've looked at Access help and it's talking about using Worksheet functions. Replace is in a function inside of this which I don't have the .hlp file installed.

    Great.

    I've got our someone getting the file for me, but in the short term any ideas?

    Also I tried the other code RedNeckGeek sent and I think I may know what the issue is here and I think that David has it. The error message (err.description) has 2 ' in it. Now, perhaps the code is evaluating these and thinking that I'm trying to close the text off. If this is the case then the Replace function may work, but I need to know how to use replace. Is there a reference I need to register or ...?

    Thanks again for your help guys!

  9. #9
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    ok try to change the replace function with this
    Code:
    'added this line to counteract ' in error string
        Do Until InStr(1, strerr, "'") = 0
            strerr = Mid(strerr, 1, InStr(1, strerr, "'") - 1) & _
                        Mid(strerr, InStr(1, strerr, "'") + 1, Len(strerr) - InStr(1, strerr, "'") + 1)
        Loop
    itll do the same job

  10. #10
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Spot on David, thanks very much.

    1 questions about the replace code. I understand what you're doing. You're looping through the data to find a ' then taking the code before and after it and making it then be strerr. But what does the

    Len(strerr) - InStr(1, strerr, "'") + 1)

    do?? I can understand the rest, but not this bit!

    Thanks guys, you've been great!

  11. #11
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    2 things i should of put $'s on the mids (a bad habit of mine)

    right
    Len is the length of the string
    instr returns a number based on the position of the first instance of the search string
    in this case '

    so that bit you are talking about is the length of the string to return
    i.e. since we are looking at the second half of the string we find the place of the ' add 1 to it so that is the first character in the string so the whole function works like this

    Everything up to the ' character & everthing after the ' character
    so it returns a string without that 1 character
    the loop is to make sure that all the ' characters are picked up

    hope that makes sense

  12. #12
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    If we break the statement down I have this complete ...

    Do Until InStr(1, strerr, "'") = 0
    strerr = Mid$(strerr, 1, InStr(1, strerr, "'") - 1) & _
    Mid$(strerr, InStr(1, strerr, "'") + 1, Len(strerr) - InStr(1, strerr, "'") + 1)
    Loop

    So, the Do loop is easy.

    Mid$(strerr, 1, InStr(1, strerr, "'") - 1) - Basically take everything up to the first instance of '

    Mid$(strerr, InStr(1, strerr, "'") + 1 - Take everything after the '

    Effectively we now have a string thats 1 ' less.

    Len(strerr) - InStr(1, strerr, "'") + 1)

    This I don't understand. Why put this in? We're already looping to start from character 1 anyway, so what does this do and what put a comma there??

    I might be being thick here, but I'm a bit confused on this 1 little bit, the rest I should have thought of myself.

    Thanks.

  13. #13
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    Youve just missed how the function is set up

    Mid$ works like this

    mid$(string, start, lengthofstring)

    so for the second part we have
    string = strerr
    start = InStr(1, strerr, "'") + 1 (i.e. the first character you want)
    lengthofstring = Len(strerr) - InStr(1, strerr, "'") + 1 (i.e. the length of the string - the start position)

    so the whole of the second Mid$ is
    Mid$(strerr, InStr(1, strerr, "'") + 1, Len(strerr) - InStr(1, strerr, "'") + 1)

    Hope this makes a bit more sense

  14. #14
    Join Date
    Feb 2005
    Location
    England.
    Posts
    232
    Doh.

    I missed a set of brackets and thought that that bit I asked about sat outside the mid$.

    Sorry!

    1 last thing. Why do you use $ for the mid function? I never have and wonder why you said it was a bad habit.

  15. #15
    Join Date
    Jan 2004
    Location
    Aberdeen, Scotland
    Posts
    1,067
    simple $ returns a string no $ returns a variant

    huge memory consumption for no real purpose
    a particularly bad habit of mine is to forget the $ at the end hence if you loop through code it takes a considerable larger amount of time
    give it a try and youll see what i mean

Posting Permissions

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