Results 1 to 2 of 2
  1. #1
    Lounger
    Join Date
    Aug 2001
    Location
    Chewelah, Washington, USA
    Posts
    31
    Thanks
    0
    Thanked 0 Times in 0 Posts

    A Big Thank You re:Make Tables/TransferSpreadsheet (2002)

    Just a big thank you for Hans, Francois, and the other moderators. I can't believe that I actually got something to work. After many, many hours of checking and rereading numerous posts, the module below takes 160 items per client record, filters all the records by date and sends them over to Excel worksheets that links to a second worksheet with 7700 formulas on it, breaking everything about by percentages for various subjects for four counties and an overall total. All I have to do, besides entering the data is type in a beginning date and ending date on a form. Since my agency doesn't control the form that starts this process, it is the easiest way I could figure out to make it work. Without the great questions, the wonderful answers, and the search function on this board, it would not have been possible. I can't thank you folks enough.

    Sue <img src=/S/bow.gif border=0 alt=bow width=15 height=15> <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>
    Still clueless, but less so than before


    Private Sub cmdOK_Click()

    On Error GoTo ErrorHandler

    Dim strSQL1 As String
    Dim strSQL2 As String

    'Using the first of the CRECORDS tables, makes a second table filtered by date.
    strSQL1 = "SELECT tblCRECORDS.* INTO tmakCRECORDS FROM tblCRECORDS WHERE (((tblCRECORDS.DateReview) Between [txtStartDate] And [txtEndDate]));"

    DoCmd.RunSQL strSQL1
    DoCmd.SetWarnings False

    'Using the second of the CRECORDS tables, makes a second table filtered by date
    strSQL2 = "SELECT tblCRECORDS2.* INTO tmakCRECORDS2 FROM tblCRECORDS INNER JOIN tblCRECORDS2 ON tblCRECORDS.CRECORDSID = tblCRECORDS2.CRECORDSID WHERE (((tblCRECORDS.DateReview) Between [txtStartDate] And [txtEndDate]));"

    DoCmd.RunSQL strSQL2

    'A series of queries that transfers the database information to sixteen worksheets
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec1ACtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec1BCtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec1COtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec1DCtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec2ACtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec2BCtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec2COtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec2DCtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec3ACtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec3BCtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec3COtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec3DCtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec4ACtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec4BCtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec4COtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"
    DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, "qryCRECORDSSec4DCtmak", "Z:SueCRECORDSEXCELCRECORDSSheets.xls"

    DoCmd.Close

    ErrorHandlerExit:
    Exit Sub

    ErrorHandler:
    MsgBox "Error No: " & Err.Number & "; Description: " & Err.Description
    Resume ErrorHandlerExit

    End Sub

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

    Re: A Big Thank You re:Make Tables/TransferSpreadsheet (2002)

    Thanks for your kind words!

Posting Permissions

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