By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,156 Members | 1,069 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,156 IT Pros & Developers. It's quick & easy.

Export records to different rtf files.

P: 1

I am using Microsoft Access 2003 running over Windows XP.
I have a table of all audit audit findings relating to different departments in my organization. The table contains a column that indicates to which department each record relates.

my intention is to create a proccess in which all records relating to a certain department will be selected and exported to an rtf document (So that i may send it to the department managers).

I have no knowledge in VBA.

What would be the best way to approach the problem?

Thank you,

Elad Shuster
Jan 1 '08 #1
Share this Question
Share on Google+
2 Replies

Expert Mod 15k+
P: 31,494
If you want various different files to be created, they will have to be created separately.
This will involve code to :
  • Loop through all departments (using a recordset).
  • Doctor the SQL of a (hopefully temp) query to select a specific department.
This is not trivial for someone new to VBA I'm afraid Elad.
Jan 2 '08 #2

Expert 100+
P: 254
Make a form with a ListBox (lstDept) with a command button (cmdExportAudit). The listbox should contain the names of all the audited departments. Make a query (qryExportAudit) that contains all of the relevant data to export including department name. In the criteria of the department field link it to the form textbox ([txtDeptName]). You will notice in the Transfertext line I am exporting to the UNC path, dept folder with today's date concatenated on the file name. UNC allows for different drive mappings. I would do a TransferSpreadsheet vice text or build a form for the department head to view the audit results directly from the database. If you put the email address of the dept mgrs in the department list and you you're using outlook as email client (or using smtp protocol on network) you can modify this process to email the results of the query to each mgr.

If you already have forms and queries built then modify accordingly.

Expand|Select|Wrap|Line Numbers
  1. Private Sub cmdExportAudit_Click()
  3. Dim i As Integer
  4. Set ctl = Me.lstDept
  5. i = 0
  6. With ctl
  7. Do While i < .ListCount
  8. Me.txtDeptName = ctl.ItemData(i)
  9. DoCmd.TransferText acExportDelim, , "qryExportAudit", "\\servername\foldername\audit\" & Me.txtDeptName & "\audit_" & Format(Date(),"mmddyyyy" & ".rtf"     
  10. i = i + 1
  11. Loop
  12. End With
  14. End Sub
Jan 2 '08 #3

Post your reply

Sign in to post your reply or Sign up for a free account.