Thanks in advance to anyone to can help.

I am trying to modify the Word Automation exercise from 'Beginning Access 2000 VBA'. I am getting a runtime error at Set recAsset = db.OpenRecordset(strSQL)

The code is below. Any help would be greatly appreciated.

This is my first VB project and it's definately baptism by fire.

Option Compare Database


Public Const m_strDIR As String = "D:\Documents and Settings\213043653\Desktop\Remarketing Database\"
Public Const m_strTEMPLATE As String = "ReturnLetter.dot"

Public m_objWord As Word.Application
Public m_objDoc As Word.Document
Public Sub ReOrder()

Dim db As Database
Dim recLease As Recordset
Dim recAsset As Recordset
Dim strSQL As String
Dim strItems As String
Dim strSerialNBR As String
Dim strDescription As String


Set db = CurrentDb()

Set recLease = db.OpenRecordset("LetterLease")
Set recAsset = db.OpenRecordset("LetterAsset")

While Not recLease.EOF
    strSQL = "SELECT * FROM LetterAsset " & _
        " WHERE Lease_NBR = " & recAsset("Lease_NBR")
    Set recAsset = db.OpenRecordset(strSQL)
    strItems = "Serial_NBR" & vbTab & "Description"
    While Not recAsset.EOF
        strItems = strItems & vbCrLf & recAsset("Serial_Nbr") & _
            vbTab & recAsset("Description")
    Wend

CreateOrderLetter recLease, recAsset

recLease.Close
recAsset.MoveNext
Wend
recAsset.Close
Set recLease = Nothing
Set recAsset = Nothing


End Sub

Public Sub CreateOrderLetter(recLease, recAsset)

    Set m_objWord = New Word.Application
    Set m_objDoc = m_objWord.Documents.Add(m_strDIR & m_strTEMPLATE)


    ' insert the customer details
    InsertTextAtBookMark "Lease_Nbr", recLease("Lease_NBR")
    InsertTextAtBookMark "Customer_Name2", recLease("Customer_Name")
    InsertTextAtBookMark "Contact_Name", recLease("Contact_Name")
    InsertTextAtBookMark "Lease_Nbr2", recLease("Lease_NBR")
    InsertTextAtBookMark "Lease_Nbr3", recLease("Lease_NBR")
    InsertTextAtBookMark "Whs_Name", recLease("Whs_Name")
    InsertTextAtBookMark "Whs_Add_1", recLease("Whs_Add_1")
    InsertTextAtBookMark "Whs_City", recLease("Whs_City")
    InsertTextAtBookMark "Whs_State", recLease("Whs_State")
    InsertTextAtBookMark "Whs_Zip", recLease("Whs_Zip")
    InsertTextAtBookMark "Whs_Contact", recLease("Whs_Contact")
    InsertTextAtBookMark "Whs_Contact2", recLease("Whs_Contact")
    InsertTextAtBookMark "Whs_Contact_Nbr", recLease("Whs_Contact_Nbr")
    InsertTextAtBookMark "Whs_Contact_Fax", recLease("Whs_Contact_Fax")
    InsertTextAtBookMark "Whs_Contact_Fax2", recLease("Whs_Contact_Fax")
    InsertTextAtBookMark "Customer_Name", recLease("Customer_Name")


InsertItemsTable (recDetails)

m_objDoc.SaveAs FileName:=m_strDIR & recLease("Lease_Nbr") & _
" - " & FormatDateTime(Date, vbLongDate) & ".DOC"

m_objWord.Visible = True




End Sub

Private Sub InsertTextAtBookMark(strBkmk As String, varText As Variant)

    ' selects the bookmark and inserts the text
    m_objDoc.Bookmarks(strBkmk).Select
    m_objWord.Selection.Text = varText & ""

End Sub

Private Sub InsertItemsTable(recR As Recordset)

    Dim strTable As String
    Dim objTable As Word.Table

    strTable = "Serial NBR" & vbTab & "Description" & vbCr
    recR.MoveFirst
    While Not recR.EOF
        strTable = strTable & recR("Serial_Nbr") & vbTab & _
            recR("Description") & vbCr
        recR.MoveNext
    Wend

    InsertTextAtBookMark "Details", strTale
    Set objTable = m_objWord.Selection.ConvertToTable(Separator:=vbTa b)
    With objTable
        .AutoFormat Format:=wdTableFormatClassic3, AutoFit:=True, _
            ApplyShading:=False
    End With

    Set objTable = Nothing

End Sub

When you mouse over stSQL in the debugger, what is the value for stSQL? My guess is that it is something like this:

"SELECT * FROM LetterAsset WHERE Lease_NBR = X"

If the value passed from recAsset("Lease_NBR") is an integer, then this should work. If the value passed is a string, then you need to write stSQL like this:

"SELECT * FROM LetterAsset " & _
" WHERE Lease_NBR = " & "'" & recAsset("Lease_NBR") & "'"

You may want to try this anyway.

You may also need to add this to your error line:


HTH



mmcdonal

Sorry, I was going to put in connection, cursor and locking info.

mmcdonal

mmcdonal.... thanks for the quick reply.
You are right on target where it(X) was oroginally looking for an integer and I'm now refering to a text syring. Your changes didn't have any effect although your solution sounded dead-on. Any other thoughts?

I did it!

mmcdonal.....Thank you. You're correction was accurate and worked. I also learned that you haev to close the debugger after making corrections and run the routine again in order for your corrections to work. (Duh) This was my first real line of code for an application. I feel like a million bucks.