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: Hi Scott, thank you for helping with syntax, the code works up to the point of queryDef creation.
attyName = rs.Fields("Name")
MsgBox (attyName)
Set qd = db.CreateQueryDef("Dynamic_Query", "SELECT [Unit/Practice Area], [Account Name], " & _
"[Total Budgeted for 2009]," & _
"FROM IndividualBudget WHERE [Name] ='" & attyName & _
" ORDER BY [Name];")
DoCmd.OpenQuery "Dynamic_Query"
ANSWER: Ahh I think I see the problem, Try changing this line to:
"' ORDER BY [Name];")
Since AttyName is a text string, you need to enclose it in quites and you are missing the right quote.
By the way, you really should change the NAME field to something like AttyName. in fact what you really should be doing is have an Attorney table with all the details about each attorney. Then use an Autonumber as a primary key and then use that value as a Foreign Key. You shouldn't be repeating the attorney's name so many times.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
Irina,
Thanks for your rating and comments, but I wanted to return the favor. It has been a pleasure helping you. Too many times people ask a question and when I answer giving them what they need to work out a solution, they come back to me asking for clarification instead of using what I gave them to try and work it out. Too often people want too much hand holding. But you took what I gave you (which admittedly was pretty bare bones) and ran with it and made great strides in determing a solution, that just needed a few tweaks from an experienced eye to get to work. And I'm betting you learned a lot more in the process, then if I had just giving you the code.
Please feel free to call on me for any further help you need.
Scott<>
---------- FOLLOW-UP ----------
QUESTION: Hi, I fixed the syntax, that was it, quote missing!
I looked at that line of code million times and did not notice. THANKS!
Yes I know what you mean with answering questions, easier to ask a question and then copy the code, I actually enjoy learning with the right teacher!
What is happening now is I get the attachment with filtered records for the first name, address to that person - which is correct, the second email is not - addressed to a second name on the list but the attachment is still for that first person. Like it is not rebuidling
the query to filter for a new name... if that makes sense to you the way I am trying to explain...
rs.MoveFirst
If rs.BOF Or rs.EOF Then
MsgBox "No records to process"
Else
Do Until rs.EOF
attyName = rs.Fields("Name")
strQueryName = "Individual_Budget"
strSQL = "SELECT [Unit/Practice Area], [Account Name], Description, [Total Budgeted for 2009]," & _
"[Actual YTD_thru 5/31/09] , [Remaining_Budget_Balance] FROM IndividualBudget WHERE ((IndividualBudget.Name)= '" & attyName & "')"
DoCmd.SendObject acSendQuery, "Individual_Budget", acFormatXLS, attyName, , , "Month to Date Marketing Budget"
I tried to delete the query right after export, and it still does not work.
Any suggestions I would appreciate this.
thanks
Irina
Answer Ok, you changed to using SendObject instead of Outlook automation. Its not an issue.
But this is different from the other process. Individual_Budget is the name of your query. to include a named query in the SendObject, you need to define it as a QueryDef. So, on the previous question where you have the OpenQuery that should be your SendObject instead. After you Send, you do a rs.MoveNext followed by a Loop. I though your OpenQuery was just to test that the querydef was working and producing a query for a different attorey with each pass.
Hope this helps,
Scott<>
Microsoft Access MVP 2007
Author: Microsoft Office Access 2007 VBA
