Stopping the recordset cycle

[davideitaly]

Hi all. I am from Italy.


I have a form with the anagraphical data of lecturers. Then a subform with the courses they have to teach in. This is how the form looks like (sorry it's in Italian):



As you may see, this lecturer is supposed to teach two different subjects (Navigazione = Navigation and Macchine = Engine) in the same course (n.1 Ed. 38).
I have an export function (top right, called "Esporta occasionale") which fills a Word model (.dotx) with bookmarks with all the relevant data. It is supposed to insert once the course data, then listing the two different UDs (Unità didattiche = didactical Units) and subjects taught by the lecturer. I take the anagraphical data from the main form and the other data from the subform through a recordset referring to a query. This is the code ruling everything:

Private Sub ESPORTA_OCCASIONALE_Click()
Dim Wrd As Word.Application, Doc As Word.Document
Dim Rst As DAO.Recordset
Dim Modello As String, NomeFile As String, i As Integer
Dim Record As String, SQL As String
Dim Tbl As String * 1
Dim TotRiga As Currency, TOTALE As Currency
Dim ReplSel As Boolean

    Modello = CurrentDb.Name
    Modello = Left(Modello, Len(Modello) - Len(Dir(Modello))) & "modello_occasionale.dotx"

    On Error Resume Next
    Set Wrd = GetObject(, "Word.Application")
    If Err.Number = 429 Then
        Set Wrd = CreateObject("Word.Application")
    End If
    
    On Error GoTo 0
    
    Wrd.Visible = True
    Wrd.Activate
    ReplSel = Wrd.Options.ReplaceSelection
    Wrd.Options.ReplaceSelection = True
    Set Doc = Wrd.Documents.Add(Modello)
    Doc.Activate
    
    Doc.Bookmarks("Nome").Select
    Wrd.Selection.TypeText Me.NOME

    Doc.Bookmarks("Cognome").Select
    Wrd.Selection.TypeText Me.COGNOME
   
ETC...    

SQL = "FROM Anagrafica_docenti INNER JOIN (Anagrafica_materie INNER JOIN (Anagrafica_corsi INNER JOIN Anagrafica_art_corsi ON Anagrafica_corsi.ID_Anagrafica_corsi = Anagrafica_art_corsi.CORSO) ON Anagrafica_materie.ID_anagmaterie = Anagrafica_art_corsi.MATERIA) ON Anagrafica_docenti.ID_Anagrafica_docenti = Anagrafica_art_corsi.ID_Anagrafica_docenti" & _
"ORDER BY Anagrafica_art_corsi.DATA_CONTRATTO DESC , Anagrafica_art_corsi.PROTOCOLLO DESC;"

Set Recordset = CurrentDb.OpenRecordset("Select * from sqlbookmark")

    Doc.Bookmarks("Data_contratto").Select
    Wrd.Selection.TypeText Me!DATA_CONTRATTO
      
    Doc.Bookmarks("prot").Select
    Wrd.Selection.TypeText Me!PROTOCOLLO
    
ETC...

    Doc.Bookmarks("UD").Select
    
    With Wrd.Selection.Paragraphs.TabStops
        .Add Wrd.Application.CentimetersToPoints(3.5), wdAlignTabRight
        .Add Wrd.Application.CentimetersToPoints(4.25), wdAlignTabRight
    End With
    
    If Not Recordset.BOF Then
       Tbl = Chr$(3.5)
        With Recordset
            TOTALE = 0 '
            While Not .EOF
                Record = "UD" & Tbl & !UD & Tbl & _
                !Nome_materia
            Wrd.Selection.TypeText Record & vbCrLf
                .MoveNext
            Wend
           End With
    End If

    Doc.Bookmarks("dal").Select
    Wrd.Selection.TypeText Me!DAL
    
    Doc.Bookmarks("al").Select
    Wrd.Selection.TypeText Me!AL
    
    Doc.Bookmarks("Numero_ore2").Select
    Wrd.Selection.TypeText Me!NUMERO_ORE
    
    Doc.Bookmarks("costo_orario").Select
    Wrd.Selection.TypeText Me!COSTO_ORARIO
    
    Doc.Bookmarks("tot").Select
    Wrd.Selection.TypeText Me![NUMERO_ORE] * [COSTO_ORARIO]
    
    Doc.Bookmarks("Data_contratto2").Select
    Wrd.Selection.TypeText Me!DATA_CONTRATTO
    
    Doc.Bookmarks("prot2").Select
    Wrd.Selection.TypeText Me!PROTOCOLLO

    Wrd.Application.WordBasic.MsgBox "Esportazione terminata", "Esportazione dati da Access"
End Sub

The problem occurs from "Doc.Bookmarks("UD").Select" on... The code inserts ALL the subjects taught by all the lecturers recorded in the database, and not only those of the selected lecturer. This is the weird result on the word file:



In the selected case, the subjects should be only two... So I would need it to insert the subjects related ONLY to the selected surname, or from the record referring to the same lecturer ID. In other words I have to tell the code to stop cycling throught the recordset, limiting its insertion to the subjects referred to one ID or surname.

Any suggestion on how to do that?

Thank you.

Ciao!

Davide

[June7]

Perhaps apply filter criteria in SQL statement that limits the recordset to only the necessary records. Something like: "Select * from sqlbookmark WHERE LecturerID=" & Me.Lecturer

The SQL string variable is not used. Why is it declared and populated?

[Micron]

I would say it's because you have passed all the records to the recordset that you use to populate the Word document

Set Recordset = CurrentDb.OpenRecordset("Select * from sqlbookmark")

You should not use "Recordset" to define a recordset variable (at least it looks like you have done this). This also indicates your modules do not have OPTION EXPLICIT turned on by default because it also appears to be undeclared. That would not be good. Perhaps you have declared "Recordset" as a variable somewhere else.

Check your code against this list of words that you should not use as variables.
http://allenbrowne.com/AppIssueBadWord.html

[orange]

Have you considered selecting the Lecturer from a list/combo and generating the report for that lecturer?
You could loop through such a list and report only those records applicable to the individual.
I think that is what June is suggesting also.

[davideitaly]

Perhaps apply filter criteria in SQL statement that limits the recordset to only the necessary records. Something like: "Select * from sqlbookmark WHERE LecturerID=" & Me.Lecturer

The SQL string variable is not used. Why is it declared and populated?

Good God, it was so simple! It works! Thank you.
The problem is that I am a beginner with SQL and VBA code. I copied and personalized the code from a forum where the populating routine was applied to a table.
So the problem is solved, but I can't understand the other hints arrived after yours... I will try to understand them to improve the code...
Thank you!

[davideitaly]

Perhaps apply filter criteria in SQL statement that limits the recordset to only the necessary records. Something like: "Select * from sqlbookmark WHERE LecturerID=" & Me.Lecturer

The SQL string variable is not used. Why is it declared and populated?

Hi, I tried the solution in different cases and I realized I need another "limit" to put into SQL. Other than limiting the cycling to the selected Lecturer through the ID (succeeded), I should ask the code to populate with only the data of the most recent contract ("Data_contratto" in the subform). Is it possible?

Thank you again!

[June7]

It's possible, a little complicated but certainly possible.

"SELECT * FROM sqlbookmark WHERE LecturuerID=" & Me.Lecturer & " AND Data_contratto=#" & DMax("Data_contratto", "sqlbookmark", "LecturerID=" & Me.Lecturer) & "#"

But exactly how is the subform filtered to provide the correct records? Instead of building the same SQL in VBA, maybe use the subform's recordset. The syntax depends on location of code. If it is behind main form: Set Recordset = Me.subformname.Form.Recordset

But I am confused. Why are the Word bookmarks set to values from subform instead of recordset? If code is behind main form then these references to subform fields/controls should fail.

And regarding variable Tbl - I have never seen a variable declared that way.

Micron is advising not to use the word 'Recordset' as a variable name because it is a reserved word. This variable has not been declared in the posted code. Every code module should have 'Option Explicit' in the module header. https://docs.microsoft.com/en-us/dot...icit-statement

[davideitaly]

It's possible, a little complicated but certainly possible.

"SELECT * FROM sqlbookmark WHERE LecturuerID=" & Me.Lecturer & " AND Data_contratto=#" & DMax("Data_contratto", "sqlbookmark", "LecturerID=" & Me.Lecturer) & "#"

But exactly how is the subform filtered to provide the correct records? Instead of building the same SQL in VBA, maybe use the subform's recordset. The syntax depends on location of code. If it is behind main form: Set Recordset = Me.subformname.Form.Recordset

But I am confused. Why are the Word bookmarks set to values from subform instead of recordset? If code is behind main form then these references to subform fields/controls should fail.

And regarding variable Tbl - I have never seen a variable declared that way.

Micron is advising not to use the word 'Recordset' as a variable name because it is a reserved word. This variable has not been declared in the posted code. Every code module should have 'Option Explicit' in the module header. https://docs.microsoft.com/en-us/dot...icit-statement

Hi.
Your code again perfectly solved the problem. You're great, thank you!
I try to answer your questions, and sorry if I am not too precise, I am a beginner...
Main form and subform are based on two different tables, with other two used as lookup:



Part of the word bookmarks are populated from fields taken from the main form (anagraphical), and part from the fields from the subform. The reference for populating from the subform is a query, that I named "sqlbookmark", collecting all the fields I need to populate the word, ordered by contract date (Data_contratto) and protocol number (prot). Here's the query SQL:

SELECT Anagrafica_docenti.ID_Anagrafica_docenti, Anagrafica_docenti.NOME, Anagrafica_docenti.COGNOME, Anagrafica_docenti.INDIRIZZO, Anagrafica_docenti.[CODICE FISCALE], Anagrafica_docenti.PIVA, Anagrafica_art_corsi.UD, Anagrafica_materie.Nome_materia, Anagrafica_corsi.Nome_corso, Anagrafica_art_corsi.EDIZIONE, Anagrafica_art_corsi.MODULO, Anagrafica_art_corsi.ANNUALITA, Anagrafica_art_corsi.ANNO, Anagrafica_art_corsi.DATA_CONTRATTO, Anagrafica_art_corsi.PROTOCOLLO, Anagrafica_art_corsi.TITOLO_CORSO, Anagrafica_art_corsi.COMM, Anagrafica_art_corsi.NUMERO_ORE, Anagrafica_art_corsi.DAL, Anagrafica_art_corsi.AL, Anagrafica_art_corsi.COSTO_ORARIO
FROM Anagrafica_materie INNER JOIN (Anagrafica_corsi INNER JOIN (Anagrafica_docenti INNER JOIN Anagrafica_art_corsi ON Anagrafica_docenti.ID_Anagrafica_docenti = Anagrafica_art_corsi.ID_Anagrafica_docenti) ON Anagrafica_corsi.ID_Anagrafica_corsi = Anagrafica_art_corsi.CORSO) ON Anagrafica_materie.ID_anagmaterie = Anagrafica_art_corsi.MATERIA
ORDER BY Anagrafica_art_corsi.DATA_CONTRATTO DESC , Anagrafica_art_corsi.PROTOCOLLO DESC;

As you may see, the recordset is already built from the subform's SQL.

The code is actually behind a button put in the mainform: top-right, called "Estrai occasionale".

As for the variable "Tbl" it is just junk remained from the cleaning up of the original code... sorry.

Actually I didn't got the meaing about the reserved word "recordset". I will try go through the link you gave me (thank you!).

But before I have to solve a strange problem: after extraction and word populating, the database goes mad... some fields disappear and show a #Nome?, and the database can't be browsed anymore if not closing and re-open it...

[June7]

That error means Access does not recognize the field names the controls are bound to. Something appears to be changing the form's RecordSource so the fields are no longer available. This might be related to the Recordset variable but I am not sure. Change the Recordset variable to some other name and see what happens.

[davideitaly]

Ok, I will check it with care. Thank you!

[davideitaly]

That error means Access does not recognize the field names the controls are bound to. Something appears to be changing the form's RecordSource so the fields are no longer available. This might be related to the Recordset variable but I am not sure. Change the Recordset variable to some other name and see what happens.

I still have one (last I hope) problem.
Everything starts with a form + subform connected with two tables. When I open it, I can browse and navigate through all the records (370) related to lecturers, etc.
Once I extract the data to populate a word file through the "estrai occasionale" button (top right of the form), I save the populated word and when I get back to the form I can't browse it anymore as it was at the beginning.
I am sure the problem is in the management of recordset closing.
This is the code (I cleaned it up):

Private Sub ESPORTA_OCCASIONALE_Click()
Dim Wrd As Word.Application, Doc As Word.Document
Dim Rst As DAO.Recordset
Dim Modello As String, NomeFile As String, i As Integer
Dim Record As String, SQL As String
Dim Tbl As String * 1
Dim ReplSel As Boolean

    Modello = CurrentDb.Name
    Modello = Left(Modello, Len(Modello) - Len(Dir(Modello))) & "modello_occasionale.dotx"

    On Error Resume Next
    Set Wrd = GetObject(, "Word.Application")
    If Err.Number = 429 Then
        Set Wrd = CreateObject("Word.Application")
    End If
    
    On Error GoTo 0
    
    Wrd.Visible = True
    Wrd.Activate
    ReplSel = Wrd.Options.ReplaceSelection
    Wrd.Options.ReplaceSelection = True
    Set Doc = Wrd.Documents.Add(Modello)
    Doc.Activate
    
    Doc.Bookmarks("Nome8").Select
    Wrd.Selection.TypeText Me.NOME

   OTHER POPULATING... ETC...

SQL = "FROM Anagrafica_docenti INNER JOIN (Anagrafica_materie INNER JOIN (Anagrafica_corsi INNER JOIN Anagrafica_art_corsi ON Anagrafica_corsi.ID_Anagrafica_corsi = Anagrafica_art_corsi.CORSO) ON Anagrafica_materie.ID_anagmaterie = Anagrafica_art_corsi.MATERIA) ON Anagrafica_docenti.ID_Anagrafica_docenti = Anagrafica_art_corsi.ID_Anagrafica_docenti" & _
"ORDER BY Anagrafica_art_corsi.DATA_CONTRATTO DESC , Anagrafica_art_corsi.PROTOCOLLO DESC;"

Set Recordset = CurrentDb.OpenRecordset("Select * from sqlbookmark WHERE ID_Anagrafica_docenti=" & Me.ID_Anagrafica_docenti & " AND Data_contratto=#" & DMax("Data_contratto", "sqlbookmark", "ID_Anagrafica_docenti=" & Me.ID_Anagrafica_docenti) & "#")

    Doc.Bookmarks("Data_contratto").Select
    Wrd.Selection.TypeText Me!DATA_CONTRATTO
      
   OTHER POPULATING... ETC...
    
    Doc.Bookmarks("prot2").Select
    Wrd.Selection.TypeText Me!PROTOCOLLO

    Recordset.Close

    Wrd.Options.ReplaceSelection = ReplSel

    Wrd.Application.WordBasic.MsgBox "Esportazione terminata", "Esportazione dati da Access"

If I keep at the end "recordset.close" or "recordset.close: recordset = nothing", when I get back to the form I have this:



It's like the database contained only one unknown record...

If I erase the recordset.close line, after the populating procedure I manage to get back to the record I worked on, but again I can't browse anymore or search other records. Like it could see only the record I proceeded.

In both cases, the entire content of the tables shows if I close and re-open the form, that it's a weird procedure.

Is there a way to have, after the word populating procedure, the database and its forms-tables restored as they were? What's the trick at the end of the code?

Sorry if I pose stupid questions, but I am a beginner...

Happy Easter everyone!

Davide

[June7]

I thought you were going to change the variable name from Recordset to something else, like maybe simply rs?

Why do you need to export data to Word document?

[Micron]

the variable declaration was changed; the use of recordset (Set Recordset =) was not. Still fairly obvious that Option Explicit may still not be turned on. Nevertheless, it wasn't added to this module if it was turned on.
@davideitaly - simply turning this option on does nothing for any existing code. You have to manually add it to existing procedures, usually right under the existing Option statement, as in

Option Compare
Option Explicit

[davideitaly]

the variable declaration was changed; the use of recordset (Set Recordset =) was not. Still fairly obvious that Option Explicit may still not be turned on. Nevertheless, it wasn't added to this module if it was turned on.
@davideitaly - simply turning this option on does nothing for any existing code. You have to manually add it to existing procedures, usually right under the existing Option statement, as in

Option Compare
Option Explicit

Hi all and happy Easter.

I tried both solutions, the one from June7 and the other from Micron.

In the first case, i renamed from "recordset" to "Rs", but the automation didn't work. Following the debug message, it stops populating when it meets the SQL declaration, here's the new code:

Private Sub ESPORTA_OCCASIONALE_Click()
Dim Wrd As Word.Application, Doc As Word.Document
Dim Rs As DAO.Recordset
Dim Modello As String, NomeFile As String, i As Integer
Dim Record As String, SQL As String
Dim Tbl As String * 1
Dim ReplSel As Boolean


    Modello = CurrentDb.Name
    Modello = Left(Modello, Len(Modello) - Len(Dir(Modello))) & "modello_occasionale.dotx"


    On Error Resume Next
    Set Wrd = GetObject(, "Word.Application")
    If Err.Number = 429 Then
        Set Wrd = CreateObject("Word.Application")
    End If
    
    On Error GoTo 0
    
    Wrd.Visible = True
    Wrd.Activate
    ReplSel = Wrd.Options.ReplaceSelection
    Wrd.Options.ReplaceSelection = True
    Set Doc = Wrd.Documents.Add(Modello)
    Doc.Activate
    
    Doc.Bookmarks("Nome8").Select
    Wrd.Selection.TypeText Me.NOME


    Doc.Bookmarks("Cognome8").Select
    Wrd.Selection.TypeText Me.COGNOME
   
    ETC...


SQL = "FROM Anagrafica_docenti INNER JOIN (Anagrafica_materie INNER JOIN (Anagrafica_corsi INNER JOIN Anagrafica_art_corsi ON Anagrafica_corsi.ID_Anagrafica_corsi = Anagrafica_art_corsi.CORSO) ON Anagrafica_materie.ID_anagmaterie = Anagrafica_art_corsi.MATERIA) ON Anagrafica_docenti.ID_Anagrafica_docenti = Anagrafica_art_corsi.ID_Anagrafica_docenti" & _
"ORDER BY Anagrafica_art_corsi.DATA_CONTRATTO DESC , Anagrafica_art_corsi.PROTOCOLLO DESC;"


Set Rs = CurrentDb.OpenRecordset("Select * from sqlbookmark WHERE ID_Anagrafica_docenti=" & Me.ID_Anagrafica_docenti & " AND Data_contratto=#" & DMax("Data_contratto", "sqlbookmark", "ID_Anagrafica_docenti=" & Me.ID_Anagrafica_docenti) & "#")


    Doc.Bookmarks("Data_contratto").Select
    Wrd.Selection.TypeText Me!DATA_CONTRATTO
      
    Doc.Bookmarks("prot").Select
    Wrd.Selection.TypeText Me!PROTOCOLLO
    
ETC...


    Doc.Bookmarks("UD").Select
    
    If Not Rs.BOF Then
        With Rs
            TOTALE = 0
            While Not .EOF
                Record = "UD" & Tbl & !UD & Tbl & _
                !Nome_materia
            Wrd.Selection.TypeText Record & vbCrLf
                .MoveNext
            Wend
           End With
    End If


ETC....


    Rs.Close


    Wrd.Application.WordBasic.MsgBox "Esportazione terminata", "Esportazione dati da Access"

In the second case, I added "Option explicit" under "Option Compare", as suggested by Micron, restoring "recordset" where I put "Rs". Here the process stops where I set the code to repeat the fields UD and materia (subject) in the same line:

 If Not Rs.BOF Then        With Rs
            TOTALE = 0
            While Not .EOF
                Record = "UD" & Tbl & !UD & Tbl & _
                !Nome_materia
            Wrd.Selection.TypeText Record & vbCrLf
                .MoveNext
            Wend
           End With
    End If

In particular, debug finds a problem in "TOTALE = 0".

To answer June7: suppose you have to register in the database every new lecturer your organization recruits. The database is used to record his anagraphicals, the course where he's supposed to lecture and the subject(s) that he may teach, that can be more than one for one course (example: mathematics basics, mathematic advanced, and so on...).
If a lecturer's anagraphics are already present in the database, it allows you to add only the new course where he's supposed to teach, with subjects, and so on.
The added value of this form is that, through the buttons top-right of the form, you can populate, save and print the related recruitment contract with the lecturer, which is little complex and request the repetition of some fields content more than one time. Consider that we do a single contract for every course a lecturer is planned to teach in, so producing a contract is very frequent. Having an automation ruling everything is a great time saving for operators: they just have to fill the database without repeating every time the editing of the contract word file. In a click they can populate it, save it, print it, and send it to the lecturer for signature.
That's why I have the need to have the database restored after every contract production: operators usually have the need to step to the following lecturer.
At the moment, June7 suggestions lead me to a perfect solution, except that final phase...

Thanks for your precious understanding and help.

Davide

[June7]

What is TOTALE? A field or a variable? It is not a declared variable. Option Explicit requires all variables to be declared.

Your contracts have extensive formatted boiler plate text? Hence the use of Word?