Wrox Programmer Forums
Go Back   Wrox Programmer Forums > Microsoft Office > Access and Access VBA > Access VBA
|
Access VBA Discuss using VBA for Access programming.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access VBA section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old February 8th, 2012, 12:53 PM
Registered User
 
Join Date: Feb 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Why can't I navigate the data? Move does not work.

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
 
Old February 9th, 2012, 05:16 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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?
 
Old February 9th, 2012, 03:19 PM
Registered User
 
Join Date: Feb 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.
 
Old February 10th, 2012, 05:13 AM
Authorized User
 
Join Date: Jan 2011
Posts: 86
Thanks: 1
Thanked 12 Times in 12 Posts
Default

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
 
Old February 11th, 2012, 11:04 AM
Registered User
 
Join Date: Feb 2012
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





Similar Threads
Thread Thread Starter Forum Replies Last Post
vb 2010 datatable move next, move previouse bigway .NET 4 and Visual Studio 2010 General Discussions 1 July 13th, 2011 03:55 AM
Using VBA Code to Move Data Nanette Access VBA 1 November 3rd, 2006 04:16 PM
Need help: Move data with store proc Gunny SQL Server 2000 3 July 28th, 2006 12:10 AM
Best way to move data from 2000 to 2005 JimReid SQL Server 2005 1 May 19th, 2006 05:23 PM
Move Data from oracle10g to oracle8i mohammed_aid Oracle 0 December 11th, 2005 02:23 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.