Could someone help with a RunTime Error

SunnySide · January 17th, 2012, 01:19 PM

I'm trying to write new code. I have to admit I've never been in traditional training...can you tell me what I'm doing wrong?

thanks!

Code:

Function AppendCBNR()
On Error GoTo m_Err
Dim mySQL As String


    DoCmd.SetWarnings False
    mySQL = "INSERT INTO Chrgback_NonReversal ( [CBVND#], CBTHED, CBTHAM, CBNSRF, CBTHAF, CBNRDT, CBRAMT, CBNRAM, CBCRNO, CBCRSQ, CBCUST, [CBORD#], CBORLN )"
    mySQL = mySQL & "SELECT AUBLIB_FPCBNRDT.[CBVND#], AUBLIB_FPCBNRDT.CBTHED, AUBLIB_FPCBNRDT.CBTHAM, AUBLIB_FPCBNRDT.CBNSRF, AUBLIB_FPCBNRDT.CBTHAF, AUBLIB_FPCBNRDT.CBNRDT, AUBLIB_FPCBNRDT.CBRAMT, AUBLIB_FPCBNRDT.CBNRAM, AUBLIB_FPCBNRDT.CBCRNO, AUBLIB_FPCBNRDT.CBCRSQ, AUBLIB_FPCBNRDT.CBCUST, AUBLIB_FPCBNRDT.[CBORD#], AUBLIB_FPCBNRDT.CBORLN"
    mySQL = mySQL & "FROM AUBLIB_FPCBNRDT;"
    
  On Error GoTo 0

       db.Execute mySQL, dbFailOnError


    DoCmd.SetWarnings True
    Beep
    MsgBox "CBNR Table Complete!", vbOKOnly, ""


m_Exit:
    Exit Function

m_Err:
    MsgBox Error$
    Resume m_Exit

End Function

HiTechCoach · January 18th, 2012, 02:27 AM

You need to add a space in some places

for example:
mySQL = mySQL & "SELECT ...
and
mySQL = mySQL & "FROM AUBLIB_FPCBNRDT;
needs to be

mySQL = mySQL & " SELECT ...

and

mySQL = mySQL & " FROM AUBLIB_FPCBNRDT;

Try:

Code:

Function AppendCBNR()
On Error GoTo m_Err
Dim mySQL As String


    DoCmd.SetWarnings False
    mySQL = "INSERT INTO Chrgback_NonReversal ( [CBVND#], CBTHED, CBTHAM, CBNSRF, CBTHAF, CBNRDT, CBRAMT, CBNRAM, CBCRNO, CBCRSQ, CBCUST, [CBORD#], CBORLN )"
    mySQL = mySQL & " SELECT AUBLIB_FPCBNRDT.[CBVND#], AUBLIB_FPCBNRDT.CBTHED, AUBLIB_FPCBNRDT.CBTHAM, AUBLIB_FPCBNRDT.CBNSRF, AUBLIB_FPCBNRDT.CBTHAF, AUBLIB_FPCBNRDT.CBNRDT, AUBLIB_FPCBNRDT.CBRAMT, AUBLIB_FPCBNRDT.CBNRAM, AUBLIB_FPCBNRDT.CBCRNO, AUBLIB_FPCBNRDT.CBCRSQ, AUBLIB_FPCBNRDT.CBCUST, AUBLIB_FPCBNRDT.[CBORD#], AUBLIB_FPCBNRDT.CBORLN"
    mySQL = mySQL & " FROM AUBLIB_FPCBNRDT;"
    

' send the SQL to the immediate window ( ctrl-g) 

Debug.Print  mySQL



  On Error GoTo 0

       db.Execute mySQL, dbFailOnError


    DoCmd.SetWarnings True
    Beep
    MsgBox "CBNR Table Complete!", vbOKOnly, ""


m_Exit:
    Exit Function

m_Err:
    MsgBox Error$
    Resume m_Exit

End Function

To help debug this SQL you can go to the immediate windows and copy and paste the SQL to the Query Designer in SQL view. Switch back to Design view to see if there are any errors.

SunnySide · January 18th, 2012, 10:42 AM

Okay I see where you added spaces.....I've added your code to the VBA Module (I know it says SQL, but it's VBA in Access) and it errors on

Code:

db.Execute mySQL, dbFailonError

And when I look at the "Immediate" Window it provides the insert code...

Code:

 INSERT INTO Chrgback_NonReversal ( [CBVND#], CBTHED, CBTHAM, CBNSRF, CBTHAF, CBNRDT, CBRAMT, CBNRAM, CBCRNO, CBCRSQ, CBCUST, [CBORD#], CBORLN )  SELECT AUBLIB_FPCBNRDT.[CBVND#], AUBLIB_FPCBNRDT.CBTHED, AUBLIB_FPCBNRDT.CBTHAM, AUBLIB_FPCBNRDT.CBNSRF, AUBLIB_FPCBNRDT.CBTHAF, AUBLIB_FPCBNRDT.CBNRDT, AUBLIB_FPCBNRDT.CBRAMT, AUBLIB_FPCBNRDT.CBNRAM, AUBLIB_FPCBNRDT.CBCRNO, AUBLIB_FPCBNRDT.CBCRSQ, AUBLIB_FPCBNRDT.CBCUST, AUBLIB_FPCBNRDT.[CBORD#], AUBLIB_FPCBNRDT.CBORLN  FROM AUBLIB_FPCBNRDT;

Any more suggestions?

HiTechCoach · January 19th, 2012, 01:14 AM

Quote:

To help debug this SQL you can go to the immediate windows and copy and paste the SQL to the Query Designer in SQL view. Switch back to Design view to see if there are any errors

Did you try this?

SunnySide · January 19th, 2012, 01:51 PM

I did and everything is still the same. No difference.

HiTechCoach · January 20th, 2012, 06:46 PM

Quote:

Originally Posted by SunnySide

I did and everything is still the same. No difference.

Did the SQL generate any errors in the query design?

Was there an error when you ran the SQL in the query designer?

SunnySide · January 23rd, 2012, 02:51 PM

When it errors it highlights the "db.Execute mySQL, dbFailonError"

My understanding is the immediate window provides the code it's questioning and places a correct version. I copy pasted the code in SQL and it continued to give me the same error and provides the same code, and I checked there wasn't anything that I saw that was different about it. I would like for this to work, but I'm looking at another option to get by. If this works it would be a simpliar process. These tables are really large and hard to work with...so if I can reduce the amount of information it would help.

HiTechCoach · January 24th, 2012, 01:50 AM

Quote:

My understanding is the immediate window provides the code it's questioning and places a correct version

Correct version of what? All the Debug.Print does is place the SQL in the immediate windows so that you ca look at it to find the syntax error.

It would really help to know the error message. What is the exact error message?

SunnySide · January 27th, 2012, 02:03 PM

I just wanted to let you know I was pulled off of this for now. I'll come back to it as soon as I can.

HiTechCoach · January 28th, 2012, 12:36 AM

Thanks for the update.

We will be here when you get back to this project.