Hi,

I have a form in MSAccess that displays a record, when the record displays it determines
whether the displayed record has a comparative quote and loads the displayed record
and the comparative records into an array..this part does not work and I need help
with.

Can anyone help in why this is not working!

Thanks
Code:
Private Sub CheckComparative(QuoteID_int As Long, BusinessID As Long, ProductID As Long)
    'Get the quote(s), load into array and make controls visible if it is comparative.
    On Error GoTo CheckComp_Err
    Dim SQL As String
    Dim Ctr As Long
    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    
    ReDim gComparativeQuotes(0)
    
    SQL = " SELECT DISTINCT top 1  tabQMS_Quotes.ProductID_int, tabQMS_Quotes.BusinessClassID_int, tabQMS_Quotes.QuoteRef_ID,"
    SQL = SQL & " tabQMS_Quotes.Status, tabQMS_Quotes.TransactionType_char, tabQMS_Quotes.AC_NO, tabQMS_Quotes.Contact_No,"
    SQL = SQL & " tabQMS_Quotes.Risk_No, tabQMS_Quotes.RiskName_char, tabQMS_Quotes.Renewal_date, tabQMS_Quotes.QuoteRequired_Date,"
    SQL = SQL & " tabQMS_Quotes.Recieved_date, tabQMS_Quotes.TargetPremium_cur, tabQMS_Quotes.TargetInsurer_char,"
    SQL = SQL & " tabQMS_Quotes.ToCarrier_date, tabQMS_Quotes.Carrier_char, tabQMS_Quotes.CarrierSource_char,"
    SQL = SQL & " tabQMS_Quotes.CarrierQuote_date, tabQMS_Quotes.QuoteReference_char, tabQMS_Quotes.QuotedPremium_cur,"
    SQL = SQL & " tabQMS_Quotes.Currency_char, tabQMS_Quotes.QuoteToBroker_date, tabQMS_Quotes.inceptionDate_date,"
    SQL = SQL & " tabQMS_Quotes.NextDiary_date, tabQMS_Quotes.RiskTransfer_char, tabQMS_Quotes.CreditTerms_char,"
    SQL = SQL & " tabQMS_Quotes.BrkComm_per, tabQMS_Quotes.BDComm_per, tabQMS_Quotes.PolicyNo_char ,  tabQMS_Quotes.ToAgentSet_date,"
    SQL = SQL & " tabQMS_Quotes.ProductCode_char, tabQMS_Quotes.ProductType_char,  tabQMS_Quotes.PreStatement_int,"
    SQL = SQL & " tabQMS_Quotes.PolicyEndDate_date, tabQMS_Quotes.QuoteHandler_char,  tabQMS_Quotes.Handler_char,"
    SQL = SQL & " tabQMS_Quotes.PolicyFee, tabQMS_Quotes.DevExec_char,  tabQMS_Quotes.BDE, tabQMS_Quotes.FeedbackStatus_char,"
    SQL = SQL & " tabQMS_Quotes.InsurerPlaced_char,  tabQMS_Quotes.PremiumPlaced_cur, tabQMS_Quotes.QuoteLogID_Int,"
    SQL = SQL & " tabQMS_Quotes.QABQuoteID_int,  tabQMS_Quotes.LastLoggedDateTime_Date, tabQMS_Quotes.QMSQuoteID_int,"
    SQL = SQL & " tabQMS_Quotes.Handling_Office,  tabQMS_Quotes.Live_Bit FROM tabQMS_Quotes"
    SQL = SQL & " WHERE tabQMS_Quotes.QMSQuoteID_int = '" & QuoteID_int & "' and tabqms_quotes.ProductID_int"
    SQL = SQL & " In (SELECT ProductID_int FROM tabQMS_Quotes As Tmp"
    SQL = SQL & " where ProductID_int <> '" & ProductID & "') order by quoteref_id desc"
           
    'rs.Open SQL, gBass, adOpenForwardOnly, adLockOptimistic
    Set rs = gBass.Execute(SQL)
    If rs.BOF Then
        Me.lblComp.Visible = False
        CompFrame.Visible = False
        cmdPrev.Visible = False
        cmdFirst.Visible = False
        cmdNext.Visible = False
        cmdLast.Visible = False
        'bail out, no need for any further processing
        Set rs = Nothing
        Exit Sub
    Else
        Me.lblComp.Visible = True
        CompFrame.Visible = True
        cmdPrev.Visible = True
        cmdFirst.Visible = True
        cmdNext.Visible = True
        cmdLast.Visible = True
        CurCompRec = 0
    End If
    'load all comparative quotes into gComparativeQuotes
    'Ctr = 0
    'ReDim gComparativeQuotes(0)
    'Do While Not rs.EOF
    '    Ctr = Ctr + 1
    '    ReDim gComparativeQuotes(Ctr)
    '    gComparativeQuotes(Ctr) = rs!QuoteRef_ID
    '    rs.MoveNext
    'Loop
            
    'Loop through the returned rs
    Do While rsSet.BOF
       'ReDim gQuotes(UBound(gQuotes))
    ReDim Preserve gComparativeQuotes(UBound(gComparativeQuotes) + 1)
        'add the ID into the current gQuotes
        gComparativeQuotes(UBound(gComparativeQuotes)) = rs!QuoteRef_ID
        rsSet.MoveNext
    Loop
    
    If UBound(gComparativeQuotes) > 0 Then
        'show the first record
        CurCompRec = 1
        GetQuote (gComparativeQuotes(CurCompRec))
     Else
        GoTo CheckComp_Err
    End If
     'Loop through the returned rs
     'CurCompRec = 1
     'lblRecInfo = "1 of " & Ctr
    Set rs = Nothing
    
Exit Sub
CheckComp_Err:
    msgbox "Error: " & Err.Number & " " & Err.Description
    Exit Sub
    Resume
End Sub