I am using MS Access 2007 and I'm trying to make multiple tables/files from one table.
The master table is titled "SmrtShtQ1-18". In this table, there are 3 owners: anyone, Patel, and Hector.
I would like to create a file for each owner with their records only. The intent is so I can email each of them their report separately.
I created a module, "Module1" where the query qryOwnerReports, is populated with "anyone@myCompany.com". The module moves the query results to tblTemp.
What I need to do next, and I don't know how to do it, is to have the next name Patel@myCompany.com create a file for Patel's records, then move on to Hector, until a file is made for each owner. To keep it simple, I am keeping the new table in the database instead of exporting the file to folder.
I'm sure there is more than one way to do this (no query, move next, etc.) and I'm not concerned with method is used, just that the separate files are made. The end result will be 3 tables with the name of the owner as the table name.
I have attached the sample database I have been working with.
Can you please assist me with this?
Option Compare Database
Public Function CreateUserReviewsRpt()
Dim rs As Recordset
Dim rs1 As Recordset
Dim rs2 As Recordset
Dim db As Database
Set db = CurrentDb
Set rs = db.OpenRecordset("SmrtShtQ
Set rs = db.OpenRecordset("qryOwner
Set rs2 = CurrentDb.OpenRecordset("t
If rs.RecordCount > 0 Then
Do Until rs.EOF
If Not rs.NoMatch Then
If Not IsNull(rs!Owner) Then
rs2!Name = rs!Name
rs2!Workspace = rs!Workspace
rs2!Type = rs!Type
rs2!Owner = rs!Owner
rs2!SharedTo = rs!SharedTo
rs2!SharedToPermission = rs!SharedToPermission
rs2!LastViewedDate = rs!LastViewedDate