Create Excel Worksheet thru Access VBA

[mays0515]

Hi All,

I need some help. I have created a form in Access that queries on a persons last name and sends all the info on the last name to an Excel spreadsheet. I want to send multiple people's data to the worksheet---but when i try to do this, I only get 1 person's data at a time in the worksheet. The data of the person before gets over-written. Here is what I have:

glb_Strsql = "SELECT * from DR_SEARCH_TABLE_Mar WHERE last_name= '" & DrName & "' AND FIRST='" & lstFirst.Value & "' and st = '" & lstState.Value & "' and kenwood_territory = '" & lstTerritory.Value & "' "

Set qdf = CurrentDb.QueryDefs("glb_Strsql")
qdf.SQL = glb_Strsql
qdf.Close
RefreshDatabaseWindow

DoCmd.TransferSpreadsheet _
TransferType:=acExport, _
SpreadsheetType:=8, _
TableName:="glb_Strsql", _
Filename:="C:\Documents and Settings\My Documents\My_Search.xls"
HasFieldNames = Yes


Any help would be greatly appreciated. Thanks!

 

[namliam]

Try creating a list of primary keys first, then when the user has finished selecting the people he/she wants export the records.

Regards

 

[mays0515]

Thanks for the reply. The problem with that is that I think it will be too messy b/c I have more than 1 criteria for the user to do the search. They must select last name, first name, city and state to get their needed data. Any suggestions on how i can make it cleaner?

Thanks!

 

[namliam]

add a field to your table called selected or something (boolean)
Set that to true, then in the end export all trues and reset them to false...

Regards