Results 1 to 3 of 3
  1. #1
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122

    Question Unanswered: Querydef - New to programatical SQL Access

    I am haveing trouble getting to grips with Querydefs. It's a fairly new thing for me even though I've been employed to handle databases for over a year now.

    I have an Access database that is called upon to carry out a few automation to word activities. These are not a problem for me. were I seem to be comeing unstuck is with the use of querydef and looping through a record set produced by a query in order to find data to act on.

    This is clearly wrong but I am unsure what to do to correct it.

    Code:
    Private Function DoLoop(BM_Loco As String, LongVer As Boolean) As Boolean
    Dim strSQL As String
    Dim rstOrder As Recordset 'QueryDef
    Dim dbProducts As DAO.database
    Dim ThisIsIt
    Dim DoResult As Boolean
    
    On Error GoTo AdoError
    Screen.MousePointer = vbHourglass
    DoResult = False
    
          
        'The Long Version
        '================
          If LongVer = True Then
                strSQL = "[WORD_Long]"
                Set dbProducts = CurrentDb
                Set rstOrder = dbProducts.QueryDefs(strSQL)
                Do While Not (rstOrder.EOF)
                    CopyThis rstOrder.Fields("Long_Note")
                    PasteThis BM_Loco
                    rstOrder.MoveNext
                Loop
                DoResult = True
          Else
          'The Short Version
          '=================
                strSQL = "[WORD_Short]"
                Set dbProducts = CurrentDb
                Set rstOrder = dbProducts.QueryDefs(strSQL)
    'Loop: <NAME OF FEATURE>  >| >| >| <PRICE OF FEATURE> & "+ VAT at 17.5%"
                Do While Not (rstOrder.EOF)
                    ThisIsIt = rstOrder.Fields("Name") & vbTab & _
                    rstOrder.Fields("Price") & " + VAT at 17.5%"
                    TypeThis ThisIsIt, BM_Loco
                    rstOrder.MoveNext
                Loop
                DoResult = True
          End If
      
        rstOrder.Close
        Set rstOrder = Nothing
        cn.Close
        Set cn = Nothing
        
        Screen.MousePointer = vbNormal
    
    GoTo exitme         'skip the error block
    AdoError:
    Screen.MousePointer = vbNormal
    FrErr "Access-to-Word_AutoBM (" & DoResult & ")"
    On Error GoTo exitme
        rstOrder.Close
        Set rstOrder = Nothing
        cn.Close
        Set cn = Nothing
    exitme:
    DoLoop = DoResult
    End Function
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  2. #2
    Join Date
    Sep 2003
    Location
    UK
    Posts
    122
    terns out I'm not passing the query to a record set.

    Anyone able to help now?
    Matt the Hat says: "what!?"
    A child of five could understand this! Fetch me a child of five!
    SCARY HARD CHALLENGE: http://www.dbforums.com/showthread.php?threadid=988682

  3. #3
    Join Date
    Dec 2002
    Location
    Préverenges, Switzerland
    Posts
    3,740
    a few suggestions.

    explicitly use a DAO.recordset...
    dim rstOrder as DAO.recordset 'is a recordset and not a qrydef

    assuming that WORD_Long is a saved query (? yes ??)...
    Set dbProducts = CurrentDb
    Set rstOrder = dbProducts.openrecordset("WORD_Long")

    of if you have a workable SQL select in strSQL
    Set rstOrder = dbProducts.openrecordset(strSQL)

    to loop thru the recordset is as you say...
    do while not rstOrder.EOF
    blah
    rstOrder.movenext
    loop

    to read a field...
    myVariable = rstOrder!fieldName

    to edit a field...
    rstOrder.Edit
    rstOrder!fieldName = myVariable
    rstOrder.update

    to add a record...
    rstOrder.addnew
    rstOrder!thisField = thisVariable
    rstOrder!thatfield = thatVariable
    rstOrder.update

    you can make your life easier (and your code neater) using
    with rstOrder 'once you have set rstOrder
    .addnew
    !thisField = thisVariable
    !thatfield = thatVariable
    .update
    end with 'when you are done with rstOrder

    and you are right with
    rstOrder.close
    set rstOrder = nothing
    set dbProducts = nothing

    izy

Posting Permissions

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