• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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.
0
petersclafani
Asked:
petersclafani
  • 5
  • 3
1 Solution
 
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
 
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
Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

 
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
 
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
 
petersclafaniAuthor Commented:
Got it, thanks!
0
 
mbizupCommented:
Glad to help!
0

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

  • 5
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now