Expert: Scottgem - 7/20/2009

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

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

QUESTION: I am getting confused now.. sorry
Yes, I am using SendObject because creating a mail item was giving me a ton of reference issues. I took out OpenQuery statement because that was just for testing purposes. After I do sendObject I do have rs.MoveNext and Loop, also I am deleting the query too BUT the code still does what it did on friday.
It attaches the first filtered resultset address to first person on the list, then it attached the same resultset filtered for the first person addresses it to second person and so on, it is not looping thru.
What should I try doing next, should I exit the loop completely and then somehow create a counter and add 1 to it???
Thanks, Irina
Public Function Budget()
Dim db As DAO.Database
Dim rs As DAO.Recordset
Dim qd As queryDef
Dim attyName As String
Dim strQueryName As String

Dim strSQL As String

Set db = CurrentDb
Set rs = db.OpenRecordset("SELECT Distinct [Name] " & _
"FROM IndividualBudget where [Name] is not Null ORDER BY [Name]")
  
   On Error Resume Next
   db.QueryDefs.Delete ("Individual_Budget")
   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 & "')"

Set qd = db.CreateQueryDef(strQueryName, strSQL)

   DoCmd.SendObject acSendQuery, "Individual_Budget", acFormatXLS, attyName, , , "Month to Date Marketing Budget", "We have developed a new method of tracking marketing expenditures that should greatly help you in managing your business development efforts over the course of the year.  Attached is a year-to-date summary of your marketing expenses.  You will be receiving an update like this each month starting today." & _
"This format should prove easy to use and should also provide you with a tool for planning next year's budget.  Please notify me and your unit manager if your remaining marketing budget is insufficient for what you have planned.  Conversely, if you don't plan on spending all of your marketing budget this year, please let us know." & _
"If you have any questions, please contact me."


 rs.MoveNext
   Loop

 db.QueryDefs.Delete ("Individual_Budget")
     
     Set rs = Nothing
     Set db = Nothing
     Set qd = Nothing
End If
End Function


Answer
What you should do is STEP through the code seeing what actually happens. To do this you set a watchpoint by clikcing in the left hand margin next to the line of code. I would set the watchpoint at the Top of the loop (the Do While statement). When you execute the code, it will stop at the Watchpoint and open the VBE. You can then use the Step icon to execute each line one by one. Look in the Locals window to see the value of each variable. After the Set qd line

go to the main Access window to see what the query looks like.

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