AboutScottgem Expertise I can answer some questions on a wide variety of business applications, including MS Office, Lotus Smartsuite, Visio, Notes and many others.
Experience I have over 16 years of experience as an IT professional, supporting a wide variety of business applications.
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];")
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
