Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2003
    Posts
    123

    Angry Unanswered: Cancel previous operation error

    I keep getting the "You canceled the previous operation" error and I can't tell why. It's happening right at the line where there's a simple DLookup to feed a generic variable "aa". "ResultsDB" is a string variable that is set earlier in the routine, and it is getting set fine.


    Set rcd = dbs.OpenRecordset("SELECT hist_prc.* FROM hist_prc " _
    & "WHERE hist_prc.ticker='" & TestTick & "' " _
    & "AND hist_prc.mon_end Between #" & StartDate & "# And #" & EndDate & "# " _
    & "ORDER BY hist_prc.mon_end;")

    rcd.MoveFirst
    PrevPrd = rcd!mon_end
    PrevPrc = rcd!adj_close
    rcd.MoveNext
    CurrPrd = rcd!mon_end
    CurrPrc = rcd!adj_close
    Do Until rcd.EOF
    With rcd


    'Check if first trade (0 rows=TRD), then start new row

    aa = DCount("ID", ResultsDB, "row_typ='TRD' AND cmpl='Y'")
    If IsNull(aa) Or aa = 0 Then
    CurrQty = DLookup("curr_qty", ResultsDB, "row_type='BEG'")
    CurrCash = DLookup("curr_cash", ResultsDB, "row_type='BEG'")
    PortCont = DLookup("port_cntrl", ResultsDB, "row_type='BEG'")

  2. #2
    Join Date
    May 2005
    Posts
    1,191
    How is the ResultsDB string compiled? Or what is in it?

    Also, try using brackets on your expression:
    aa = DCount("[ID]", ResultsDB, "row_typ='TRD' AND cmpl='Y'")
    Me.Geek = True

  3. #3
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Nick,
    If you're using square brackets do you need the quotes?
    Code:
    aa = DCount("[ID]", ...
    '*OR
    aa = DCount([ID], ...
    George
    Home | Blog

  4. #4
    Join Date
    May 2005
    Posts
    1,191
    Here's an example from the VB help:

    Quote Originally Posted by VB Help
    =DCount("[OrderID]", "Orders", "[ShipRegion] = 'CA'")
    I typically use this format when I use a domain aggregate function, and it works for me. For example:
    strName = Nz(DLookup("[FldUserName]", "TblUsers", "[FldUserLogin] = '" & fOSUserName() & "'"), fOSUserName())
    Me.Geek = True

  5. #5
    Join Date
    Aug 2003
    Posts
    123
    I've determined the error occurs whenevery there is a DLookup, DMax, DCount, etc. using the variable ResultsDB as the domain string. ResultsDB is being populated from a text field in a table. The value is the name of another table that will be used throughout the routine--i.e. where the results of various calculations will be stored. So the name of the table will change each time the routine is run--hence the string variable. For example, instead of running:
    x=DLookup("field1","table1","type='BUY'")
    I want to be able to use
    x=DLokup("field1",ResultsDB,"type='BUY'")
    because it might be looking in table1, or table2, or table35. The Expression and Criteria are always going to be the same, just the Domain changes each time.

  6. #6
    Join Date
    May 2005
    Posts
    1,191
    I ask again... How is your ResultsDB being defined? What's the code?

    I was able to get a simplistic example to work using the following:

    Dim TableName As String
    TableName = "Table1"

    MsgBox Nz(DLookup("[Field1]", TableName, "[Field2] = 'Value2'"), "zls")
    Me.Geek = True

  7. #7
    Join Date
    Aug 2003
    Posts
    123
    Like I already said, it's getting populated from a text field in another recordset.

    ResultsDB = rcd!rslt_db

    But once the code gets to a DLookup or similar statement where ResultsDB is the domain string, it errors out.

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    ...text field in another recordset
    Shouldn't the syntaxt therefore be?
    Code:
    ResultsDB = rcd("rslt_db")
    George
    Home | Blog

  9. #9
    Join Date
    Apr 2004
    Location
    Derbyshire, UK
    Posts
    789
    Provided Answers: 1
    Hi

    Only two thoughts on this

    I always use DCount determine if a record(s) exists (if I do not need the actual field value) as DCount always returns an integer (or long?) which eliminated the Null problem.

    Shouldn't it be

    ResultsDB = "rcd!rslt_db" as ResultsDB is a string

    or is rcd!rslt_db not the table name but a control/record field holding the table name ? If so what does rcd!rslt_db equal ?

    Sorry three thoughts to answer an earlier question

    You only need [] if the there are spaces in the field/domain names but you always need "" to define the string.


    MTB

  10. #10
    Join Date
    May 2005
    Posts
    1,191
    Quote Originally Posted by MikeTheBike
    You only need [] if the there are spaces in the field/domain names but you always need "" to define the string.
    Thanks Mike. I couldn't remember how that went, and when in doubt I tend to use the most code to ensure it'll work; better safe than sorry!
    Me.Geek = True

Posting Permissions

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