VBA: ADODB: get the values from RECSET.Fields

Question

My code is posted below. I can't get the values in ranges "test2" and "test3". I have only the value in range "test". Do I need to modify the code ?

Thank you very much for your suggestions !

Public Sub INFO_PROTO(NO_POLICE As String)

Dim RECSET As New ADODB.Recordset
RECSET.Open "select proto.b_perf_cma as b_perf_cma, proto.b_perf_supp_ann as b_perf_supp_ann, proto.b_perf_ctrat_gar as b_perf_ctrat_gar from db_dossier sousc, db_produit prod, db_protocole proto" & _
            " where sousc.no_police = '" & NO_POLICE & "' and sousc.cd_dossier = 'SOUSC' and sousc.lp_etat_doss not in ('ANNUL','A30','IMPAY') and sousc.is_produit = prod.is_produit and sousc.is_protocole = proto.is_protocole ", cnn_Pegase, adOpenDynamic, adLockBatchOptimistic
If Not RECSET.EOF Then
    Worksheets("1 - Feuille de Suivi Commercial").Range("test").Value = RECSET.Fields("b_perf_cma").Value
    Worksheets("1 - Feuille de Suivi Commercial").Range("test2").Value = RECSET.Fields("b_perf_supp_ann").Value
    Worksheets("1 - Feuille de Suivi Commercial").Range("test3").Value = RECSET.Fields("b_perf_ctrat_gar").Value
Else
   Worksheets("1 - Feuille de Suivi Commercial").Range("test").Value = "NC"
    Worksheets("1 - Feuille de Suivi Commercial").Range("test2").Value = "NC"
    Worksheets("1 - Feuille de Suivi Commercial").Range("test3").Value = "NC"
End If
RECSET.Close

End Sub

@TimWilliams I don't have errors. I'm wondering is it right: Worksheets("1 - Feuille de Suivi Commercial").Range("test").Value = RECSET.Fields("b_perf_cma").Value Worksheets("1 - Feuille de Suivi Commercial").Range("test2").Value = RECSET.Fields("b_perf_supp_ann").Value Worksheets("1 - Feuille de Suivi Commercial").Range("test3").Value = RECSET.Fields("b_perf_ctrat_gar").Value — Marie
There's nothing wrong with that code (though it would benefit from some refactoring to remove the repeated Worksheets("1 - Feuille de Suivi Commercial") But is it possible your two missing fields are nulls and not zeros? — Tim Williams
@TimWilliams the missing fields have values 0, but I supposed to have 0 in the ranges. — Marie

Tim Williams Tim Williams · Accepted Answer · 2021-01-27T18:52:33

Try this - it will replace any empty values with zeros

Public Sub INFO_PROTO(NO_POLICE As String)

    Dim RECSET As New ADODB.Recordset
    
    RECSET.Open "select proto.b_perf_cma as b_perf_cma, proto.b_perf_supp_ann as " & _
                " b_perf_supp_ann, proto.b_perf_ctrat_gar as b_perf_ctrat_gar " & _
                "from db_dossier sousc, db_produit prod, db_protocole proto" & _
                " where sousc.no_police = '" & NO_POLICE & "' and sousc.cd_dossier = 'SOUSC' " & _
               " and sousc.lp_etat_doss not in ('ANNUL','A30','IMPAY') and " & _
              "sousc.is_produit = prod.is_produit and sousc.is_protocole = proto.is_protocole ", _
               cnn_Pegase, adOpenDynamic, adLockBatchOptimistic
    'use a With block to remove repetition
    With ThisWorkbook.Worksheets("1 - Feuille de Suivi Commercial")
    
        If Not RECSET.EOF Then
            .Range("test").Value = CheckValue(RECSET.Fields("b_perf_cma").Value)
            .Range("test2").Value = CheckValue(RECSET.Fields("b_perf_supp_ann").Value)
            .Range("test3").Value = CheckValue(RECSET.Fields("b_perf_ctrat_gar").Value)
        Else
            .Range("test").Value = "NC"
            .Range("test2").Value = "NC"
            .Range("test3").Value = "NC"
        End If
    
    End With
    RECSET.Close

End Sub

Function CheckValue(v)
    'Default to zero if null
    CheckValue = iif(Len(v) = 0, 0, v)
End Function

VBA: ADODB: get the values from RECSET.Fields

1 Answers