5 record sets making me crazy

[Genalube]

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.Connectio n
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 = "lettersQue ry"

With comLetters
Set .ActiveConnecti on = Conn
.CommandText = "lettersQue ry"
.CommandType = adCmdStoredProc
Set parameter = .CreateParamete r("ReferenceNo" , adVarChar, _
adParamInput, Len(RefNo), RefNo)
.Parameters.App end parameter
End With

lettersQuery.Op en comLetters, , adOpenKeyset, adLockOptimisti c

'Loop for letters
Do While Not lettersQuery.EO F

'Run macro in Word document
wordApp.Run "CreateLettersT able"

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

'Loop for notes

'Set letterNo variable equal to current record entry number
LetterNo = lettersQuery("L etterNo").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 .ActiveConnecti on = Conn
.CommandText = "notesQuery "
.CommandType = adCmdStoredProc
Set parameter = .CreateParamete r("LetterNo", adVarChar, _
adParamInput, Len(LetterNo), LetterNo)
.Parameters.App end parameter
End With

notesQuery.Open comNotes, , adOpenKeyset, adLockOptimisti c ************

'Loop for notes
Do While Not notesQuery.EOF

'Run macro in Word document
If notesQuery.Abso lutePosition > 1 Then
wordApp.Run "AddNoteLin e"
End If

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

'Move to next record in data source
notesQuery.Move Next

Loop 'End of notes loop.

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

'Move to next record in data source
lettersQuery.Mo veNext

Loop 'End of letters loop

'Close record set
lettersQuery.Cl ose
comLetters.Canc el

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

[NeoPa]

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.

[Genalube]

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.

[NeoPa]

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 ;).

[MMcCarthy]

try ...

 
lettersQuery.Close
Set lettersQuery = Nothing
 

and so on.

[Genalube]

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 misunderstandin g. 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.

[NeoPa]

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.

[MMcCarthy]

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

[Genalube]

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?

[MMcCarthy]

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