Creating multiple spreadsheets from form in access.

[AJ]

I have a form in which I have a list and a button. On clicking the button and
making a selection from the list box, it imports all the data corresponding
to the selection and transfers the records into a spreadsheet. This is the
code:
Private Sub Create_Report_by_Mandate_Manager_Click()
On Error GoTo Err_Create_Report_by_Mandate_Manager_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "Summary Report - Incompleted Mandates"

stLinkCriteria = "[Sr Mgr / Mgr]=" & "'" & Me![List0] & "'"
DoCmd.OpenForm stDocName, , , stLinkCriteria
DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel9, stDocName,
"C:\IncMandates\Mandates - " & CStr([List0]) & ".xls", True

Exit_Create_Report_by_Mandate_Manager_Cl:
Exit Sub

Err_Create_Report_by_Mandate_Manager_Click:
MsgBox Err.Description
Resume Exit_Create_Report_by_Mandate_Manager_Cl

End Sub

Now how do I generate a separate spreadsheet for every entry in the list by
one click of the button.
thanks.

 

[Alex Dybenko]

Hi,
you can make a loop to go through listbox items (or open recordset and go
through its records, if listbox based on a table), modify stLinkCriteria for
each item and then do export the way you have now

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[AJ]

Thanks for your reply.
How could I go about doing what you said. I am not a VBA expert.

 

[AJ]

the listbox is based on a query.

 

[Alex Dybenko]

Hi,
look in access help for OpenRecordset, MoveNext - these samples give you
idea how to make a loop

and then instead of
stLinkCriteria = "[Sr Mgr / Mgr]=" & "'" & Me![List0] & "'"

use
stLinkCriteria = "[Sr Mgr / Mgr]=" & "'" & rst!MyField & "'"

this one and 2 more lines put inside the loop

--
Best regards,
___________
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

 

[AJ]

thanks again