Loop through form and export report for each customer

  • Thread starter dhoover via AccessMonster.com
  • Start date
D

dhoover via AccessMonster.com

I have a form (formA) in which I select a customer from a dropdown box and it
populates the form with the customers info (customer1). I then click a
button which opens formB and populates those fields with the customer1's
information. The code below then clicks a button on formB which runs a
report based on the customer selected in formB, saves the report as an rtf
and then saves the file in the specified client folder. What I need to be
able to do is to click one button that runs this report for each client in my
table and saves each in the specified folder. I'm thinking I need to loop
thorough each customer, but I'm not great at VBA so I need some help.



On Error GoTo Err_cmdCustInfo_Click

Dim strCISFolderPath As String
Dim strFolder5 As String
Dim strfolderpath5 As String
strFolder5 = Dir(strfolderpath5, vbDirectory)

If CISFolderPath <> "" Then
strfolderpath5 = CISFolderPath
strFolder5 = Dir(strfolderpath5, vbDirectory)
End If

'Open the Customer Information form and go to customer selected in Reporting
form

Dim intCustomer As Integer
intCustomer = cmbCustomer

DoCmd.OpenForm "Customer Info"
DoCmd.FindRecord intCustomer, acEntire, , acSearchAll, , acCurrent


DoCmd.OpenReport "Customer Instructions", acViewPreview, , "[ID]=" & [ID]



DoCmd.OutputTo acOutputReport, "Customer Instructions", acFormatRTF,
strfolderpath5 & "\CIS.rtf", False

DoCmd.Close acOutputReport, "customer Instructions", False
DoCmd.Close acForm, "customer info"











Exit_cmdCustInfo_Click:
Exit Sub

Err_cmdCustInfo_Click:
MsgBox Err.Description
Resume Exit_cmdCustInfo_Click
 
S

Steve Sanford

How are you selecting which customers to print?

Are all of the RTF files saved:
1) in the same folder with different names,
2) to individual folders with the same name (CIS.rtf) or
3) to individual folders with the individual names?

If individual folders, how are the folders selected?
 
D

dhoover via AccessMonster.com

That's the problem, I can't get it to loop through to select each customer.
I was able to put a drop down box on the form and select the customer from
there and save the file, but I don't want to do this for 600+ clients.

The rtf files are saved in different folders, with different names. This is
determined by the strfolderpath5 = CISFolderPath
strFolder5 = Dir(strfolderpath5, vbDirectory) which is defined in another
piece of the code. I'm able to get it to name the files and put it in the
correct folder, my problem is getting the code to loop through each customer
with one click rather than selecting a customer and then running the code and
then selecting another customer and then running the code and so on.



Steve said:
How are you selecting which customers to print?

Are all of the RTF files saved:
1) in the same folder with different names,
2) to individual folders with the same name (CIS.rtf) or
3) to individual folders with the individual names?

If individual folders, how are the folders selected?
I have a form (formA) in which I select a customer from a dropdown box and it
populates the form with the customers info (customer1). I then click a
[quoted text clipped - 41 lines]
MsgBox Err.Description
Resume Exit_cmdCustInfo_Click
 
S

Steve Sanford

OK, each customer has a file saved in seperate folders with different names.
Would you post 3 or 4 examples?

Do you want to save a report for each of the 600+ customers every time you
click the button? How are you going to select which customers you want to
print?


--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


dhoover via AccessMonster.com said:
That's the problem, I can't get it to loop through to select each customer.
I was able to put a drop down box on the form and select the customer from
there and save the file, but I don't want to do this for 600+ clients.

The rtf files are saved in different folders, with different names. This is
determined by the strfolderpath5 = CISFolderPath
strFolder5 = Dir(strfolderpath5, vbDirectory) which is defined in another
piece of the code. I'm able to get it to name the files and put it in the
correct folder, my problem is getting the code to loop through each customer
with one click rather than selecting a customer and then running the code and
then selecting another customer and then running the code and so on.



Steve said:
How are you selecting which customers to print?

Are all of the RTF files saved:
1) in the same folder with different names,
2) to individual folders with the same name (CIS.rtf) or
3) to individual folders with the individual names?

If individual folders, how are the folders selected?
I have a form (formA) in which I select a customer from a dropdown box and it
populates the form with the customers info (customer1). I then click a
[quoted text clipped - 41 lines]
MsgBox Err.Description
Resume Exit_cmdCustInfo_Click
 
D

dhoover via AccessMonster.com

Sorry, maybe I'm not explaining this correctly. The problem I am having is
how to select the customers. I need the code used to loop through each
customer in the table so that when the button is clicked it would export a
report for als 600 customers.

ex.
Customer1/Folder/doc.rtf
Customer2/Folder/doc.rtf


Steve said:
OK, each customer has a file saved in seperate folders with different names.
Would you post 3 or 4 examples?

Do you want to save a report for each of the 600+ customers every time you
click the button? How are you going to select which customers you want to
print?
That's the problem, I can't get it to loop through to select each customer.
I was able to put a drop down box on the form and select the customer from
[quoted text clipped - 22 lines]
 
S

Steve Sanford

In your code, what is "CISFolderPath"? Is it on a form? Do you manually enter
a path?

If you select 3 customers,
"Jon Dough",
"James Brown" &
"Jenny Jones",

their individual reports would be saved in
"Customer1/Folder/doc.rtf",
"Customer2/Folder/doc.rtf" &
"Customer3/Folder/doc.rtf"???

Given the names above, what would the folder and file name be???

What happens if there are 2 customers with the same name? (two Dave Smiths?)



--
Steve S
--------------------------------
"Veni, Vidi, Velcro"
(I came; I saw; I stuck around.)


dhoover via AccessMonster.com said:
Sorry, maybe I'm not explaining this correctly. The problem I am having is
how to select the customers. I need the code used to loop through each
customer in the table so that when the button is clicked it would export a
report for als 600 customers.

ex.
Customer1/Folder/doc.rtf
Customer2/Folder/doc.rtf


Steve said:
OK, each customer has a file saved in seperate folders with different names.
Would you post 3 or 4 examples?

Do you want to save a report for each of the 600+ customers every time you
click the button? How are you going to select which customers you want to
print?
That's the problem, I can't get it to loop through to select each customer.
I was able to put a drop down box on the form and select the customer from
[quoted text clipped - 22 lines]
MsgBox Err.Description
Resume Exit_cmdCustInfo_Click
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top