Macro code error querying SQL database

Question

I have a detailed macro that runs on invoices then queries a SQL database to identify whether any previous invoices have been issued to the client this year and inserts a file containing a table with various fields from the database to list those invoices and the amounts from them. I have copied the code from a similar query that functions properly to insert a different file and modified the SQL table and field names to get the data I need. The file gets inserted but none of the field information is updated and I can't identify why. This is the code I have, which is in a separate module of the macro. The initial codes are in the invoice document. If I include the line rsInvoiceLines.Close, then I get an error, although it functions correctly in the other macro:

Public Sub InvoiceStatement()

Dim rsInvoiceCount, rsInvoiceLines As Recordset

Dim strJobNo, strDate As String

Dim intNumLines As Integer

'Get the job number and date from the doc

strJobNo = GetValue("[JobNo]")

If Len(strJobNo) < 1 Then

    Exit Sub

End If

strDate = GetValue("[Date]")

If Len(strDate) < 1 Then

    Exit Sub

End If

'Find the invoice statement marker and remove it

With Selection.Find

    .Text = "[InvoiceStatement]"

    .Forward = True

    .Wrap = wdFindContinue

End With

If Selection.Find.Execute = True Then

    Selection.Delete Unit:=wdCharacter, Count:=1

Else

    Exit Sub

End If

'See if there are any invoice transactions.  If not, bail out

Set rsInvoiceCount = modDB.Query("select NUM=IsNull(Count(*), 0) from INV where JOB_NO='" & strJobNo & "'")

intNumLines = rsInvoiceCount.Fields("NUM")

rsInvoiceCount.Close

If intNumLines < 1 Then

    Exit Sub

End If

'Insert a new page and the invoice statement file

Selection.InsertBreak Type:=wdPageBreak

Selection.InsertFile FileName:=ModDBMacros.FirmPath & "Invoice_stmt.doc", range:="", _

    ConfirmConversions:=False, Link:=False, Attachment:=False

Set rsInvoiceLines = modDB.Query("select LN.DATE, HEAD=case HD.TYPE when 'INV' then HD.INV_NO, LN.FEES_AMT, LN.PARTS_AMT, HD.DISC_AMT, HD.DATE_PAID from INVDET as LN, INV as HD where LN.INV_AUDIT_NO = HD.AUDIT_NO and HD.JOB_NO ='" & strJobNo & "' and HD.DATE > '2013.12.31' order by HD.DATE")

Selection.GoTo what:=wdGoToBookmark, Name:="Inv_Stmt_Start"

Do While Not rsInvoiceLines.EOF

    Selection.TypeText (rsInvoiceLines.Fields("DATE"))

    Selection.MoveRight Unit:=wdCell

    If Len(rsInvoiceLines.Fields("HEAD")) > 0 Then

        Selection.TypeText (rsInvoiceLines.Fields("HEAD"))

        Selection.TypeText vbCrLf

    End If

    Selection.TypeText (rsInvoiceLines.Fields("REF_NO"))

    Selection.MoveRight Unit:=wdCell

    Selection.TypeText (Format(rsInvoiceLines.Fields("FEES_AMT"), "#,###,##0.00"))

    Selection.MoveRight Unit:=wdCell

    Selection.TypeText (Format(rsInvoiceLines.Fields("PARTS_AMT"), "#,###,##0.00"))

    Selection.MoveRight Unit:=wdCell

    Selection.TypeText (Format(rsInvoiceLines.Fields("DISC_AMT"), "#,###,##0.00"))

    Selection.MoveRight Unit:=wdCell

    Selection.TypeText (rsInvoiceLines.Fields("DATE_PAID"))

    Selection.MoveRight Unit:=wdCell

    rsInvoiceLines.MoveNext

    If Not rsInvoiceLines.EOF Then

        Selection.MoveRight Unit:=wdCell

    End If

Loop

'rsInvoiceLines.Close

End Sub

Answer 1

Sorry,

this is the first time I have posted on here and I changed the table names and fields without thinking of the consequences. I narrowed the problem to one line of code which causes an error in a SQL Query and I tried your change to [TRAN$], which still caused an error. Then I tried [TRAN] and it ran the query, giving me the result I wanted. The macro also now works properly.

Thank you.

Ray

Answer 2

Your previous posts don't indicate any SQL or other reference to a table named "TRAN", so it's hard to see how that could account for the reported errors. That said, you might try [TRAN$].

Answer 3

After further testing I have noted that SQL treats the word "TRAN" as having a certain meaning. The database I am using has a table named "TRAN" and the information I want to extract is in that table. Does anyone know how I can phrase the code to extract data from a SQL "TRAN" table without getting a syntax error?

Answer 4

thanks but this is just one part of the macro that runs when an invoice is generated. I need to find the right code to extract the information from the database as part of the invoice creation.

Answer 5

If you export the data from the SQL Database to an Excel Workbook, you could perhaps use the Many To One utility on my Merge Tools Add-in the create documents with the information that you require (Details of invoices previously issued for each particular Job Number.

The file needs to be saved in the Word Startup folder.  In Windows XP the default location for that folder is

C:\Documents and Settings[User Name]\Application Data\Microsoft\Word\STARTUP

In Windows Vista and Windows 7, 8 or 8.1 it is

C:\Users[User Name]\AppData\Roaming\Microsoft\Word\STARTUP

If you do not see the AppData folder: -

In Windows 7, - In Windows Explorer, click on the Organize drop down and then on Folder and search options and in the Folder Options dialog, go to the View tab and select the item "Show hidden files, folders, and drives".  While there, it is a good idea to uncheck the box of "Hide extensions for known file types".

In Windows 8 and 8.1, in the File Explorer, click on Options on the View tab of the ribbon and then on the View tab in the dialog that appears and select the item "Show hidden files, folders, and drives".  While there, it is a good idea to uncheck the box of "Hide extensions for known file types".

When that has been done and Word is started\re-started, the tab shown below will be added to the Ribbon:

One thing to note is that the field names in the data source must contain only alphanumeric characters (No @,#,$,%,&,(,), etc) and the field names must not start with a numeric character (0-9).

You may also want to download:

1. the Merging with Attachments document that is also on that page which explains how the system is used.  It is not actually necessary to have separate attachments as the facility can be used to send just the documents created by the merge itself as attachments, either as the body of the message itself or in the form of Word files or .pdf files.
2. the Mail Merging with Charts document that is also on that page.  That document explains how you must set up the Excel Data Source and the Mail Merge Main document to be able to execute a merge with a Chart that is unique to each record in the data source.
3. the Using the Many to One Facility document that describes how to use that facility.