loading records into an array

[holly]

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

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

[si_the_geek]

I can see two problems...

You have "TOP 1" in your SQL statement, which means it will only return one record (as well as "DISTINCT", which doesn't make sense).

Your loop has "Do While rsSet.BOF ", which means the loop will only run if there are no records (and then have various errors). It should be "Do While Not rsSet.EOF "

[holly]

OK Thanks, I will change the code...

On your second suggestion re: Top 1.....I think I need to explain..... Below is
a sample of the data I'm working on, from the spreadsheet I need to load into
the array and display on the screen logid 9065 and 9066 as they are quotes from two different carriers... Hope that makes sense....

Thanks