export the current record in an Access to Excel

I need to export the current record in an Access to an Excel spreadsheet. I have a macro that uses TransferSpreadsheet to export ALL records and it is working fine.  How would I create a query that includes only the currect record?  If I can do that then I think I can use the query as the source in TransferSPreadsheet; and this should accomplish what I need.
petersclafaniAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
If you have a hidden text box for the ID, you could do something like this:

Public Function ExportCurrentRecord()
    Dim qd As QueryDef
    Dim strSQL As String
 
        strSQL = "Select * from tblYourTable Where [ID] = " & me.txtID
        Set qd = CurrentDb.CreateQueryDef("qryTemp", strSQL)
       
       '********* Transfer spreadsheet using qryTemp

    'Delete the temporary query when done
        CurrentDb.QueryDefs.Delete "qryItemsTemp"
   
End Function
mbizupCommented:
Mistake in the last line...

Public Function ExportCurrentRecord()
    Dim qd As QueryDef
    Dim strSQL As String  
        strSQL = "Select * from tblYourTable Where [ID] = " & me.txtID
        Set qd = CurrentDb.CreateQueryDef("qryTemp", strSQL)
       
       '********* Transfer spreadsheet using qryTemp

    'Delete the temporary query when done
        CurrentDb.QueryDefs.Delete "qryTemp"
   
End Function
petersclafaniAuthor Commented:
Hi 2 questions:

- Somewhere is your code, I think I need to specify the button name,  correct?  Where would I do this?  I tried this but it does not seem to work:  Public Function mybutton_ExportCurrentRecord()

- Do I replace this '********* Transfer spreadsheet using qryTemp with the macro that exports the spreadsheet?
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

mbizupCommented:
Well,  Id suggest to to it all in VBA without a macro.  The vba command to export the query is:

docmd.TransferSpreadsheet acExport,,"qryTemp","c:\transfer.xls",true
                       name of the query-----------^          ^---File Name   ^---------Puts column titles in the first row (false if you don't want this)


Put the following code in the command button's click event (let me know if you need help with this)
private sub myButton_click()
     ExportCurrentRecord
end sub

Place this function (I've added the transfer spreadsheet command) in a module  (just name the module something different from the function, otherwise you'll run into problems):
         
Public Function ExportCurrentRecord()
    Dim qd As QueryDef
    Dim strSQL As String  
        strSQL = "Select * from tblYourTable Where [ID] = " & me.txtID
        Set qd = CurrentDb.CreateQueryDef("qryTemp", strSQL)
       
       '********* Transfer spreadsheet using qryTemp
       docmd.TransferSpreadsheet acExport,,"qryTemp","c:\transfer.xls",true

    'Delete the temporary query when done
        CurrentDb.QueryDefs.Delete "qryTemp"
   
End Function

petersclafaniAuthor Commented:
OK, I've got everything set as you suggested.  

I ran into this error message: "Compile Error: User-defined type not defined". I did some research on this and found out that I needed to check "Microsoft DAO 3.6 Object Library" in References.  So I did that and am no longer seeing that error message.

I am now getting:  Compile error:  Invalid use of Me keyword.  I saw one commented on the web that stated you can't use Me in a module (http://p2p.wrox.com/topic.asp?TOPIC_ID=10386).

By the way, Ichanged the object/field names as shown below ...

___________________________
Public Function ExportCurrentRecord()
    Dim qd As QueryDef
    Dim strSQL As String
        strSQL = "Select * from deal Where [PKID] = " & Me.PKID
        Set qd = CurrentDb.CreateQueryDef("qryTemp", strSQL)
       
       '********* Transfer spreadsheet using qryTemp
       DoCmd.TransferSpreadsheet acExport, , "qryTemp", "c:\transfer.xls", True

    'Delete the temporary query when done
        CurrentDb.QueryDefs.Delete "qryTemp"
   
End Function

mbizupCommented:
Sorry about that.  You can do this in two ways:

1.  Place the above code in the form's module (with all the form event code)
or 2.  Place it in a module as I'd suggested (I prefer this approach, but it is a matter of taste) and specify the full form name:

Public Function ExportCurrentRecord()
    Dim qd As QueryDef
    Dim strSQL As String
        strSQL = "Select * from deal Where [PKID] = " & Forms!YourFormName.PKID    '**** supply the name of your form here
        Set qd = CurrentDb.CreateQueryDef("qryTemp", strSQL)
       
       '********* Transfer spreadsheet using qryTemp
       DoCmd.TransferSpreadsheet acExport, , "qryTemp", "c:\transfer.xls", True

    'Delete the temporary query when done
        CurrentDb.QueryDefs.Delete "qryTemp"
   
End Function


Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
petersclafaniAuthor Commented:
Got it, thanks!
mbizupCommented:
Glad to help!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.