Access ADO - weird problem

Chris Quinn

Our company provide gift vouchers in several denominations - I wrote this code to split an amount into the smallest number of denominations - the first stored procedure returns a recordset of the available denominations for the voucher type, sorted in descending order of value e.g. £20 £10 £5 £1 The first SP only returns denominations that are less than the total amount of the transaction, and less than or equal to the preferred denomination - You can request £175 with a preferred denomination of £20, in which case you would get 8x£20, 1x£10 and 1x £5 - if you request a preferred denomination of £10, you would get 17x£10 and 1x£5. Each of these quantities and denominations must be stored against the order for stock control purposes (amongst other things)

InsertSplit = True
Set cmd = New ADODB.Command
With cmd
    .ActiveConnection = mcnnParam
    .CommandType = adCmdStoredProc
    .CommandText = "dbo.usp\_GetSplitDenoms"
    .Parameters.Refresh
    .Parameters("@Voucher\_Code") = sVoucherCode
    .Parameters("@Amount") = dAmount
    .Parameters("@PreferredDenom") = iDenom
End With
Set rstSplit = New ADODB.Recordset
With rstSplit
    .Open cmd, , adOpenStatic, adLockReadOnly
    curAwardTotal = dAmount
    curAmountToGo = curAwardTotal
    Do While Not .EOF
        iCount = Int(curAmountToGo / !\[denomination\])
        iVoucherAmount = iCount \* !\[denomination\]
        curAmountToGo = (curAmountToGo - iVoucherAmount)
        If iVoucherAmount > 0 Then
            Set cmdRIVDenom = New ADODB.Command
            With cmdRIVDenom
                .ActiveConnection = mcnnParam
                .CommandType = adCmdStoredProc
                .CommandText = "dbo.usp\_tblRequestItemVoucherDenom\_insert"
                .Parameters.Refresh
                .Parameters("@request\_item\_id") = iRequestItemID
                .Parameters("@voucher\_code") = rstSplit!\[voucher\_code\]
                .Parameters("@currency\_code") = rstSplit!\[currency\_code\]
                .Parameters("@cost\_centre\_code") = strCostCentre
                .Parameters("@denomination") = rstSplit!\[denomination\]
                .Parameters("@count\_of\_denom") = iCount
                .Parameters("@amount") = iVoucherAmount
                .Execute
            End With
            Set cmdRIVDenom = Nothing
        End If
        If curAmountToGo <= 0 Th

PIEBALDconsult

subtle bugs that you've found and fixed If you're asking a question, go to the proper forum.

Chris Quinn

I did fix it by changing it from a forwardonly recordset - what I didn't do was explain it!

==================================== Transvestites - Roberts in Disguise! ====================================