Stopping the recordset cycle

[davideitaly]

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?

TOTALE is again rubbish remained from the code I copied from another access forum. I tried to erase it but the automation always stops there, probably because WITH gets undeclared if I erase it.

Yes, of course contracts are made almost totally of boilerplate text, the only changing parts are those I would like to take from the form: (lecturer's) name, surname address, ID number, followed by the course and subject(s) data. Name and surname are often repeated within the text.

[June7]

You should be able to entirely delete TOTALE line or at least comment it with an apostrophe.

[davideitaly]

You should be able to entirely delete TOTALE line or at least comment it with an apostrophe.

Ok, I did it and it runs, but the problem remains.

If I change "recordset" with something else ("Rst"), it doesn't recognize the SQL and following:



If I keep "Recordset", with "Recordset.Close" at the end of the code, when I get back to the form I have the usual blocked-unknown result.

If I totally erase "Recordset.Close" at the end of the code, when I get back to the form I have as result only the record I printed and the other ones disappear, forcing me to close and re-open the form.

I anyway kept at the top the additional "Option explicit", as suggested by Micron.

[davideitaly]

Ok, I solved myself.
I just added at the end the instruction to re-open a recordset making reference to the main form-table:

    ETC...

    Recordset.Close

    Wrd.Application.WordBasic.MsgBox "Esportazione terminata", "Esportazione dati da Access"
    
    Set Recordset = CurrentDb.OpenRecordset("Select * from Anagrafica_docenti")


End Sub

I would like to thank June7 for his precious support and Micron too.

Thanks a lot indeed.

[davideitaly]

Hi guys. Sorry for re-open this thread but there's a new unexpected problem going on. With the code that seemed to work well:

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) & "#")

it happens that some contract dates (DATA_CONTRATTO) result as "null", even if I typed them in.

For instance, for the lecturer X, if I put dates from 26th March to 31th March, the automation populates the word model without any problem. With older or more recent dates, it stops at the DATA_CONTRATTO with the message: "invalid use of Null". But the field is not null!

Is there any limitation that I can't see in the code that sets the recordset?

Thank you.

[davideitaly]

Hi guys. Sorry for re-open this thread but there's a new unexpected problem going on. With the code that seemed to work well:

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) & "#")

it happens that some contract dates (DATA_CONTRATTO) result as "null", even if I typed them in.

For instance, for the lecturer X, if I put dates from 26th March to 31th March, the automation populates the word model without any problem. With older or more recent dates, it stops at the DATA_CONTRATTO with the message: "invalid use of Null". But the field is not null!

Is there any limitation that I can't see in the code that sets the recordset?

Thank you.

Ok, did it: i correct the code this way:

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

[davideitaly]

Ok, did it: i correct the code this way:

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

Nope... with this change, the code doesn't select the last contract date, but all the ones related to a lecturer.
The problem remains. Need some urgent help, please!

[davideitaly]

I reply here my problem. Hope June, who provides the code, would help me...

Hi guys. Sorry for re-open this thread but there's a new unexpected problem going on. With the code that seemed to work well:

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) & "#")

it happens that some contract dates (DATA_CONTRATTO) result as "null", even if I typed them in.

For instance, for the lecturer X, if I put dates from 26th March to 31th March, the automation populates the word model without any problem. With older or more recent dates, it stops at the DATA_CONTRATTO with the message: "invalid use of Null". But the field is not null!

Is there any limitation that I can't see in the code that sets the recordset?

Thank you.