467,081 Members | 1,118 Online
Bytes | Developer Community
Ask Question

Home New Posts Topics Members FAQ

Post your question to a community of 467,081 developers. It's quick & easy.

5 record sets making me crazy

I have an application that will produce a Word document based on five separate queries this has required that I create a ADODB connection:

'Create connection to current database
Dim Conn As ADODB.Connection
Set Conn = CurrentProject.Connection

There are five separate recordSets, one for each query. The record sets each fill out a table in the Word document, utilizing bookmarks. Some areas of the Word document have repeating tables. For example one of the tables has information about particular documents, like memos, letters. If there is to be more than one referenced document, the table repeats and is filled out based on the next record in the query. There is no problem with this.

It gets interesting when a separate query (and recordset) for the user notes is integrated. The memo or letter is described by these user notes to let the reader of the Word document know and understand the history being represented by the various letters and memos. Because of the required format of my Word document this requires a nested loop. See below code


'This repetition is for the record query and macro

'Variables for recordset in database, command for recordset
Dim lettersQuery As New ADODB.Recordset
Dim comLetters As New ADODB.Command

'Name of Access query
Dim lettersQuery As String
lettersQuery = "lettersQuery"

With comLetters
Set .ActiveConnection = Conn
.CommandText = "lettersQuery"
.CommandType = adCmdStoredProc
Set parameter = .CreateParameter("ReferenceNo", adVarChar, _
adParamInput, Len(RefNo), RefNo)
.Parameters.Append parameter
End With

lettersQuery.Open comLetters, , adOpenKeyset, adLockOptimistic

'Loop for letters
Do While Not lettersQuery.EOF

'Run macro in Word document
wordApp.Run "CreateLettersTable"

'Populate document bookmarks with query data
((code not relevant))

'Loop for notes

'Set letterNo variable equal to current record entry number
LetterNo = lettersQuery("LetterNo").Value

'Variables for recordset in database, command for recordset
Dim notesQuery As New ADODB.Recordset
Dim comNotes As New ADODB.Command

'Name of Access query
Dim notesQuery As String
notesQuery = "notesQuery"

With comNotes
Set .ActiveConnection = Conn
.CommandText = "notesQuery"
.CommandType = adCmdStoredProc
Set parameter = .CreateParameter("LetterNo", adVarChar, _
adParamInput, Len(LetterNo), LetterNo)
.Parameters.Append parameter
End With

notesQuery.Open comNotes, , adOpenKeyset, adLockOptimistic ************

'Loop for notes
Do While Not notesQuery.EOF

'Run macro in Word document
If notesQuery.AbsolutePosition > 1 Then
wordApp.Run "AddNoteLine"
End If

'Populate document bookmarks with query data
((code not relevant))

'Move to next record in data source
notesQuery.MoveNext

Loop 'End of notes loop.

'Close record set
notesQuery.Close
'Set notesQuery = Nothing
comNotes.Cancel

'Move to next record in data source
lettersQuery.MoveNext

Loop 'End of letters loop

'Close record set
lettersQuery.Close
comLetters.Cancel

I have tried to simplify the scenario and names of things in the code a little bit I may have so typos I didn’t catch. The point is - the code works - what happens is instead of adding the appropriate notes to the second and subsequent letters, it adds the notes for the first letter, it is as if the first recordset for the notes is still active. The stars (***) are under the line that is not working properly the second time through the loop. The query works correctly and pulls the correct information. I have tried setting the record set to nothing with no results.

I hope this makes sense
Nov 22 '06 #1
  • viewed: 2363
Share:
19 Replies
NeoPa
Expert Mod 16PB
I'm confused that you define some variables twice (notesQuery for instance).
Though I notice it is the same for the working code.
The only thing that occurs to me is that perhaps the notes query needs more than the one parameter set.
Nov 22 '06 #2
Those are some of the typos I was afraid I had created, in my code they are only defined once, I intended to type notesQueryRs or something like it for the record set. My query only has one parameter, the letters table has a one to many relationship with the notes table and it is adequate to query by letter number. The appropriate letter number is passed to the notes query, it just won't let go of the previous results.
Nov 22 '06 #3
NeoPa
Expert Mod 16PB
I think it would be a good idea to post actual tested code here.
I appreciate the stripping out of irrelevant code, but the actual code should be cleared of typos and compiled before posting.

I'm a little confused as it did sound as if you had done that already from your first post.
I suspect you're 'paraphrasing' for brevity, which is a nice idea, but more important is that the code is compiled code.

Not that I can promise to solve your problem for you - it looks quite complicated - but at least I can have another look.
BTW don't forget to use the CODE tags - it keeps the formatting of the code ;).
Nov 22 '06 #4
MMcCarthy
Expert Mod 8TB
try ...

Expand|Select|Wrap|Line Numbers
  1.  
  2. lettersQuery.Close
  3. Set lettersQuery = Nothing
  4.  
and so on.
Nov 22 '06 #5
To NeoPa

I was paraphrasing my code, I only included about a tenth or less of the code I have written. In order for you to be able to test the code, I think would have to send you data, and some word templates. I think that would be asking too much. Maybe I am misunderstanding. I tried a few other things, see below:

To mmccarthy

I tried doing that but it didn't work

I think when I run the query the second time in the loop, and it uses the first query's name for the recordset, it decides I want the first query's results. Even after setting the first recordset to nothing, the same thing happens.

I guess the query results could be stored somewhere other than the recordset, but I thought that was what the recordset did.

If I could come up with a way to rename the recordset each time it loops my problem would be solved.

I tried making an array of recordsets but I haven't done that before and am having a time of it - at least naming the recordsets differently in the array.
Nov 28 '06 #6
NeoPa
Expert Mod 16PB
Genalube,

I appreciate that (in both senses of the word). Chopping it down makes it a lot easier to deal with.
The point I was trying to make (without intending to sound critical) is that when you chop anything out it still needs to be tested to ensure :
1. It still retains its integrity (compiles - works as expected etc)
2. It still reflects the same problem that is being searched for.
Otherwise we could be wasting even more time than was saved 'chasing shadows'.

Again, let me assure you this is just a fuller explanation of what I was trying to say and should not be taken as criticism.
Good luck with your problem btw.
Nov 29 '06 #7
MMcCarthy
Expert Mod 8TB
Can I make a suggestion.

Unless you need to have this code in ADO for some reason can it be rewritten in DAO as I think this will solve most of your problems.

Although I don't normally recommend it, I think we will need to see all of your code as I can't follow the logic of what you are doing with what you've posted.

Post the full code and if you don't have a problem with DAO I will rewrite it as DAO code and I think this will solve the problem.

Mary
Nov 29 '06 #8
NeoPa, Mary

Thank you, I hope my reply didn't sound angry, I am just new at this and feel like I am imposing myself upon others.

I found examples of ADO, so I utilized them, I don't really understand the difference. What are the advantages of both?

Let me apologize for my code, I am sure I am writing sloppily and excessively, but I think I comment everything (perhaps to excess).

I tried to make my code generic when I was paraphrasing it, it is a very specific application designed to work with real estate and the documents that are associated with it.

There is a lot of code, about nine printed pages of it, I don't want you rewrite all of it, but understand you need to see all of it to understand what I am doing. I feel like I am taking advantage or abusing this forum by posting this much code. Forgive the impropriety.

Thank you both very much, whenever I run into a problem and web search for an answer, I find them on this forum more often than any other. It is very kind of you to help people like me.

I tried to put the repetition of the record set into a different sub (like the set bookmark sub), but found the connection variable couldn't be seen inside it, the bookmark setting is also quite variable, which I tried to do with case statements. Creating this application is my first experience with Visual Basic, and am new to programming in general, I am learning a lot, but it probably violates good formatting conventions.

----------------

Apparently there is too much code to post, I can break it up or send it to you some other way. What would you recomend?
Nov 29 '06 #9
MMcCarthy
Expert Mod 8TB
OK.

Firstly 9 pages of code sounds way too much.

DAO vs. ADO is a long running argument that I won't get into now. However, ADO is not needed for programming connections between or within Access Databases and DAO is easier to understand and code.

Forget about posting the 9 pages as I suspect the code is impractical.

Post the logic of what you are trying to do in English giving the relevant table names, field names, forms and controls. Post each of the recordset statements in full descibing what you are trying to do in each case.

Using english to descibe what steps you are trying to take and what results you want from each step. This should make things clearer and if you provide all the necessary information as above we should be able to help you put the code together in DAO.

Don't worry so much about the nature of your problem as we appreciate the fact that you've obviously tried very hard to put this together yourself.

Mary
Nov 29 '06 #10
I cut out the parts that were not applicable
Here is the first part of my code
Expand|Select|Wrap|Line Numbers
  1. Sub RunWordMacro()
  2.  
  3.     'Word application and document variables
  4.     Dim wordApp As Word.Application
  5.     Dim wordDoc As Word.Document
  6.  
  7.     'Create Word application
  8.     Set wordApp = CreateObject("Word.Application")
  9.  
  10.     'Set template location
  11.     Dim strTemplateLoc As String
  12.     strTemplateLoc = Options.DefaultFilePath(wdUserTemplatesPath)
  13.  
  14.     'Create Word document
  15.     Set wordDoc = wordApp.Documents.Add(strTemplateLoc & _
  16.         "\OwnershipRecord.dot")
  17.  
  18.     'Make Word visible so user can see document in Word
  19.     wordApp.Visible = True
  20.  
  21.     'Create connection to current database
  22.     Dim Conn As ADODB.Connection
  23.     Set Conn = CurrentProject.Connection
  24.  
  25.     'Create ParcelNo variable and set it equal to field in DocProd form
  26.     Dim ParcelNo As String
  27.     ParcelNo = Forms.DocProduction.ParcelNoTextBox
  28.  
  29.     'Create leadingZeros variable, set based on field in DocProd form
  30.     Dim LeadingZeros As String
  31.     LeadingZeros = Forms.DocProduction.TotalNoParcels
  32.  
  33.     'Create entryNo variable and set it equal to current record number
  34.     Dim EntryNo As String
  35.  
  36.     'The code below is repeated  with variations for each macro
  37.     'This is for the parcel query and macro
  38.  
  39.     'Variables for recordset in database, command for recordset
  40.     Dim parcelQueryData As New ADODB.recordSet
  41.     Dim Command As New ADODB.Command
  42.  
  43.     'Name of Access query
  44.     Dim parcelQuery As String
  45.     parcelQuery = "parcelQuery"
  46.  
  47.     With Command
  48.         Set .ActiveConnection = Conn
  49.         .CommandText = "parcelQuery"
  50.         .CommandType = adCmdStoredProc
  51.         Set parameter = .CreateParameter("Enter Parcel No", adVarChar, _
  52.             adParamInput, Len(ParcelNo), ParcelNo)
  53.         .Parameters.Append parameter
  54.     End With
  55.  
  56.     parcelQueryData.Open Command, , adOpenKeyset, adLockOptimistic
  57.  
  58.     'Run macro in template to "StartDocument"
  59.     wordApp.Run "StartDocument"
  60.  
  61.     'Populate document bookmarks with query data
  62.     If IsNull(parcelQueryData("RouteNo").Value) Then
  63.         SetBookmark wordDoc, "RouteNo", "<<Value Not Entered>>" & _
  64.             parcelQueryData("ParcelNo").Value
  65.     End If
Sample bookmark population, others not included
Expand|Select|Wrap|Line Numbers
  1. 'Close record set
  2.     parcelQueryData.Close
  3.     Command.Cancel
  4.  
  5.     'This repetition is for the property query and macro
  6.  
  7.     'Variables for recordset in database, command for recordset
  8.     Dim propQueryData As New ADODB.recordSet
  9.     Dim comProp As New ADODB.Command
  10.  
  11.     'Name of Access query
  12.     Dim propQuery As String
  13.     propQuery = "propQuery"
  14.  
  15.     With comProp
  16.         Set .ActiveConnection = Conn
  17.         .CommandText = "propQuery"
  18.         .CommandType = adCmdStoredProc
  19.         Set parameter = .CreateParameter("Enter Parcel No", adVarChar, _
  20.             adParamInput, Len(ParcelNo), ParcelNo)
  21.         .Parameters.Append parameter
  22.     End With
  23.  
  24.     propQueryData.Open comProp, , adOpenKeyset, adLockOptimistic
  25.  
  26.     'Loop for properties
  27.     Do While Not propQueryData.EOF
  28.  
  29.         'Run macro in template to "CreatePropertiesTable"
  30.         wordApp.Run "CreatePropertiesTable"
  31.  
  32.         'Populate document bookmarks with query data
  33.         If IsNull(propQueryData("TaxID").Value) Then
  34.             SetBookmark wordDoc, "TaxID", "<<Value Not Entered>>"
  35.         Else
  36.             SetBookmark wordDoc, "TaxID", _
  37.                 propQueryData("TaxID")
  38.         End If
  39.         If IsNull(propQueryData("Address").Value) Then
  40.             SetBookmark wordDoc, "Address", "<<Value Not Entered>>"
  41.         Else
  42.             SetBookmark wordDoc, "Address", _
  43.                 propQueryData("Address").Value
  44.         End If
  45.  
  46.         'Move to next record in data source
  47.         propQueryData.MoveNext
  48.  
  49.     Loop 'End of properties loop
  50.  
  51.     'Close record set
  52.     propQueryData.Close
  53.     comProp.Cancel
Skip over other record set definitions
Below is the portion that is not working properly
Expand|Select|Wrap|Line Numbers
  1. 'This repetition is for the record query and macro
  2.  
  3.     'Variables for recordset in database, command for recordset
  4.     Dim recQueryData As New ADODB.recordSet
  5.     Dim comRec As New ADODB.Command
  6.  
  7.     'Name of Access query
  8.     Dim recQuery As String
  9.     recQuery = "recQuery"
  10.  
  11.     With comRec
  12.         Set .ActiveConnection = Conn
  13.         .CommandText = "recQuery"
  14.         .CommandType = adCmdStoredProc
  15.         Set parameter = .CreateParameter("Enter Parcel No", adVarChar, _
  16.             adParamInput, Len(ParcelNo), ParcelNo)
  17.         .Parameters.Append parameter
  18.     End With
  19.  
  20.     recQueryData.Open comRec, , adOpenKeyset, adLockOptimistic
  21.  
  22.     'Loop for records
  23.     Do While Not recQueryData.EOF
  24.  
  25.         'If this is an "ALSO"/"LESS" record run that macro
  26.         If recQueryData("IsLess").Value = True Then
  27.             wordApp.Run "LessRecord"
  28.         End If
  29.         If recQueryData("IsAlso").Value = True Then
  30.             wordApp.Run "AlsoRecord"
  31.         End If
  32.  
  33.         'Run macro in template to "CreateRecordTable"
  34.         wordApp.Run "CreateRecordTable"
  35.  
  36.         'Populate document bookmarks with query data
Bookmark population not included
Expand|Select|Wrap|Line Numbers
  1. 'Loop for notes field, change record set to notes query
  2.  
  3.         'This repetition is for the sub query and macro for notes
  4.  
  5.         'Set entryNo variable equal to current record entry number
  6.         EntryNo = recQueryData("EntryNo").Value
  7.  
  8.         'Variables for recordset in database, command for recordset
  9.         Dim notesQueryData As New ADODB.recordSet
  10.         Dim comNotes As New ADODB.Command
  11.  
  12.         'Name of Access query
  13.         Dim notesQuery As String
  14.         notesQuery = "notesQuery"
  15.  
  16.         With comNotes
  17.             Set .ActiveConnection = Conn
  18.             .CommandText = "notesQuery"
  19.             .CommandType = adCmdStoredProc
  20.             Set parameter = .CreateParameter("Enter Entry No", _
  21.                 adVarChar, adParamInput, Len(EntryNo), EntryNo)
  22.             .Parameters.Append parameter
  23.         End With
  24.  
  25.         notesQueryData.Open comNotes, , adOpenKeyset, adLockOptimistic
  26.  
  27.         'Loop for notes
  28.         Do While Not notesQueryData.EOF
  29.  
  30.             'Run macro in template to "CreatePropertiesTable"
  31.             If notesQueryData.AbsolutePosition > 1 Then
  32.                 wordApp.Run "AddNoteLine"
  33.             End If
  34.  
  35.             'Populate document bookmarks with query data
  36.             If IsNull(notesQueryData("Note").Value) Then
  37.             SetBookmark wordDoc, "Note", "<<Value Not Entered>>"
  38.             Else
  39.                 SetBookmark wordDoc, "Note", _
  40.                     notesQueryData("Note")
  41.             End If
  42.  
  43.             'Move to next record in data source
  44.                 notesQueryData.MoveNext
  45.  
  46.         Loop 'End of notes loop.
  47.  
  48.         'Run macro in template to "JumpToEnd"
  49.         wordApp.Run "JumpToEnd"
  50.  
  51.         'Close record set
  52.         notesQueryData.Close
  53.         'Set notesQueryData = Nothing
  54.         comNotes.Cancel
  55.  
  56.         'Move to next record in data source
  57.         recQueryData.MoveNext
  58.  
  59.     Loop 'End of records loop
  60.  
  61.     'Close record set
  62.     recQueryData.Close
  63.     comRec.Cancel
Nov 29 '06 #11
NeoPa
Expert Mod 16PB
NeoPa, Mary

Thank you, I hope my reply didn't sound angry, I am just new at this and feel like I am imposing myself upon others.
Not at all. I am fully sympathetic. As Mary says, it's clear you're making every effort to co-operate with us on this.
For Mary's request, try reading through your response first. This is a very difficult thing to get right and typos make it just that little bit harder to work with.
Nov 30 '06 #12
I have a word template with several macos in it. Their names are AddNoteLine, AddNotesBookmark, AlsoRecord, CreateAdditionalOwnerTable, CreateFirstOwnerTable, CreateOwnershipTable, CreatePropertiesTable, CreateRecordTable, EndDocument, GeneralNotes, JumpToEnd, LessRecord, NewTableBookmark, and StartDocument. The document is formatted using tables but views as a complex letter. Each macro creates a table, populates it with standard text then populates the tables with bookmarks where the access data is to be entered.

In access there are several queries that form the basis for the record sets and display all the data needed for populating the bookmarks, they are genNotesQuery, notesQuery, ownQuery, parcelQuery, propQuery, recQuery. There is a form called docProduction in access that launches the macro I have been talking to you about. It has 7 controls, that let you determine to print a single document or range, two of these controls are not visible but hold variables important to the process called ParcelNoTextBox, and TotalNoParcels.

Here I have rewritten my code to make it lots easier to follow my intent, this code does not work as well as my code now. The fields in my tables correspond exactly to bookmark names (the arrays), there are additional strings that get thrown in at various bookmark locations, and there are exceptions, sometimes more than one data field is thrown in to a single bookmark location. These exceptions are in the array reDim areas, but have an (x) for location and are just comments right now. All the queries are parameter queries and are based on the parcel number (parcelNo) with the exception of the notes which are based on the entry number (entryNo) of the record.

Parcels can have more than one property, which each can have more than one owner, which each can have more than one title record document, or share a record. Each record document can have multiple descriptive notes that are used to describe the records and their effect to the letter recipient. Finally there are some general notes that describe the area the parcel represents and others. Thus all of the tables and the macros that create them in word need to appear multiple times dependant on the data in the tables. Some complexity to the problem is the notesQuery, a new table is not created, the data is placed in the record table. This requires that for every record, first the record query must be run, then the notes query run (and then looped through to find all notes in the record set) before the next record can be pulled from the record query record set. Only the CreateOwnershipTable macro and the parcel query that contains it’s data is run once consistently. This macro is run by the StartDocument macro along with code that sets the fonts and title. The format of the document is specified by the letter recipient, and is not very flexible.

Some common problems stem from the populating of the table commencing before the table creation macro has finished running. The second (etc.) iteration of a table creation moves the bookmarks to the new table because the names are the same. When the populating overruns this process multiple pieces of data appear in the cell where only one is meant to be. The other major problem, which often appears when I try to fix the first, is the cutting out of the longer data pieces, some of my data are memo types and are long, only a small amount of the data makes it into the cell. The code below does this.

In case this is still confusing I have created a web email address genalube3"AT"yahoo.com the user name is “genalube3” and the password “recordset”, I emailed some sample documents and the template file to it, open them if you want to see what I am trying to do. A picture is worth 10,000 words, after all.

Expand|Select|Wrap|Line Numbers
  1. Sub RunWordMacros()
  2.  
  3.     Dim wordApp As Word.Application  'Word application
  4.     Dim wordDoc As Word.Document     'Word document
  5.     Dim strTemplateLoc As String     'Word template location string
  6.     Dim Conn As ADODB.Connection     'Database connection object
  7.  
  8.     Dim LeadingZeros As String       'Variable for padding parcelNo strings
  9.     Dim ParcelNo As String           'Parcel number variable for queries
  10.     Dim EntryNo As String            'Entry number variable for recQuery
  11.  
  12.     Dim Bookmarks() As String        'Array of bookmark/field names
  13.  
  14.     'Create Word application
  15.     Set wordApp = CreateObject("Word.Application")
  16.     'Set template location
  17.     strTemplateLoc = Options.DefaultFilePath(wdUserTemplatesPath)
  18.     'Create Word document
  19.     Set wordDoc = wordApp.Documents.Add(strTemplateLoc & _
  20.         "\OwnershipRecord.dot")
  21.     'Make Word visible so user can see document in Word
  22.     wordApp.Visible = True
  23.     'Create connection to current database
  24.     Set Conn = CurrentProject.Connection
  25.     'Create ParcelNo variable and set it equal to field in DocProd form
  26.     ParcelNo = Forms.DocProduction.ParcelNoTextBox
  27.     'Create leadingZeros variable, set based on field in DocProd form
  28.     LeadingZeros = Forms.DocProduction.TotalNoParcels
  29.  
  30.     'The code below is repeated  with variations for each macro
  31.  
  32.     'This is for the parcel query and macro
  33.     'Set bookmark array
  34.     ReDim Bookmarks(2)
  35. '        Bookmarks(x) = "RouteNo"
  36. '        Bookmarks(x) = "ParcelNo"
  37.         Bookmarks(0) = "LicenseeInitials"
  38.         Bookmarks(1) = "MunicCounty"
  39.         Bookmarks(2) = "ProjectNo"
  40.     'Populate document
  41.     SetRecordSet wordApp, wordDoc, Conn, ParcelNo, Bookmarks(), _
  42.         "parcelQuery", "StartDocument"
  43.  
  44.     'This repetition is for the property query and macro
  45.     'Set bookmark array
  46.     ReDim Bookmarks(1)
  47.         Bookmarks(0) = "TaxID"
  48.         Bookmarks(1) = "Address"
  49.     'Populate document
  50.     SetRecordSet wordApp, wordDoc, Conn, ParcelNo, Bookmarks(), _
  51.         "propQuery", "CreatePropertiesTable"
  52.  
  53.     'This repetition is for the owner query and macro
  54.     'Set bookmark array
  55.     ReDim Bookmarks(2)
  56.         Bookmarks(0) = "OwnName"
  57. '        Bookmarks(x) = "MailAddress"
  58. '        Bookmarks(x) = "MailCity"
  59. '        Bookmarks(x) = "MailState"
  60. '        Bookmarks(x) = "MailZip"
  61.         Bookmarks(1) = "OwnerType"
  62.         Bookmarks(2) = "OwnerPercent"
  63.     'Populate document
  64.     SetRecordSet wordApp, wordDoc, Conn, ParcelNo, Bookmarks(), _
  65.         "ownQuery", "CreateAdditionalOwnerTable"
  66. '        "CreateFirstOwnerTable"
  67.  
  68.     'This repetition is for the records query and macro
  69.     'Set bookmark array
  70.     ReDim Bookmarks(9)
  71.         Bookmarks(0) = "EntryNo"
  72.         Bookmarks(1) = "Book"
  73.         Bookmarks(2) = "Page"
  74.         Bookmarks(3) = "DocType"
  75.         Bookmarks(4) = "DateSigned"
  76.         Bookmarks(5) = "DateRecorded"
  77.         Bookmarks(6) = "PropIDNo"
  78.         Bookmarks(7) = "RecDesc"
  79.         Bookmarks(8) = "Grantor"
  80.         Bookmarks(9) = "Grantee"
  81.     'Populate document
  82.     SetRecordSet wordApp, wordDoc, Conn, ParcelNo, Bookmarks(), _
  83.         "recQuery", "CreateRecordTable"
  84.  
  85.     'This repetition is for the sub query and macro for notes
  86.     'Set bookmark array
  87.     ReDim Bookmarks(0)
  88.         Bookmarks(0) = "Note"
  89.     'Populate document
  90.     SetRecordSet wordApp, wordDoc, Conn, ParcelNo, Bookmarks(), _
  91.         "notesQuery", "AddNoteLine"
  92.  
  93.     'The code below is save and close the document
  94.  
  95.     'Run macro in template to "EndDocument"
  96.     wordApp.Run "EndDocument"
  97.     'Save Word document based on parcel name in ownership folder
  98.     wordDoc.SaveAs ("" & CurrentProject.Path & "\Ownership\" & _
  99.         LeadingZeros & ParcelNo & "-own.doc")
  100.     'Print document if form requests it
  101.     If Forms.DocProduction.PrintOption = True Then
  102.         wordApp.ActiveDocument.PrintOut Background:=False
  103.     End If
  104.     Close Document
  105.     wordApp.Quit SaveChanges:=wdDoNotSaveChanges
  106.     Set wordApp = Nothing
  107.  
  108. End Sub
  109.  
  110. Sub SetRecordSet(wordApp, wordDoc, Conn, ParcelNo, _
  111.     Bookmarks() As String, query As String, macro As String)
  112.  
  113.     'Variables for recordset in database, command for recordset
  114.     Dim recordSet As New ADODB.recordSet
  115.     Dim Command As New ADODB.Command
  116.     Dim ParameterString As String
  117.     If query = "notesQuery" Then
  118.         ParameterString = "Enter Entry No"
  119.     Else: ParameterString = "Enter Entry No"
  120.     End If
  121.  
  122.     With Command
  123.         Set .ActiveConnection = Conn
  124.         .CommandText = query
  125.         .CommandType = adCmdStoredProc
  126.         Set parameter = .CreateParameter(ParameterString, adVarChar, _
  127.             adParamInput, Len(ParcelNo), ParcelNo)
  128.         .Parameters.Append parameter
  129.     End With
  130.  
  131.     recordSet.Open Command, , adOpenKeyset, adLockOptimistic
  132.  
  133.     Do While Not recordSet.EOF
  134.         wordApp.Run macro
  135.         Dim i As Integer
  136.         LowerVal = LBound(Bookmarks)
  137.         UpperVal = UBound(Bookmarks)
  138.         For i = LowerVal To UpperVal
  139.             TestRecordSet recordSet, Bookmarks(i)
  140.             SetBookmark wordDoc, Bookmarks(i), recordSet(Bookmarks(i)).Value
  141.         Next
  142.         recordSet.MoveNext
  143.     Loop
  144.  
  145.     recordSet.Close
  146.     Command.Cancel
  147.  
  148. End Sub
  149.  
  150. Sub TestRecordSet(recordSet, fieldName As String)
  151.  
  152.     If IsNull(recordSet(fieldName).Value) Then
  153.         SetBookmark wordDoc, fieldName, "<<Value Not Entered>>"
  154.     End If
  155.  
  156. End Sub
  157.  
  158. Sub SetBookmark(wordDoc, sBookmark As String, sValue As String)
  159.  
  160.     If wordDoc.Bookmarks.Exists(sBookmark) Then
  161.         wordDoc.Bookmarks(sBookmark).Range.Text = sValue
  162.     End If
  163.  
  164. End Sub
  165.  
Dec 1 '06 #13
NeoPa
Expert Mod 16PB
Genalube,

After all that's happened and been asked for and provided, I'm so sorry to say that Mary and I have both reached the limit of our experience on this one.
Apart from neither of us knowing much on the Word side of things, it's just so much to get your head around (remember we don't have direct experience of it and are trying to work at a level once removed from the job itself). It's a poor return for all the effort we know you've put in I know, and believe me we've put in some between us too trying to find solutions, but in the end we're stumped.

Something a little more self-contained (and smaller, definitely smaller) may prove possible to handle or help with, so if you were to post other questions, or parts of this question even, then maybe we could help.
Dec 2 '06 #14
MMcCarthy
Expert Mod 8TB
Genalube,

After all that's happened and been asked for and provided, I'm so sorry to say that Mary and I have both reached the limit of our experience on this one.
Apart from neither of us knowing much on the Word side of things, it's just so much to get your head around (remember we don't have direct experience of it and are trying to work at a level once removed from the job itself). It's a poor return for all the effort we know you've put in I know, and believe me we've put in some between us too trying to find solutions, but in the end we're stumped.

Something a little more self-contained (and smaller, definitely smaller) may prove possible to handle or help with, so if you were to post other questions, or parts of this question even, then maybe we could help.
I have also contacted a number of other experts on the forum to see if they have more experience in this area.

Mary
Dec 2 '06 #15
nico5038
Expert 2GB
Phew, a lot of code....

Did you try to close the connection before re-using it for the other recordset?

Nic;o)
Dec 2 '06 #16
Wow, well thanks to you for your help.

I can't close the connection because this is part of a nested loop, and need to do operations on the first recordset.

However trying and thinking about whether that would work lead me to the following. If I can name the bookmark, I don't have to do any nested loops and that would effectively solve my problem. I wrote a new maco in word, SetNotesEntry(EntryNo As String). It jumps to the Note bookmark and renames it to the string that is passed. I tested this by writing another macro and ran the following:

Dim NoteEntryNo As String
NoteEntryNo = "Note" & "4110177"
SetNotesEntry (NoteEntryNo)

It worked it named the bookmark "Note4110177", I can very easily build this string in my access module, the problem is how to pass the variable to word.

wordApp.Run macro

Is how I run the word macro (macro is a String), how do pass this variable to my macro.

wordApp.Run "SetNotesEntry" (NoteEntryNo)

Does not work, It makes sense that it shouldn't, but how do I do it right.
Dec 5 '06 #17
nico5038
Expert 2GB
For manipulating a Word document I use my own "tags" in the text like:
$$MyFieldName$$
These are replaced by using this routine in my VBA code:
Expand|Select|Wrap|Line Numbers
  1. Function fncReplace(strFrom As String, strTo As String)
  2.  
  3. Dim doc As Word.Document
  4. Dim rng As Word.Range
  5.  
  6. Set rng = ActiveDocument.Content
  7. With rng
  8.   .Find.ClearFormatting
  9.   .Find.Text = strFrom
  10.   .Find.Replacement.ClearFormatting
  11.   .Find.Replacement.Text = strTo
  12.   .Find.Execute Replace:=wdReplaceAll
  13. End With
  14. GoTo exit_fncReplace
  15.  
  16. exit_fncReplace:
  17. End Function
Nic;o)
Dec 5 '06 #18
Thanks, I also found out that
wordApp.Run "SetNotesEntry", NoteEntryNo
works too.

I have two questions, and I think my clunky little application will work. My current data set has the information for creating 98 seperate word documents, when I make all my documents the errors are focused on two problems. My word macro creates a table in word with standard text and bookmarks in it. My access routine calls the macros and fills in the bookmarks with the variable data. The loops allow some documents to have more of a particuar table type than another document does.

Sometimes it appears that the line that calls for the table to be created and the code that fills it in are running at the same time. I need the code for filling in the table to wait until the table building macro is finished. Is there something like RunWait in VB, I can't find it?

The other problem I think is related some of my access data is in a memo field and is sometimes very long (a page of text in word), sometimes only a few lines show up. It usually happens after I try to change my code to fix the first problem. Is this happening because it doesn't have time to finish?
Dec 5 '06 #19
nico5038
Expert 2GB
Hmm, hard to judge from a distance.
I would probably skip the Word macro's and manage the document from Access VBA only to keep full control.

Memofields are rather "picky" and when not moved "directly", but via an intermediate string, then loss of data is very likely. In queries a Memo field will be truncated to the max of 255 characters as that's the internal max of the Jet engine.
In VBA code the string should be longer, but not sure in relation to Word macro's...

Nic;o)
Dec 6 '06 #20

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

1 post views Thread by David | last post: by
reply views Thread by swapna_munukoti@yahoo.co.in | last post: by
2 posts views Thread by Mark Reed | last post: by
9 posts views Thread by Joshua.Buss@gmail.com | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.