Results 1 to 6 of 6
  1. #1
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103

    Unanswered: 'DoCmd.OutputTo' Error

    When using 'DoCmd.OutputTo' (Excel), I receive a non-numbered message stating 'You selected more records than can be copied onto the Clipboard at one time.' Upon clicking OK, the output operation attempts to continue resulting in a lock-up. How can I cancel the operation completely following this MS Access message and allow a user to continue on, change their parameters, retry, etc?

  2. #2
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    By the time the error is triggered, it may already be too late. Typically, you'd use On Error Goto or On Error Resume Next .. If Err Then to create an "error trap".

    Error traps are good temporary fixes, but you should investigate why you're getting the error and check for that prior to doing the operation. In this case, it sounds like you are exceeding the size Excel can handle (which is 65,536 rows by 256 columns in Excel 2003 per the help file). If you have more than 65,535 records (the first line is for the field names), you've exceeded that limitation. You also can not have more that 256 fields, but I don't think a table can either.

    You should count the number of records first and provide a message to the user if the number is exceeded.

    tc

  3. #3
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    Thanks for the prompt reply. I did try "handling" the error, but as you said, it's probably too late at that point. It's more of a user error because they didn't pair down their data enough with the available parameters.

    So, is there a way to count the records in the select query prior to initiating the 'DoCmd.OutputTo'? I could probably say if record count > 65,535 then display message and exit routine. Excessive columns is definitely not an issue here.

  4. #4
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    Sure there is. Several ways, in fact.

    Quick and dirty: use DCount
    Better: use recordsets

    tc

  5. #5
    Join Date
    Jun 2002
    Location
    Kansas City
    Posts
    103
    That's a fix! Thank you very much.

  6. #6
    Join Date
    Apr 2004
    Location
    outside the rim
    Posts
    1,011
    You are welcome!

    Cheers

Posting Permissions

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