Transfer Spreadsheet

[mvpdep]

Good evening I have created a MACRO function on an 'on click' button that will output a table 'accounts' when clicked.

This outputs the whole table.

My question would be: Is there a way to output only specific fields? If yes to that, also can you pull only specific records?

For example, table accounts has 800 records with 20 fields for each record. Can I set up the macro such that I can pull only 20 specific records and only transfer to excel only four fields (thus column headings)

Any help would be deeply appreciated thanks.

 

[ZmrAbdulla]

OutputTo supports queries too.
You can create a parameterized query with what fields and records you need then use the query instead of table.

________________________________________________________
Zameer Abdulla
Help to find Missing people
All cats love fish but fear to wet their paws..

 

[AccessGuruCarl]

mvpdep

If the 20 specific records are always the same, I'd suggest ZmrAbdulla advice. Create the query then run as needed.

If the criteria changes, I'd build a custom form with the options needed to run the query, then build it on the fly.

Take a look at CreateQueryDef, it should get you started.

AccessGuruCarl
Programmers helping programmers
you can't find a better site.

 

[mvpdep]

Thank you both...AccessGuruCarl please explain CreateQueryDef, sorry not familiar with term

Tx

 

[AccessGuruCarl]

Hi mvpdep,

Here is a quick sample..

Private Sub Command0_Click()

Dim db As Database
Dim qdf As QueryDef
Dim strSQL As String

    Set db = CurrentDb
    
    strSQL = "Select LastName, FirstName, PhoneNumber"
    strSQL = strSQL & " Where Salary > 9"
    
    Set qdf = db.CreateQueryDef("TEMPQRY", strSQL)
    
    'Your code here

    'Delete the qry or an error will occur the next time you run the code.
    DoCmd.DeleteObject acQuery, "TEMPQRY"
    
End Sub

To use this, you need a reference to DAO! Access stopped defaulting to DAO in Office 2000 and above and now uses ADO

Open a code window, click Tools -- References

Scroll down until you see Microsoft DAO x.xx Library where x.xx will be a number depending on the version of Access installed. Click it to add it to the references.

Return to the code window...
Type -- CreateQueryDef -- Highlight it, and select F1 for detailed help!

Hope this gets you pointed in the right direction. Once you learn how to ust this, you'll probally never use a regular query again.(This prevents a user from modifying the query or changing it in the future)

Use the query tabs to build the query. Run it to verify it works! Now view the source, copy and paste it onto your form. (as strSQL) - Delete the query!

Good Luck -


AccessGuruCarl
Programmers helping programmers
you can't find a better site.