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?
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.
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.