Why can't I navigate the data? Move does not work.

RaySL · February 8th, 2012, 12:53 PM

Here is the code. When I run it, I only get data from the first row of the table and there are three rows in the table.

Thanks for any help anyone can give.

Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Dim strFile As String
Dim objWord As Word.Application
Dim dbsA As DAO.Database
Dim rstA As DAO.Recordset
Dim vCount As Integer
Set objWord = CreateObject("Word.Application")

With objWord
.Visible = True
.Documents.Open ("R:\IRS Forms\CopyB_1099-sTemplate.doc")
Set dbsA = OpenDatabase("G:\Database\REManager\REManagerData. mdb")
Set rstA = dbsA.OpenRecordset("tbl2011_1099STemp")
rstA.MoveFirst
While rstA.EOF = False
If IsNull([BCSP]) = False Then
.ActiveDocument.Bookmarks("BCSP_1").Select
.Selection.Text = (CStr(Me.BCSP))
End If

If IsNull([BCSPAdd]) = False Then
.ActiveDocument.Bookmarks("BCSPAdd_1").Select
.Selection.Text = (CStr(Me.BCSPAdd))
End If

If IsNull([BCSPCSZ]) = False Then
.ActiveDocument.Bookmarks("BCSPCSZ_1").Select
.Selection.Text = (CStr(Me.BCSPCSZ))
End If

If IsNull([BCSPNo]) = False Then
.ActiveDocument.Bookmarks("BCSPNo_1").Select
.Selection.Text = (CStr(Me.BCSPNo))
End If

If IsNull([Seller1]) = False Then
.ActiveDocument.Bookmarks("Seller_1").Select
.Selection.Text = (CStr(Me.Seller1))
End If

If IsNull([SocialSec1]) = False Then
.ActiveDocument.Bookmarks("SocSec_1").Select
.Selection.Text = (CStr(Me.SocialSec1))
End If

If IsNull([ClosingDate]) = False Then
.ActiveDocument.Bookmarks("CloseDate_1").Select
.Selection.Text = (CStr(Me.ClosingDate))
End If

If IsNull([Gross]) = False Then
.ActiveDocument.Bookmarks("Gross_1").Select
.Selection.Text = (CStr(Me.Gross))
End If

If IsNull([PropAdd]) = False Then
.ActiveDocument.Bookmarks("PropAdd_1").Select
.Selection.Text = (CStr(Me.PropAdd))
End If

If IsNull([PropCSZ]) = False Then
.ActiveDocument.Bookmarks("PropCSZ_1").Select
.Selection.Text = (CStr(Me.PropCSZ))
End If

If IsNull([SellerAddress]) = False Then
.ActiveDocument.Bookmarks("SellerAdd_1").Select
.Selection.Text = (CStr(Me.SellerAddress))
End If

If IsNull([SellerCSZ]) = False Then
.ActiveDocument.Bookmarks("SellerCSZ_1").Select
.Selection.Text = (CStr(Me.SellerCSZ))
End If

If IsNull([FileNumber]) = False Then
.ActiveDocument.Bookmarks("FileNum_1").Select
.Selection.Text = (CStr(Me.FileNumber))
End If

If IsNull([BuyersPart]) = False Then
.ActiveDocument.Bookmarks("BuyerPart_1").Select
.Selection.Text = (CStr(Me.BuyersPart))
End If
rstA.MoveNext
If IsNull([BCSP]) = False Then
.ActiveDocument.Bookmarks("BCSP_2").Select
.Selection.Text = (CStr(Me.BCSP))
End If

If IsNull([BCSPAdd]) = False Then
.ActiveDocument.Bookmarks("BCSPAdd_2").Select
.Selection.Text = (CStr(Me.BCSPAdd))
End If

If IsNull([BCSPCSZ]) = False Then
.ActiveDocument.Bookmarks("BCSPCSZ_2").Select
.Selection.Text = (CStr(Me.BCSPCSZ))
End If

If IsNull([BCSPNo]) = False Then
.ActiveDocument.Bookmarks("BCSPNo_2").Select
.Selection.Text = (CStr(Me.BCSPNo))
End If

If IsNull([Seller1]) = False Then
.ActiveDocument.Bookmarks("Seller_2").Select
.Selection.Text = (CStr(Me.Seller1))
End If
If IsNull([SocialSec1]) = False Then
.ActiveDocument.Bookmarks("SocSec_2").Select
.Selection.Text = (CStr(Me.SocialSec1))
End If

If IsNull([ClosingDate]) = False Then
.ActiveDocument.Bookmarks("CloseDate_2").Select
.Selection.Text = (CStr(Me.ClosingDate))
End If

If IsNull([Gross]) = False Then
.ActiveDocument.Bookmarks("Gross_2").Select
.Selection.Text = (CStr(Me.Gross))
End If

If IsNull([PropAdd]) = False Then
.ActiveDocument.Bookmarks("PropAdd_2").Select
.Selection.Text = (CStr(Me.PropAdd))
End If

If IsNull([PropCSZ]) = False Then
.ActiveDocument.Bookmarks("PropCSZ_2").Select
.Selection.Text = (CStr(Me.PropCSZ))
End If

If IsNull([SellerAddress]) = False Then
.ActiveDocument.Bookmarks("SellerAdd_2").Select
.Selection.Text = (CStr(Me.SellerAddress))
End If

If IsNull([SellerCSZ]) = False Then
.ActiveDocument.Bookmarks("SellerCSZ_2").Select
.Selection.Text = (CStr(Me.SellerCSZ))
End If

If IsNull([FileNumber]) = False Then
.ActiveDocument.Bookmarks("FileNum_2").Select
.Selection.Text = (CStr(Me.FileNumber))
End If

If IsNull([BuyersPart]) = False Then
.ActiveDocument.Bookmarks("BuyerPart_2").Select
.Selection.Text = (CStr(Me.BuyersPart))
End If

rstA.MoveNext

If IsNull([BCSP]) = False Then
.ActiveDocument.Bookmarks("BCSP_3").Select
.Selection.Text = (CStr(Me.BCSP))
End If

If IsNull([BCSPAdd]) = False Then
.ActiveDocument.Bookmarks("BCSPAdd_3").Select
.Selection.Text = (CStr(Me.BCSPAdd))
End If

If IsNull([BCSPCSZ]) = False Then
.ActiveDocument.Bookmarks("BCSPCSZ_3").Select
.Selection.Text = (CStr(Me.BCSPCSZ))
End If

If IsNull([BCSPNo]) = False Then
.ActiveDocument.Bookmarks("BCSPNo_3").Select
.Selection.Text = (CStr(Me.BCSPNo))
End If

If IsNull([Seller1]) = False Then
.ActiveDocument.Bookmarks("Seller_3").Select
.Selection.Text = (CStr(Me.Seller1))
End If
If IsNull([SocialSec1]) = False Then
.ActiveDocument.Bookmarks("SocSec_3").Select
.Selection.Text = (CStr(Me.SocialSec1))
End If

If IsNull([ClosingDate]) = False Then
.ActiveDocument.Bookmarks("CloseDate_3").Select
.Selection.Text = (CStr(Me.ClosingDate))
End If

If IsNull([Gross]) = False Then
.ActiveDocument.Bookmarks("Gross_3").Select
.Selection.Text = (CStr(Me.Gross))
End If

If IsNull([PropAdd]) = False Then
.ActiveDocument.Bookmarks("PropAdd_3").Select
.Selection.Text = (CStr(Me.PropAdd))
End If

If IsNull([PropCSZ]) = False Then
.ActiveDocument.Bookmarks("PropCSZ_3").Select
.Selection.Text = (CStr(Me.PropCSZ))
End If

If IsNull([SellerAddress]) = False Then
.ActiveDocument.Bookmarks("SellerAdd_3").Select
.Selection.Text = (CStr(Me.SellerAddress))
End If

If IsNull([SellerCSZ]) = False Then
.ActiveDocument.Bookmarks("SellerCSZ_3").Select
.Selection.Text = (CStr(Me.SellerCSZ))
End If

If IsNull([FileNumber]) = False Then
.ActiveDocument.Bookmarks("FileNum_3").Select
.Selection.Text = (CStr(Me.FileNumber))
End If

If IsNull([BuyersPart]) = False Then
.ActiveDocument.Bookmarks("BuyerPart_3").Select
.Selection.Text = (CStr(Me.BuyersPart))
End If

rstA.MoveNext

.Selection.GoTo What:=wdGoToBookmark, Name:="Top"

End With
Wend
rstA.Close

disel2010 · February 9th, 2012, 05:16 AM

Not sure, but it puzzles me a bit why you call Me.BCSP, Me.BCSPAdd, Me.BCSPNo, etcetera..

Give it a try using rstA("BCSP"), rstA("BCSPAdd"), rstA("BCSPNo"), ...

In addition, it's strange that you coded this with the expectance that your table will always have only three records... (as you close your Word object before the end of the While loop). Are you sure you won't run into problems here?

RaySL · February 9th, 2012, 03:19 PM

di ... The reason I set it up for only three rows of data is because I'm populating three forms on a page. I would, eventually, do more after i get this to work for only one page. What happens, when I run it, is I get the first row of data repeated on all the forms. The movenext command is not moving to the next row of data and I don't understand why. I was hoping that someone on the forum might see a mistake.

disel2010 · February 10th, 2012, 05:13 AM

Hi Ray,

I think it's because you are calling Me.BCSP etc. and not the recordset..

What do you see when you put a breakpoint and step into the code?

At first sight, this is how I would refactor your code:
> replace Me.BCSP by rstA("BCSP")
> replace the movenext statements in the loop by a case statement checking the modulus

If it doesn't help, I hope a more experienced contributor can help.

Code:

Option Compare Database
Option Explicit
Private Sub Form_Open(Cancel As Integer)
Dim strFile As String
Dim objWord As Word.Application
Dim dbsA As DAO.Database
Dim rstA As DAO.Recordset
Dim vCount As Integer
Dim rstCount As Integer
Set objWord = CreateObject("Word.Application")

With objWord
	.Visible = True
	.Documents.Open ("R:\IRS Forms\CopyB_1099-sTemplate.doc")
	Set dbsA = OpenDatabase("G:\Database\REManager\REManagerData. mdb")
	Set rstA = dbsA.OpenRecordset("tbl2011_1099STemp")
	rstA.MoveFirst
	rstCount = 0
	While rstA.EOF = False
		rstCount = rstCount +1
		
		Select Case rstCount Mod 3
			Case 1 ' row 1,4,7,....'
				If IsNull([BCSP]) = False Then
					.ActiveDocument.Bookmarks("BCSP_1").Select
					.Selection.Text = (CStr(rstA("BCSP")))
				End If
		
				If IsNull([BCSPAdd]) = False Then
					.ActiveDocument.Bookmarks("BCSPAdd_1").Select
					.Selection.Text = (CStr(rstA("BCSPAdd")))
				End If
		
				If IsNull([BCSPCSZ]) = False Then
					.ActiveDocument.Bookmarks("BCSPCSZ_1").Select
					.Selection.Text = (CStr(rstA("BCSPCSZ")))
				End If
		
				If IsNull([BCSPNo]) = False Then
					.ActiveDocument.Bookmarks("BCSPNo_1").Select
					.Selection.Text = (CStr(rstA("BCSPNo")))
				End If
		
				If IsNull([Seller1]) = False Then
					.ActiveDocument.Bookmarks("Seller_1").Select
					.Selection.Text = (CStr(rstA("Seller1")))
				End If
		
				If IsNull([SocialSec1]) = False Then
					.ActiveDocument.Bookmarks("SocSec_1").Select
					.Selection.Text = (CStr(rstA("SocialSec1")))
				End If
		
				If IsNull([ClosingDate]) = False Then
					.ActiveDocument.Bookmarks("CloseDate_1").Select
					.Selection.Text = (CStr(rstA("ClosingDate")))
				End If
		
				If IsNull([Gross]) = False Then
					.ActiveDocument.Bookmarks("Gross_1").Select
					.Selection.Text = (CStr(rstA("Gross")))
				End If
		
				If IsNull([PropAdd]) = False Then
					.ActiveDocument.Bookmarks("PropAdd_1").Select
					.Selection.Text = (CStr(rstA("PropAdd")))
				End If
		
				If IsNull([PropCSZ]) = False Then
					.ActiveDocument.Bookmarks("PropCSZ_1").Select
					.Selection.Text = (CStr(rstA("PropCSZ")))
				End If
		
				If IsNull([SellerAddress]) = False Then
					.ActiveDocument.Bookmarks("SellerAdd_1").Select
					.Selection.Text = (CStr(rstA("SellerAddress")))
				End If
		
				If IsNull([SellerCSZ]) = False Then
					.ActiveDocument.Bookmarks("SellerCSZ_1").Select
					.Selection.Text = (CStr(rstA("SellerCSZ")))
				End If
		
				If IsNull([FileNumber]) = False Then
					.ActiveDocument.Bookmarks("FileNum_1").Select
					.Selection.Text = (CStr(rstA("FileNumber")))
				End If
		
				If IsNull([BuyersPart]) = False Then
					.ActiveDocument.Bookmarks("BuyerPart_1").Select
					.Selection.Text = (CStr(rstA("BuyersPart")))
				End If
			Case 2 ' row 2,5,8,...
				If IsNull([BCSP]) = False Then
					.ActiveDocument.Bookmarks("BCSP_2").Select
					.Selection.Text = (CStr(rstA("BCSP")))
				End If

				If IsNull([BCSPAdd]) = False Then
					.ActiveDocument.Bookmarks("BCSPAdd_2").Select
					.Selection.Text = (CStr(rstA("BCSPAdd")))
				End If

				If IsNull([BCSPCSZ]) = False Then
					.ActiveDocument.Bookmarks("BCSPCSZ_2").Select
					.Selection.Text = (CStr(rstA("BCSPCSZ")))
				End If

				If IsNull([BCSPNo]) = False Then
					.ActiveDocument.Bookmarks("BCSPNo_2").Select
					.Selection.Text = (CStr(rstA("BCSPNo")))
				End If

				If IsNull([Seller1]) = False Then
					.ActiveDocument.Bookmarks("Seller_2").Select
					.Selection.Text = (CStr(rstA("Seller1")))
				End If
	
				If IsNull([SocialSec1]) = False Then
					.ActiveDocument.Bookmarks("SocSec_2").Select
					.Selection.Text = (CStr(rstA("SocialSec1")))
				End If

				If IsNull([ClosingDate]) = False Then
					.ActiveDocument.Bookmarks("CloseDate_2").Select
					.Selection.Text = (CStr(rstA("ClosingDate")))
				End If

				If IsNull([Gross]) = False Then
					.ActiveDocument.Bookmarks("Gross_2").Select
					.Selection.Text = (CStr(rstA("Gross")))
				End If
	
				If IsNull([PropAdd]) = False Then
					.ActiveDocument.Bookmarks("PropAdd_2").Select
					.Selection.Text = (CStr(rstA("PropAdd")))
				End If

				If IsNull([PropCSZ]) = False Then
					.ActiveDocument.Bookmarks("PropCSZ_2").Select
					.Selection.Text = (CStr(rstA("PropCSZ")))
				End If

				If IsNull([SellerAddress]) = False Then
					.ActiveDocument.Bookmarks("SellerAdd_2").Select
					.Selection.Text = (CStr(rstA("SellerAddress")))
				End If

				If IsNull([SellerCSZ]) = False Then
					.ActiveDocument.Bookmarks("SellerCSZ_2").Select
					.Selection.Text = (CStr(rstA("SellerCSZ")))
				End If

				If IsNull([FileNumber]) = False Then
					.ActiveDocument.Bookmarks("FileNum_2").Select
					.Selection.Text = (CStr(rstA("FileNumber")))
				End If
		
				If IsNull([BuyersPart]) = False Then
					.ActiveDocument.Bookmarks("BuyerPart_2").Select
					.Selection.Text = (CStr(rstA("BuyersPart")))
				End If

			Case 0 ' row 3,6,9,...
		                If IsNull([BCSP]) = False Then
					.ActiveDocument.Bookmarks("BCSP_3").Select
					.Selection.Text = (CStr(rstA("BCSP")))
				End If
				
				If IsNull([BCSPAdd]) = False Then
					.ActiveDocument.Bookmarks("BCSPAdd_3").Select
					.Selection.Text = (CStr(rstA("BCSPAdd")))
				End If
				
				If IsNull([BCSPCSZ]) = False Then
					.ActiveDocument.Bookmarks("BCSPCSZ_3").Select
					.Selection.Text = (CStr(rstA("BCSPCSZ")))
				End If
				
				If IsNull([BCSPNo]) = False Then
					.ActiveDocument.Bookmarks("BCSPNo_3").Select
					.Selection.Text = (CStr(rstA("BCSPNo")))
				End If
				
				
				If IsNull([Seller1]) = False Then
					.ActiveDocument.Bookmarks("Seller_3").Select
					.Selection.Text = (CStr(rstA("Seller1")))
				End If
				If IsNull([SocialSec1]) = False Then
					.ActiveDocument.Bookmarks("SocSec_3").Select
					.Selection.Text = (CStr(rstA("SocialSec1")))
				End If
				
				If IsNull([ClosingDate]) = False Then
					.ActiveDocument.Bookmarks("CloseDate_3").Select
					.Selection.Text = (CStr(rstA("ClosingDate")))
				End If
				
				If IsNull([Gross]) = False Then
					.ActiveDocument.Bookmarks("Gross_3").Select
					.Selection.Text = (CStr(rstA("Gross")))
				End If
				
				If IsNull([PropAdd]) = False Then
					.ActiveDocument.Bookmarks("PropAdd_3").Select
					.Selection.Text = (CStr(rstA("PropAdd")))
				End If
				
				If IsNull([PropCSZ]) = False Then
					.ActiveDocument.Bookmarks("PropCSZ_3").Select
					.Selection.Text = (CStr(rstA("PropCSZ")))
				End If
				
				If IsNull([SellerAddress]) = False Then
					.ActiveDocument.Bookmarks("SellerAdd_3").Select
					.Selection.Text = (CStr(rstA("SellerAddress")))
				End If
				
				If IsNull([SellerCSZ]) = False Then
					.ActiveDocument.Bookmarks("SellerCSZ_3").Select
					.Selection.Text = (CStr(rstA("SellerCSZ")))
				End If
				
				If IsNull([FileNumber]) = False Then
					.ActiveDocument.Bookmarks("FileNum_3").Select
					.Selection.Text = (CStr(rstA("FileNumber")))
				End If
				
				If IsNull([BuyersPart]) = False Then
					.ActiveDocument.Bookmarks("BuyerPart_3").Select
					.Selection.Text = (CStr(rstA("BuyersPart")))
				End If
	        End Select

	rstA.MoveNext
	Wend

	rstA.Close
	.Selection.GoTo What:=wdGoToBookmark, Name:="Top"
End With

RaySL · February 11th, 2012, 11:04 AM

di .... Well, you were correct. Changing me.---- to rstA(----) did the trick. I can now populate a page without a problem. All I need to do now is be able to add pages as required for a larger data set.

Thanks for all your help,

Ray