Results 1 to 5 of 5
  1. #1
    Join Date
    Jan 2004
    Posts
    115

    Unanswered: update querys sql and expoert data

    I would like to update my Query's sql based on the list box selection.
    Here is what I have, but I get different errors.
    Can you please see what is missing or extra?
    Thanks

    Private Sub cmdExportToQB_Click()
    On Error GoTo cmdExportToQB_Click_Err
    Dim str as String
    Dim dbsCurrent As Database
    Dim qryTest As QueryDef
    Set qryTest = CurrentDb.QueryDefs("QryExportTemp")
    str = "SELECT qryExportToQBFilter.InternalID, qryExportToQBFilter.Type, qryExportToQBFilter.Title, qryExportToQBFilter.County_Block_Lot, qryExportToQBFilter.QBbillingDetails, qryExportToQBFilter.Price, qryExportToQBFilter.Free, qryExportToQBFilter.TitleCoName, qryExportToQBFilter.TypeId, qryExportToQBFilter.TitleNo, qryExportToQBFilter.TitleCoID FROM qryExportToQBFilter WHERE (((qryExportToQBFilter.TypeId) <> 1)) ORDER BY qryExportToQBFilter.Type and qryExportToQBFilter.InternalID in ("
    For i = 0 To ListExport.ItemsSelected.Count - 1
    str = str & ListExport.ItemData(ListExport.ItemsSelected(i))
    If i = ListExport.ItemsSelected.Count - 1 Then
    str = str & ")"
    Else
    str = str & ","
    End If
    Next i
    qryTest.SQL = str
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, qryTest.Name, "C:\temp\ExcelFiles\ExportToQB.xls"

    cmdExportToQB_Click_Exit:
    On Error Resume Next
    Exit Sub

    cmdExportToQB_Click_Err:
    Resume cmdExportToQB_Click_Exit

  2. #2
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    And the error ypu get is....
    the actual sql that triggers tge sql is..... NOT the vba that generates the sql but the value of the variable containing the sql.



    My initial impression is that you have a where clause, followed by an order by followed by another fragment of sql which looks like a bit more of the where clause. If so that isnt going to work. The order by comes after the where, which comes after the join (if any) which comes after the list of columns to be selected.

    Your iteration through the list box assumes that there will always be values in that listbox, that may well be valid business logic but there is a risk that your code will result in invalid code if the user doesnt select something from the list box
    I'd rather be riding on the Tiger 800 or the Norton

  3. #3
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    ..its gong to be something like
    Code:
    Private Sub cmdExportToQB_Click()
    On Error GoTo cmdExportToQB_Click_Err
    Dim str as String
    Dim dbsCurrent As Database
    strSQL = "SELECT InternalID, Type, Title, County_Block_Lot, QBbillingDetails, Price, Free, TitleCoName, TypeId, TitleNo, TitleCoID FROM qryExportToQBFilter
     WHERE qryExportToQBFilter.TypeId <> 1 "
    if ListExport.ItemsSelected.Count > 1 then 'test to see if we have any items selected
       strWhereClause =   " and InternalID in ("
       For i = 0 To ListExport.ItemsSelected.Count - 1
           strWhereClause = strWhereClause & ListExport.ItemData(ListExport.ItemsSelected(i)) & ", "
        next i
        strWhereClause = left(strWhereClause,len(strWhereClause - 2)    'chop off the trailing two characters (a comma + space )
        strWhereClause = strWhereClause & ")" 'don't forget to close the 'in' clause grouping
    endif 'finished handling any /. all itmes selected
    strSQL = strSQL & strWhereClause & " ORDER BY Type "
    
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel7, strSQL, "C:\temp\ExcelFiles\ExportToQB.xls"
    'think there shoudl be soemthign else here, otherwise you will alwasy goto the error handler
    exit sub 'probably shoudl be this
    
    cmdExportToQB_Click_Exit:
    On Error Resume Next
    Exit Sub
    
    cmdExportToQB_Click_Err:
    Resume cmdExportToQB_Click_Exit
    I'd rather be riding on the Tiger 800 or the Norton

  4. #4
    Join Date
    Jan 2004
    Posts
    115
    Thanks for your help. It looks that you can't export from sql. You need to have querydef. (Based on other posts). I tried your way but I get error 7871 does qty exist?

    Any solution?
    Thanks

  5. #5
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So save the sql in the querydefs collection......
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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