Not Looping? 1

[EllieFant]

Hi,

I have the following code:


Dim WordObj As Word.Application
Dim db As DAO.Database
Dim rsInspectionReport As Recordset
Dim mystrSQL As String

Set WordObj = CreateObject("Word.application")
Set db = CurrentDb

mystrSQL = "SELECT * FROM qryInspectionReportAll WHERE [InspNumber] =" & [Forms]![frmInspectionReportMenu]![txtIDNumber]

Set rsInspectionReport = db.OpenRecordset(mystrSQL)

With WordObj

.Visible = True

rsInspectionReport.MoveLast
rsInspectionReport.MoveFirst

Do Until rsInspectionReport.EOF = True

.Documents.Add ("\\rdc1\surety\surety reviews\suretyreview.dot")
.Activedocument.bookmarks("FNumber").Range.insertafter DLookup("[FindingNum]", "qryInspectionReportAll")
.Activedocument.bookmarks("Finding").Range.insertafter DLookup("[Finding]", "qryInspectionReportAll")

rsInspectionReport.MoveNext
Loop

End With

rsInspectionReport.Close

Set rsInspectionReport = Nothing

It creates the right number of documents (one for each finding), but the information is the same in each one even though it shouldn't be. It is like it doesn't move to the next record.

Any help would be appreciated. This is a Access 97 Database.


Lena
**Using Access 97 at work**
**Using Access 2003 at home**

lenawood@qwest.net

 

[dhookom]

I don't see any code that references fields from the recordset.

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[EllieFant]

How/Where do I need to reference it at? I haven't done many recordsets so am very unsure of what I am trying to do.

Many Thanks!

Lena
**Using Access 97 at work**
**Using Access 2003 at home**

lenawood@qwest.net

 

[dhookom]

I don't know what is in your recordset and how it relates to your documents. I try to never "SELECT * ..." in code since anyone maintaining the code will not know what the fields are. Try to explicitly name your fields in all recordset queries.

I expect you need to do something with

        .Activedocument.bookmarks("FNumber").Range.insertafter DLookup("[FindingNum]", "qryInspectionReportAll")
        .Activedocument.bookmarks("Finding").Range.insertafter DLookup("[Finding]", "qryInspectionReportAll")

These Dlookup()s will always return the same values. Do you need the DLookup() or is the information needed for the document already in the recordset?

Duane
MS Access MVP
[green]Ask a great question, get a great answer.[/green]
[red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]
[blue]Ask me about my grandson, get a grand answer.[/blue]

 

[EllieFant]

Information is already in the recordset. I was following an example I had that used DLookup. I was trying to bit and piece it together to make it work...without any success as you can see.

Lena

Lena
**Using Access 97 at work**
**Using Access 2003 at home**

lenawood@qwest.net

 

[dhookom]

Apparently you want to replace the DLookup() with values from the recordset:

  .Activedocument.bookmarks("FNumber").Range.insertafter _
          rsInspectionReport![FindingNum]
  .Activedocument.bookmarks("Finding").Range.insertafter _
          rsInspectionReport![Finding]

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[EllieFant]

Thank you...works great. Would have answered sooner but internet at work wasn't working well.

Thanks again!
Lena

Lena
**Using Access 97 at work**
**Using Access 2003 at home**

lenawood@qwest.net