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


0
 
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
0
 
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
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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

0
 
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

0
 
petersclafaniAuthor Commented:
Got it, thanks!
0
 
mbizupCommented:
Glad to help!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.