[RESOLVED] Object variable or with block variable not set

[DubweiserTM]

Hi Everybody !

I got this error(Title) and I dont know where to begin the "Debug operation" ???

Here is my code (Sorry some words are in french), the error occurs at the Bolded/Underlined line

'Initialisation de la Connection et du Recordset
        'Instanciation des variables
        Set rs = New ADODB.Recordset
        Set cn = New ADODB.Connection
        'Connection et ouverture de la Database
        cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & "Data Source=" & lblProjetSource.Caption
        
'Afficher les données (Onglet Informations)
        'Ouverture de la Connection
        cn.Open
        'Ouverture du Recordset
        rs.Open "Informations", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        rs.MoveFirst
                frMain.txtNoProjet.Text = rs.Fields("NoProjet").Value
                frMain.cboInfoRepresentant.Text = rs.Fields("Representant").Value
                frMain.cboInfoTypeClient.Text = rs.Fields("TypeClient").Value
                frMain.txtNom(0).Text = rs.Fields("NAcheteur1").Value
                frMain.txtPrenom(0).Text = rs.Fields("PAcheteur1").Value
                frMain.txtNom(1).Text = rs.Fields("NAcheteur2").Value
                frMain.txtPrenom(1).Text = rs.Fields("PAcheteur2").Value
                frMain.txtCompagnie.Text = rs.Fields("Compagnie").Value
                frMain.txtContactP.Text = rs.Fields("PContact").Value
                frMain.txtContactN.Text = rs.Fields("NContact").Value
                frMain.txtAdresse(0).Text = rs.Fields("AddActuelle").Value
                frMain.txtVille(0).Text = rs.Fields("VilleActuelle").Value
                frMain.txtCodePostal(0).Text = rs.Fields("CPostalActuel").Value
                frMain.txtAdresse(1).Text = rs.Fields("AddFutur").Value
                frMain.txtVille(1).Text = rs.Fields("VilleFutur").Value
                frMain.txtCodePostal(1).Text = rs.Fields("CPostalFutur").Value
                frMain.txtTelephone(0).Text = rs.Fields("TelephoneR").Value
                frMain.txtTelephone(1).Text = rs.Fields("TelephoneC").Value
                frMain.txtTelephone(2).Text = rs.Fields("TelephoneB").Value
                frMain.txtTelephone(3).Text = rs.Fields("Telecopieur").Value
                frMain.txtCourriel.Text = rs.Fields("Courriel").Value
                frMain.lblDatedujour.Caption = rs.Fields("Date").Value
        'Fermeture de la connection ADO
        rs.Close
        cn.Close
 
'Afficher les données Cotation
        'Ouverture de la Connection
        cn.Open
        'Ouverture du Recordset
        rs.Open "Cotation", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        rs.MoveFirst
                frMain.txtVente.Text = rs.Fields("Vente").Value
                frMain.txtEntente.Text = rs.Fields("Entente").Value
                frMain.txtEchange.Text = rs.Fields("Echange").Value
                frMain.txtAccompte.Text = rs.Fields("Accompte").Value
                frMain.txtTaxe1 = rs.Fields("Taxe1").Value
                frMain.txtTaxe2 = rs.Fields("Taxe2").Value
                varCreditTaxe = rs.Fields("TaxeRed").Value
                If rs.Fields("TaxesReduitesApp").Value = "True" Then
                        frMain.chkTaxesRed.Value = vbChecked
                Else
                        frMain.chkTaxesRed.Value = vbUnchecked
                End If
        'Fermeture de la connection ADO
        rs.Close
        cn.Close
 
'Afficher les données Maison
        'Ouverture de la Connection
        cn.Open
        'Ouverture du Recordset
        rs.Open "Maison", cn, adOpenKeyset, adLockPessimistic, adCmdTable
        rs.MoveFirst
                frMain.cboMaisonType.Text = rs.Fields("TypeMaison").Value
                [U][B]frMain.cboMaisonModele.Text = rs.Fields("ModeleMaison").Value[/B][/U]
                frMain.cboMaisonNbChambre.Text = rs.Fields("NbreChambre").Value
                frMain.txtDim(0).Text = rs.Fields("DimMaison1").Value
                frMain.txtDim(1).Text = rs.Fields("DimMaison2").Value
                frMain.txtDim(2).Text = rs.Fields("DimAnnexeA1").Value
                frMain.txtDim(3).Text = rs.Fields("DimAnnexeA2").Value
                frMain.txtDim(4).Text = rs.Fields("DimAnnexeB1").Value
                frMain.txtDim(5).Text = rs.Fields("DimAnnexeB2").Value
                frMain.txtDim(6).Text = rs.Fields("DimGarage1").Value
                frMain.txtDim(7).Text = rs.Fields("DimGarage2").Value
                frMain.txtSuperficie.Text = rs.Fields("Superficie").Value
                frMain.cboMaisonGarantie.Text = rs.Fields("Garantie").Value
        'Fermeture de la connection ADO
        rs.Close
        cn.Close

Thanks in advance !

[Pasvorto]

First off, I would open the adReadOnly. You don't seem to be changing the values.

[leinad31]

if the table name is correct and the combo box exists and is named correctly then the field value is probably null... concatenate a zero length string to convert nulls before assigning to text property

frMain.cboMaisonModele.Text = rs.Fields("ModeleMaison").Value & ""

[DubweiserTM]

First off, I would open the adReadOnly. You don't seem to be changing the values.

What I have to replace with adReadOnly ?

[Pasvorto]

Example

rs.Open "Informations", cn, adOpenKeyset, adReadOnly, adCmdTable

[DubweiserTM]

if the table name is correct and the combo box exists and is named correctly then the field value is probably null... concatenate a zero length string to convert nulls before assigning to text property

frMain.cboMaisonModele.Text = rs.Fields("ModeleMaison").Value & ""

I tried this, and it's not okay...

I tried also to put a value frMain.cboMaisonModele.Text = "Whatever" and the error still occurs but on the next line...

[Pasvorto]

I would also set thinsg = nothing when not needed.

rs.close
set rs = nothing


then

Set rs = New ADODB.Recordset
rs.Open "Informations", cn, adOpenKeyset, adReadOnly, adCmdTable

etc
etc

[DubweiserTM]

Example

rs.Open "Informations", cn, adOpenKeyset, adReadOnly, adCmdTable

Now I got an error on this line...

[Pasvorto]

adLockReadOnly... my bad

[DubweiserTM]

adLockReadOnly... my bad

I change with this and the error still occurs...

[leinad31]

Are you sure the table has records? You never did any tests... you assumed at least one record exists for all rs.Open

[DubweiserTM]

Are you sure the table has records? You never did any tests... you assumed at least one record exists for all rs.Open

Yeah Im really sure !

[Pasvorto]

Try, after the rs.close,

set rs = nothing

then set rs = new adodb.recordset
before doing next db access.

The readonly thing would not affect your initial error, it would just cause less impact on the db.

[DubweiserTM]

Try, after the rs.close,

set rs = nothing

then set rs = new adodb.recordset
before doing next db access.

The readonly thing would not affect your initial error, it would just cause less impact on the db.

About this, I did it but it's not on the part of the code I posted...

[leinad31]

Is this a public recordset?

Any chance your setting it to nothing elsewhere in your project such as in the change event of the combo's?

You could try using a recordset with procedure lvl scope

[Pasvorto]

If you have "hidden" some off the code, you must post it. We are only able to go on what you post. In it, you set rs = new adodb.recordset at the start of the code, not each time you needed it. Maybe, you are carrying some 'memory-residual' bits. By setting it to nothing when you close it, it should be cleared from memory. However, that implies that you will need a "set rs = new adodb.recordset" the next time you wish to open rs.

[DubweiserTM]

Is this a public recordset?

Any chance your setting it to nothing elsewhere in your project such as in the change event of the combo's?

You could try using a recordset with procedure lvl scope

I think you're right with your suggestion...

When I got the error 91, is it possible to know at this moment if rs = nothing ?

[DubweiserTM]

If you have "hidden" some off the code, you must post it. We are only able to go on what you post. In it, you set rs = new adodb.recordset at the start of the code, not each time you needed it. Maybe, you are carrying some 'memory-residual' bits. By setting it to nothing when you close it, it should be cleared from memory. However, that implies that you will need a "set rs = new adodb.recordset" the next time you wish to open rs.

Here is the missing part of code, sorry !

'Vider les paramètres
        Set rs = Nothing
        Set cn = Nothing
        
        'Fermeture de la Form
        Unload Me

[Pasvorto]

I think if rs were set to nothing, you would get the error on this line

rs.Open "Maison", cn, adOpenKeyset, adLockPessimistic, adCmdTable

[DubweiserTM]

I think if rs were set to nothing, you would get the error on this line

rs.Open "Maison", cn, adOpenKeyset, adLockPessimistic, adCmdTable

Is it possible to know moment when the error occurs, if rs is set to nothing ???

[Pasvorto]

Why do you keep opening & closing the connection? Seems like a lot of overhead..

This is spelled correctly? " cboMaisonModele "

[DubweiserTM]

Why do you keep opening & closing the connection? Seems like a lot of overhead..

Because I change the Table, it's not okay ?

This is spelled correctly? " cboMaisonModele "

Yeah !

[Pasvorto]

Your 'connection' opens the database. Your 'recordset open' code opens the table. You do not need to keep reconnecting to the db. Try commenting the unecessary connections out and see what happens.

[DubweiserTM]

Your 'connection' opens the database. Your 'recordset open' code opens the table. You do not need to keep reconnecting to the db. Try commenting the unecessary connections out and see what happens.

Cool !

I removed the others cn.open and cn.close, Thanks I appreciate !

[DubweiserTM]

I replaced rs by rs1 and cn by cn1 and now its' okay !

Maybe somewhere in my code the rs and cn are set to nothing...

Thanks for all guys !

[Pasvorto]

Be sure to mark the thread as resolved