Expert: Scottgem - 7/16/2009

Question
QUESTION: Hey Scott,
I have table with records that look like this
Smith, Jim    Business    Lunch          $50
Smith, Jim    Business    Travel Expense     $30
Smith, Jim    Legal       Document Assembly  $25
Summers, Tom  Business    Conference         $100
Summers, Tom  Community   Columbia School    $35

I would like to automatically (via a macro/quesry or VBA code) export the records in to 2 tables, basically put Smith records into 1 and Summers into 2.
Is there a way to do that?
I would need to export each of those 2 tables to excel and attach to outlook email.
Please let me know if you need me to describe this 'outrageous' project in detail.
thanks for your help

ANSWER: What you need to do is create a query that filters for each person. You then export that query (using TransferSpreadsheet) to Excel. You do NOT need to, nor should you, create separate tables. You can then create an Outlook e-mail wioth the spreadsheet attached.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Scott, thanks for that answer.
I am not clear on how to create a filter with out specifying the criteria. I have tried using Max and Min
on the name but I am not getting anywhere with that.
thanks
Irina

ANSWER: Well, of course you have to specify criteria. What you are going to need to do is loop through a recordset of unique people. For each person, you use a querydef to replace the saved query with the filtered query for that person. You then export and e-m,ail befopre going on to the next person.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA

---------- FOLLOW-UP ----------

QUESTION: Okay, I may be asking for too much, but I have done a great deal of research and wrote some code, which is not working, it locks my database and I get Not responding
for MSACCESS.I get the pop-up "in the loop" and then nothing happens.

SO if you could take a look at my code, I would greatly appreciate it. If not, may be you could direct me to where you think I have an issue.
Public Function QuertyDef()
Dim dbsIndividualBudget As DAO.Database
Dim rstAttorneys As DAO.Recordset
Dim queryDef As queryDef
Dim attyName As Variant
'Dim outlookApp As Outlook.Application
'Dim objMail As Outlook.MailItem

Set dbsIndividualBudget = CurrentDb
'Set rstAttorneys = dbsIndividualBudget.OpenRecordset("SELECT Distinct IndividualBudget.Name, " & _
         "IndividualBudget.[Unit/Practice Area], IndividualBudget.[Account Name], " & _
         "IndividualBudget.Description, IndividualBudget.[Total Budgeted for 2009]," & _
         "IndividualBudget.[Actual YTD_thru 5/31/09] , IndividualBudget.Remaining_Budget_Balance FROM IndividualBudget where IndividualBudget.Name is not Null")

Set rstAttorneys = dbsIndividualBudget.OpenRecordset("SELECT Distinct IndividualBudget.Name " & _
"FROM IndividualBudget where IndividualBudget.Name is not Null")
 
   On Error Resume Next
   dbsIndividualBudget.QueryDefs.Delete ("Dynamic_Query")
 If rstAttorneys.BOF And rstAttorneys.EOF Then
   MsgBox "No records to process"
 Else
 MsgBox ("in the loop")
   rstAttorneys.MoveFirst
   MsgBox (rstAttorneys.GetRows)
   
   Do Until rstAttorneys.EOF
   attyName = rstAttorneys
   Set queryDef = dbsIndividualBudget.CreateQueryDef("Dynamic_Query", _
   "SELECT Distinct IndividualBudget.Name, " & _
   "IndividualBudget.[Unit/Practice Area], IndividualBudget.[Account Name], " & _
   "IndividualBudget.Description, IndividualBudget.[Total Budgeted for 2009]," & _
   "IndividualBudget.[Actual YTD_thru 5/31/09] , IndividualBudget.Remaining_Budget_Balance FROM IndividualBudget where " & attyName & "ORDER BY IndividualBudget.Name;")
   DoCmd.OpenQuery "Dynamic_Query"
   DoCmd.OutputTo acOutputQuery, queryDef, acFormatXLS, "C:\IndividualBudget_" & attyName & ".xls", False
   'Set myOutlookApp = CreateObject("Outlook.Application")
   'Set myNameSpace = myOutlookApp.GetNameSpace("MAPI")
   'Set objMail = outlookApp.CreateItem(outlookMailItem)
   
   
   Loop
   Set rstAttorneys = Nothing
   Set dbsIndividualBudget = Nothing
   
 End If

End Function

Answer
Well your first problem is you are naming a variable with a reserved word. naming a QueryDef type as QueryDef is going to confuse matters.

Public Function DistributeBudget()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As queryDef
Dim attyName As String

'Dim outlookApp As Outlook.Application
'Dim objMail As Outlook.MailItem

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Distinct [Name] " & _
        "FROM IndividualBudget where [Name] is not Null")

  On Error Resume Next
  db.QueryDefs.Delete ("Dynamic_Query")
  rs.Movefirst
If rs.BOF OR rs.EOF Then
  MsgBox "No records to process"
Else
MsgBox ("in the loop")
  MsgBox (rstAttorneys.GetRows)
  
  Do Until rs.EOF
  attyName = rs.Fields("Name")
  Set qd = db.CreateQueryDef("Dynamic_Query", "SELECT Distinct [Name], " & _
  "[Unit/Practice Area], [Account Name], " & _
  "Description, [Total Budgeted for 2009]," & _
  "[Actual YTD_thru 5/31/09] , Remaining_Budget_Balance " & _
  "FROM IndividualBudget WHERE [Name] ='" & attyName & _
  " ORDER BY [Name];")

  DoCmd.OpenQuery "Dynamic_Query"
  DoCmd.OutputTo acOutputQuery, queryDef, acFormatXLS, "C:\IndividualBudget_" & attyName & ".xls", False
  'Set myOutlookApp = CreateObject("Outlook.Application")
  'Set myNameSpace = myOutlookApp.GetNameSpace("MAPI")
  'Set objMail = outlookApp.CreateItem(outlookMailItem)
  
  rs.MoveNext   
  Loop
  Set rs = Nothing
  Set db = Nothing
  Set qd = Nothing
  
End If

End Function

Name is also a reserved word in Access and shouldn't be used an Object name. Another problems was there was no MoveNext before the Loop. So more housekeeping, its not a good idea to use spaces in object names. And you don't want to make your variables too long.

But you did very good to come up with what you did. You understood exactly what I was telling you and came up with the code to do it. You just had some minor syntactical errors.

Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA