Selecting and copying all records on a form

[MartinF]

Hi, i am currently having trouble selecting and copying all of the records off a form using VB, I know it can be done easily of the menu's but I am trying to do it all from a button. I am using Access'97 and using the RunCommand action. I can get it to select all the records, no problem, but its then says that the copy command is unavailable. I know this must be quite simple. [sig][/sig]

 

[kathryn]

Could you give a bit more background on what you are trying to do? For example, why are you copying all the records on the form? Where are you copying them to?

I'm sure that we will be able to figure it out with a bit more information. [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MartinF]

Hi Kathryn, thanks for responding

What i am exactly trying to do is to quickly copy a set of records from a form straight onto an Excel spreadsheet. I have done this before using a 'docmd.transferspreadsheet' action to transfer entire tables onto a spreadsheet but in this instance the data has to go onto a specific range of a specific worksheet, and I figured the easiest way would be to have a button on the form that copied all of the records and then then user could go to their Excel sheet and paste them where the required. [sig][/sig]

 

[kathryn]

OK, where are the records on the form coming from? My thinking is that if the records are from a query or the result of a filter, you could actually transfer the query.

[sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MartinF]

The records come from an SQL statement (It's basically a select query with sum's and totals in it) in the 'Record Source' property of the form.

Martin [sig][/sig]

 

[kathryn]

The TransferSpreadsheet command can take a query name, so you can just send the underlying query. If you have a SQL statement as the recordsource of the form, you can do one of two things.

1. If the SQL doesnt' change, save the SQL as a query.

2. If the SQL changes, you can create a QueryDef in code, put your SQL in the QueryDef, save the QueryDef, and then use transferspreadsheet.

Does this sound like what you are looking for? [sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MartinF]

Sort of, the only thing is, is that the destination Excel workbook will have a different filename every time, so then I would have to use a common OpenDialog box to enable the user to point the tarnsfer spreadsheet action to the correct file. As they would normally have the file open at this point i was thinking that if they had the records copied to the clipboard it would be a bit quicker and easier to just paste them in.

Martin [sig][/sig]

 

[kathryn]

OK, I missed the clipboard aspect of your problem. All the code that I can find on the Clipboard only copy a string, and you need to copy a set of records. I'm stumped.

Only other thought I had is: Is there any rhyme or reason to the name of the excel spreadsheet? Is it related in any way to any of the data or to a criteria in your query?

Sorry I couldn't be of more help.

[sig]<p>Kathryn<br><a href=mailto: > </a><br><a href= > </a><br> [/sig]

 

[MartinF]

No, the name of the spreadsheet is not related to the data in anyway.

Thanks anyway Kathryn for spending time looking into my problem. My users will just have to copy off the Edit menu manualy.

Martin [sig][/sig]