×
INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Contact US

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Students Click Here

Selecting and copying all records on a form

Selecting and copying all records on a form

Selecting and copying all records on a form

(OP)
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.

RE: Selecting and copying all records on a form

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.

Kathryn


RE: Selecting and copying all records on a form

(OP)
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.

RE: Selecting and copying all records on a form

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.

Kathryn


RE: Selecting and copying all records on a form

(OP)
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

RE: Selecting and copying all records on a form

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?

Kathryn


RE: Selecting and copying all records on a form

(OP)
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

RE: Selecting and copying all records on a form

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.

Kathryn


RE: Selecting and copying all records on a form

(OP)
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

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members! Already a Member? Login

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close