Results 1 to 4 of 4
  1. #1
    New Lounger
    Join Date
    Jan 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Invalid Bookmark ADO (Office XP)

    The code below is raw code testing an idea I had for searching for cheapest pricing. I know that I am wasting resources at this time opening forms and stuff.

    The issue I am having is that for the first two records in rst, rstCost works fine. However, for some reason when it gets to the third record it should return ItemDeail=10, the bookmark property is returning invalid. I am wondering if this has anything to do with the fact the itemDetail is now in double digits and all previous records return single digits.

    Code:
    --------------------------------------------------------------------------------

    Private Sub cmdEstimate_Click()
    'We need to create an estimate finding the lowest priced quote for each item
    'This cannot be done in query language so we have to iterate using code in small blocks until we have found every single item
    Dim rst As ADODB.Recordset
    Dim rstCost As ADODB.Recordset
    Dim curCost As Currency
    Dim strSQL As String
    Dim strSQLCost As String
    Dim lngItemQuote As Long
    Dim varBookmark As Variant

    Set rst = New ADODB.Recordset
    Set rstCost = New ADODB.Recordset
    strSQL = "SELECT tblOrderQuoteDetail.OrderDetailID, tblOrderQuoteDetail.Quantity, tblOrderQuoteDetail.CostPrice, " & _ "tblOrderQuoteDetail.StockDescription, tblOrderQuoteDetail.ReferenceNumber, " & _
    "tblOrderQuoteDetail.DeliveryDate, tblOrderQuoteDetail.DeliveryAddress, " & _
    "tblOrderQuoteDetail.OrderSent FROM tblOrderQuoteDetail " & _
    "WHERE (((tblOrderQuoteDetail.ReferenceNumber)='" & Me.cboJob & "')); "

    'Debug.Print strSQL

    With rst
    .Open strSQL, CurrentProject.Connection, adOpenKeyset, adLockPessimistic
    Do Until .EOF 'Find cheapest price in form and tick box

    DoCmd.OpenForm "frmStockDescription", OpenArgs:=.Fields("StockDescription") 'Grab the lowest cost and drop on record in subform

    strSQLCost = "SELECT tblItemQuote.ItemQuote, tblItemDetail.Stock, tblItemQuote.Item, " & _
    "tblItemQuote.Supplier, tblItemQuote.QuoteNumber, tblItemQuote.Cost, " & _
    "tblItemQuote.Job, tblItemQuote.QuoteDate, tblItemQuote.ContactForQuote, " & _
    "tblItemQuote.QuoteExpiryDate, tblItemQuote.SelectedQuote " & _
    "FROM tblItemDetail INNER JOIN tblItemQuote " & _
    "ON tblItemDetail.ItemDetailID = tblItemQuote.Item " & _
    "WHERE tblItemDetail.stock = " & .Fields("StockDescription") & ";"

    rstCost.Open strSQLCost, CurrentProject.Connection, adOpenKeyset, adLockPessimistic

    Do Until rstCost.EOF

    If curCost = 0 Then
    lngItemQuote = rstCost.Fields("ItemQuote")
    curCost = rstCost.Fields("Cost")
    varBookmark = rstCost.Bookmark

    ElseIf curCost > rstCost.Fields("Cost") Then
    varBookmark = IsNull(varBookmark)
    lngItemQuote = rstCost.Fields("ItemQuote")
    curCost = rstCost.Fields("Cost")
    varBookmark = rstCost.Bookmark
    End If

    Debug.Print rstCost.Fields("ItemQuote")
    rstCost.MoveNext

    Loop

    rstCost.Bookmark = varBookmark
    .Fields("CostPrice") = lngItemQuote
    .Update
    rstCost.Fields("SelectedQuote") = True
    rstCost.Update
    rstCost.Close

    DoCmd.Close acForm, "frmStockDescription"
    .MoveNext

    Loop

    .Close
    End With

    Set rst = Nothing

    Set rstCost = Nothing

    End Sub
    --------------------------------------------------------------------------------


    The error is occuring always on the same record and always on rstCost.Bookmark = varBookmark

    Anyone, please help? I have tried compact and repair and debug and compact and repair.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid Bookmark ADO (Office XP)

    1) It's a bit hard to visualize the problem from seeing just the code, but I think you have to reset curCost in the outer loop. Above the line

    Do Until rstCost.EOF

    insert

    curCost = 0

    2) I would delete the line

    varBookmark = IsNull(varBookmark)

    It doesn't serve any purpose as far as I can see.

    3) I don't understand why you open and close a form in the outer loop.

  3. #3
    New Lounger
    Join Date
    Jan 2002
    Posts
    10
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Invalid Bookmark ADO (Office XP)

    I solved it just now by importing all objects into a new database.

    Thank you for your comments. There is a lot f extraneous code in this as it is still at sketch stage. You know where I mean, can I make this work, will it work. That is why I was opening and closing the form, as part of the debug I was walking through the code and reading the data off the form. Having said that I actually quite like the effect when the code is running. Potentially this code could iterate several hundred or several thousand times - depending on project, and the flashing of the form may be an alternative to a progress bar?

    Good call curCost=0

  4. #4
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Invalid Bookmark ADO (Office XP)

    You could make the code slightly shorter by initializing curCost to an impossibly high amount:

    curCost = 100000000
    Do Until rstCost.EOF
    If curCost > rstCost.Fields("Cost") Then
    lngItemQuote = rstCost.Fields("ItemQuote")
    curCost = rstCost.Fields("Cost")
    varBookmark = rstCost.Bookmark
    End If
    Debug.Print rstCost.Fields("ItemQuote")
    rstCost.MoveNext
    Loop

    Note: opening a form incurs a large overhead - if your code iterates thousands of times I wouldn't do that. And, after all, you can display a progress bar using SysCmd acSysCmdInitMeter and SysCmd acSysCmdUpdateMeter.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •