Connecting Tech Pros Worldwide Forums | Help | Site Map
bytes > topic > access > answers

Can't get query to close before looping to requery

Newbie
  
Join Date: Oct 2007
Location: Bay Area, CA
Posts: 26
#1: Nov 1 '07
Hi there,
I'm woking on a function that takes the input from a form (including Payroll Contact), uses it as a query parameter, runs the query, and then emaills out a custom .xls file using the sendObject method to the Payroll Contact specified in a list.

After perfroming this function it loops to the next [PayrollContact]person on the list and requeries to send them a custom list.

The problem: After sending the first list the first query will not close causing the same list to be sent to each of the PayrollContacts in individual emails.

If I can get the query to close and reopen after I've moved to the next record it should refresh itself with the correct data.

Any ideas on what's wrong with my code? All help is greatly apprecated. (I'm using Access 2003 on XP)

Expand|Select|Wrap|Line Numbers
  1. Public Function SendEMail()
  2. Dim rs As DAO.Recordset
  3.     Dim strEmailRec As String
  4.     Dim strSubject As String
  5.     Dim strMessageText As String
  6.  
  7.     Set rs = CurrentDb.OpenRecordset("SELECT * FROM MailList")
  8.      Do While Not rs.EOF
  9.  
  10.             'Set the contents of the email
  11.             strEmailRec = rs.Fields("[EmailAddress]").Value
  12.             strSubject = "Q3 2007 eIP - Payout File - " & rs.Fields("[WorkLocation]").Value & " " & rs.Fields("[PA]").Value
  13.             strMessageText = "Hi " & rs.Fields("[PayrollContact]").Value & "," & vbNewLine & vbNewLine & "Please find attached the Q3 eIP Payout File to be processed with your November 2007 payroll." & vbNewLine & vbNewLine & "The Total Payout Amounts to be paid out are in column BP." & vbNewLine & "The Payout Currency is in column BO." & vbNewLine & vbNewLine & "Please let me know if you have any questions." & vbNewLine & vbNewLine & "Best regards,"
  14.  
  15.             'Send the e-mail with the spreadsheet attached. Change TRUE to FALSE if you don't want to review the e-mail
  16.             DoCmd.SendObject acQuery, "qryLocationCut", acFormatXLS, strEmailRec, "", "", strSubject, strMessageText, True, ""
  17.  
  18.             'Close the currently open query
  19.             DoCmd.Close acQuery, "qryLocationCut", acSaveNo
  20.  
  21.             rs.MoveNext
  22.  
  23.             'Open the control panel form and advance to the next record
  24.             DoCmd.OpenForm "frmControlPanel", acNormal
  25.             DoCmd.GoToRecord acActiveDataObject, "frmControlPanel", acNext
  26.             'open the location cut query based on the next record.
  27.             DoCmd.OpenQuery "qryLocationCut", acViewNormal, acEdit
  28.  
  29.  
  30.         Loop
  31. End Function

nico5038's Avatar
Moderator
  
Join Date: Nov 2006
Location: The Netherlands
Posts: 2,232
#2: Nov 3 '07

re: Can't get query to close before looping to requery

Instead of the DoCmd query manipulation I would use a "template" query e.g. named qryLocationCutTemplate like:
select * from tblX where WorkLocation = '$WorkLocation$')
In the code I would use:
Expand|Select|Wrap|Line Numbers
  1. dim qdT as DAO.QueryDef ' Make sure you have a reference set to the DAO library
  2. dim qd as DAO.QueryDef
  3.  
  4. set qdT=currentdb.querydefs("qryLocationCutTemplate")
  5. set qd=currentdb.querydefs("qryLocationCut")
  6.  
  7. ' Now in the loop use before executing the SendEmail:
  8. qd.SQL = Replace(qdT.SQL,'$WorkLocation$',rs!WorkLocation)
  9.  
Getting the idea ?

Nic;o)
Newbie
  
Join Date: Oct 2007
Location: Bay Area, CA
Posts: 26
#3: Nov 5 '07

re: Can't get query to close before looping to requery

Can you explain more about using the template? I'm not sure I get it after all...
nico5038's Avatar
Moderator
  
Join Date: Nov 2006
Location: The Netherlands
Posts: 2,232
#4: Nov 6 '07

re: Can't get query to close before looping to requery

Try:
Expand|Select|Wrap|Line Numbers
  1. qd.SQL = Replace(qdT.SQL,"$WorkLocation$",rs!WorkLocation)
  2.  
The template is needed to make sure the part to replace ($WorkLocation$) is "stable". After updating the $WorkLocation$ in qd, the next time the location needs to be replaced you would need to know the previously entered location...

Nic;o)
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#5: Nov 9 '07

re: Can't get query to close before looping to requery

Quote:

Originally Posted by raddrummer

Can you explain more about using the template? I'm not sure I get it after all...

This would be a query in your database that is simply used for making copies from. A "Template" query.
What you do is :
  1. Design and save the template query (one off).
  2. For each use :
    • Copy the template query to a new / temporary query.
    • Update the SQL with your own modified SQL.
    • Run it.
    • Delete it.
Newbie
  
Join Date: Oct 2007
Location: Bay Area, CA
Posts: 26
#6: Nov 12 '07

re: Can't get query to close before looping to requery

Thanks NeoPa,

I'm playing with this but still getting an error message saying that a dialog box is open on line 30.

any ideas????


Expand|Select|Wrap|Line Numbers
  1.  
  2. Public Function SendEMail()
  3. Dim rs As DAO.Recordset
  4. Dim strEmailRec As String
  5. Dim strSubject As String
  6. Dim strMessageText As String
  7. Dim qdT As DAO.QueryDef ' Make sure you have a reference set to the DAO library
  8. Dim qd As DAO.QueryDef
  9.         Set rs = CurrentDb.OpenRecordset("SELECT * FROM PayrollContacts")
  10.         Set qdT = CurrentDb.QueryDefs("qryLocationCutTemplate") 'Master Template
  11.         Set qd = CurrentDb.QueryDefs("qryLocationCutTemporary") 'Master Template copies itself to this query and replaces criteria based on current record.
  12.  
  13.         Do While Not rs.EOF
  14.  
  15.             'Create a copy of the template called qryLocationCutTemporary
  16.             DoCmd.CopyObject , "qryLocationCutTemporary", acQuery, "qryLocationCutTemplate"
  17.  
  18.             'Replace SQL with that from current record
  19.             qd.SQL = Replace(qdT.SQL, "$PA$", rs!PA)
  20.             qd.SQL = Replace(qdT.SQL, "$WorkLocation$", rs!WorkLocation)
  21.             qd.SQL = Replace(qdT.SQL, "$PayrollContact$", rs!PayrollContact)
  22.  
  23.  
  24.  
  25.             'Set the contents of the email
  26.             strEmailRec = rs.Fields("[EmailAddress]").Value
  27.             strSubject = "Q3 2007 eIP - Payout File - " & rs.Fields("[WorkLocation]").Value & " " & rs.Fields("[PA]").Value
  28.             strMessageText = "Hi " & rs.Fields("[PayrollContact]").Value & "," & vbNewLine & vbNewLine & "Please find attached the Q3 eIP Payout File to be processed with your November 2007 payroll." & vbNewLine & vbNewLine & "The Total Payout Amounts to be paid out are in column BP." & vbNewLine & "The Payout Currency is in column BO." & vbNewLine & vbNewLine & "Please let me know if you have any questions." & vbNewLine & vbNewLine & "Best regards,"
  29.  
  30.             'Send the e-mail with the spreadsheet attached. Change TRUE to FALSE if you don't want to review the e-mail
  31.             DoCmd.SendObject acQuery, "qryLocationCutTemporary", acFormatXLS, strEmailRec, "", "", strSubject, strMessageText, True, ""
  32.  
  33.             'Close the currently open query
  34.             DoCmd.DeleteObject acQuery, "qryLocationCutTemporary"
  35.  
  36.             rs.MoveNext
  37.  
  38.  
  39.  
  40.         Loop
  41. End Function
  42.  
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#7: Nov 12 '07

re: Can't get query to close before looping to requery

Let me see if I can see anything when I get home with a bit more time available...
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#8: Nov 12 '07

re: Can't get query to close before looping to requery

Rad,

I like some of your code. There are some tidy ideas in there.
I've made some small sugested changes and my comments (only to stand out from yours) start at the left.
It's never a good idea to change (or recreate) an object while there is a pointer to it currently active (qd), so I moved the Set into the loop AFTER the copy. Similarly, deleting an item that is currently in a pointer (referenced) is not a good idea.

I'm not sure this will fix your particular problem though, as that seems to be connected with a dialog box remaining open. Possibly due to the [EditMessage] parameter being set to TRUE in your call. I don't know for sure. Try this anyway, perhaps the problem will have sorted itself out in the wash.
Expand|Select|Wrap|Line Numbers
  1. Public Function SendEMail()
  2. Dim rs As DAO.Recordset
  3. Dim strEmailRec As String
  4. Dim strSubject As String
  5. Dim strMessageText As String
  6. Dim qdT As DAO.QueryDef ' Make sure you have a reference set to the DAO library
  7. Dim qd As DAO.QueryDef
  8.  
  9.         Set rs = CurrentDb.OpenRecordset("SELECT * FROM PayrollContacts")
  10.         Set qdT = CurrentDb.QueryDefs("qryLocationCutTemplate") 'Master Template
  11.  
  12.         Do While Not rs.EOF
  13.  
  14.             'Create a copy of the template called qryLocationCutTemporary
  15.             DoCmd.CopyObject , "qryLocationCutTemporary", acQuery, "qryLocationCutTemplate"
  16. 'Line moved into the loop to run after object created.
  17.             Set qd = CurrentDb.QueryDefs("qryLocationCutTemporary") 'Master Template copies itself to this query and replaces criteria based on current record.
  18.  
  19.             'Replace SQL with that from current record
  20.             qd.SQL = Replace(qdT.SQL, "$PA$", rs!PA)
  21.             qd.SQL = Replace(qdT.SQL, "$WorkLocation$", rs!WorkLocation)
  22.             qd.SQL = Replace(qdT.SQL, "$PayrollContact$", rs!PayrollContact)
  23.  
  24.  
  25.  
  26.             'Set the contents of the email
  27.             strEmailRec = rs.Fields("[EmailAddress]").Value
  28.             strSubject = "Q3 2007 eIP - Payout File - " & rs.Fields("[WorkLocation]").Value & " " & rs.Fields("[PA]").Value
  29.             strMessageText = "Hi " & rs.Fields("[PayrollContact]").Value & "," & vbNewLine & vbNewLine & "Please find attached the Q3 eIP Payout File to be processed with your November 2007 payroll." & vbNewLine & vbNewLine & "The Total Payout Amounts to be paid out are in column BP." & vbNewLine & "The Payout Currency is in column BO." & vbNewLine & vbNewLine & "Please let me know if you have any questions." & vbNewLine & vbNewLine & "Best regards,"
  30.  
  31.             'Send the e-mail with the spreadsheet attached. Change TRUE to FALSE if you don't want to review the e-mail
  32.             DoCmd.SendObject acQuery, "qryLocationCutTemporary", acFormatXLS, strEmailRec, "", "", strSubject, strMessageText, True, ""
  33. 'Finish with qd before deleting temp query
  34.             Set qd = Nothing
  35.  
  36.             'Close the currently open query
  37. 'Delete the temp query
  38.             DoCmd.DeleteObject acQuery, "qryLocationCutTemporary"
  39.  
  40.             rs.MoveNext
  41.  
  42.  
  43.  
  44.         Loop
  45. End Function
nico5038's Avatar
Moderator
  
Join Date: Nov 2006
Location: The Netherlands
Posts: 2,232
#9: Nov 13 '07

re: Can't get query to close before looping to requery

Hmm, it would be easier to save a qryDummy for the processing and use:
Expand|Select|Wrap|Line Numbers
  1. dim qd as dao.querydef
  2. dim qdT as dao.querydef
  3.  
  4. set qd = currentdb.querydefs("qryOriginal")
  5. set qdT = currentdb.querydefs("qryDummy")
  6.  
  7. qdT = replace(qd.SQL,"[string to replace]","[string new]")
  8. ' place here the needed command with the qryDummy 
  9.  
The query "qryDummy" can be used directly after the replace.

Nic;o)
Newbie
  
Join Date: Oct 2007
Location: Bay Area, CA
Posts: 26
#10: Nov 13 '07

re: Can't get query to close before looping to requery

Alright, I'm officially frustrated now. The replace method isn't taking and I;m ending up with the entire (unfiltered) ataset as an attachement for all emails.

I am having success getting the recordset to advance when adressing the individual emails though.

I thought maybe tying the recordset to a form and then advancing to the next record in the form and passing the parameters of the current record through to the query might help things but it doesn't....so I'm back to square one.

Any help getting the query to work properly would be most appreciated!!

Thanks!


Expand|Select|Wrap|Line Numbers
  1. Public Function SendEMail()
  2. Dim rs As DAO.Recordset
  3. Dim strEmailRec As String
  4. Dim strSubject As String
  5. Dim strMessageText As String
  6. Dim qd As DAO.QueryDef 'This is the original "Master" query
  7. Dim qdT As DAO.QueryDef 'This is the copy of the original for each use
  8.  
  9.         Set rs = CurrentDb.OpenRecordset("SELECT * FROM PayrollContacts")
  10.         Set qdT = CurrentDb.QueryDefs("qryLocationCutTemplate") 'Master query
  11.  
  12.  
  13.         Do While Not rs.EOF
  14.  
  15.             'Create a copy of the template called qryLocationCutTemporary
  16.             DoCmd.CopyObject , "qryLocationCutTemporary", acQuery, "qryLocationCutTemplate"
  17.              Set qd = CurrentDb.QueryDefs("qryLocationCutTemporary") 'Master query copies itself to this query_
  18.              'and replaces criteria based on current record.
  19.  
  20.  
  21.  
  22.             'Replace SQL with that from current record
  23.             qd.SQL = Replace(qdT.SQL, "$PA$", rs!PA)
  24.             qd.SQL = Replace(qdT.SQL, "$WorkLocation$", rs!WorkLocation)
  25.             qd.SQL = Replace(qdT.SQL, "$PayrollContact$", rs!PayrollContact)
  26.              DoCmd.OpenQuery "qryLocationCutTemporary", acViewNormal, acEdit
  27.  
  28.  
  29.             'Set the contents of the email
  30.             strEmailRec = rs.Fields("[EmailAddress]").Value
  31.             strSubject = "Q3 2007 eIP - Payout File - " & rs.Fields("[WorkLocation]").Value & " " & rs.Fields("[PA]").Value
  32.             strMessageText = "Hi " & rs.Fields("[PayrollContact]").Value & "," & vbNewLine & vbNewLine & "Please find attached the Q3 eIP Payout File to be processed with your November 2007 payroll." & vbNewLine & vbNewLine & "The Total Payout Amounts to be paid out are in column BP." & vbNewLine & "The Payout Currency is in column BO." & vbNewLine & vbNewLine & "Please let me know if you have any questions." & vbNewLine & vbNewLine & "Best regards,"
  33.  
  34.             'Send the e-mail with the spreadsheet attached. Change TRUE to FALSE if you don't want to review the e-mail
  35.             DoCmd.SendObject acQuery, "qryLocationCutTemporary", acFormatXLS, strEmailRec, "", "", strSubject, strMessageText, True
  36.  
  37.             'Finish with qd before deleting temp query
  38.             Set qd = Nothing
  39.  
  40.  
  41.  
  42.             'Close the currently open query
  43.             DoCmd.Close acQuery, "qryLocationCutTemporary"
  44.             DoCmd.DeleteObject acQuery, "qryLocationCutTemporary"
  45.  
  46.             rs.MoveNext
  47.              DoCmd.OpenForm "frmpayrollcontacts", acNormal
  48.              DoCmd.GoToRecord acDataForm, "PayrollContact", acNext
  49.  
  50.         Loop
  51. End Function
  52.  
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#11: Nov 13 '07

re: Can't get query to close before looping to requery

Quote:

Originally Posted by nico5038

Hmm, it would be easier to save a qryDummy for the processing and use:

Expand|Select|Wrap|Line Numbers
  1. dim qd as dao.querydef
  2. dim qdT as dao.querydef
  3.  
  4. set qd = currentdb.querydefs("qryOriginal")
  5. set qdT = currentdb.querydefs("qryDummy")
  6.  
  7. qdT = replace(qd.SQL,"[string to replace]","[string new]")
  8. ' place here the needed command with the qryDummy 
  9.  
The query "qryDummy" can be used directly after the replace.

Nic;o)

Correct me if I'm wrong Nico, but you're saying here that copying and deleting the temp query for every iteration is wasted time and effort. If so, then I agree completely. I wasn't focused on optimising at that stage ;) (My excuse & I'm sticking to it).
I'm also assuming you meant line #7 to read :
Expand|Select|Wrap|Line Numbers
  1. qdT.SQL = Replace(qd.SQL,"[string to replace]","[string new]")
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#12: Nov 13 '07

re: Can't get query to close before looping to requery

@raddrummer - I'm not ignoring you but your explanation is not too clear (no useful details I could see) so I will need to spend more time to understand what you're trying to say. This will have to wait until I get home when I can check again with a more relaxed timeframe.
Newbie
  
Join Date: Oct 2007
Location: Bay Area, CA
Posts: 26
#13: Nov 13 '07

re: Can't get query to close before looping to requery

OK, thanks NEoPa. Let me know what details you need and I'd be happy to post them.


Rad
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#14: Nov 14 '07

re: Can't get query to close before looping to requery

Well Rad, it would be considerate to actually post what it is you've tried (not the code - that's simply a request for me to go to all the trouble of working out what's what by parsing your code) and what did and did not work. If an issue is fixed then you pass that on clearly to save me the effort of reading through the whole thread again to work out where we are. Posting code is not bad in itself, but if you use it to save yourself the trouble of explaining what you changed and what effect that has had on your project, then you will appreciate that working on your problem is going to be even harder than if you communicate clearly. It's always important to know whether or not you've taken any of the previous suggestions on board. Otherwise it's like trying to having a conversation with a mute person - frustrating and nonprogressive.

For instance, I had to read through all of your code to notice that you'd added in new lines at #26 & #43. The only effect I would expect this to have is to stop some of the processing working as the query should not be open during this process at all as far as I can see.

If all this sounds really heavy then I'm sorry, and I'm certainly not trying to flame or insult you. I just don't know any way of getting the point across without it.
nico5038's Avatar
Moderator
  
Join Date: Nov 2006
Location: The Netherlands
Posts: 2,232
#15: Nov 14 '07

re: Can't get query to close before looping to requery

Quote:

Originally Posted by NeoPa

Correct me if I'm wrong Nico, but you're saying here that copying and deleting the temp query for every iteration is wasted time and effort. If so, then I agree completely. I wasn't focused on optimising at that stage ;) (My excuse & I'm sticking to it).
I'm also assuming you meant line #7 to read :

Expand|Select|Wrap|Line Numbers
  1. qdT.SQL = Replace(qd.SQL,"[string to replace]","[string new]")

It's not "better", but giving the same result and personally I prefer as little as code as necessary.
Your posted code:
Expand|Select|Wrap|Line Numbers
  1.             qd.SQL = Replace(qdT.SQL, "$PA$", rs!PA)
  2.             qd.SQL = Replace(qdT.SQL, "$WorkLocation$", rs!WorkLocation)
  3.             qd.SQL = Replace(qdT.SQL, "$PayrollContact$", rs!PayrollContact)
  4.  
won't work as you're replacing only one field and place it into qd.SQL
It shold look like:
Expand|Select|Wrap|Line Numbers
  1.             qd.SQL = Replace(qdT.SQL, "$PA$", rs!PA)
  2.             qd.SQL = Replace(qd.SQL, "$WorkLocation$", rs!WorkLocation)
  3.             qd.SQL = Replace(qd.SQL, "$PayrollContact$", rs!PayrollContact)
  4.  
The qdT.SQL should only be used "initially", then continue with the qd.SQL !

Best to place a breakpoint in the code and use F8 to singlestep through the code to see what's happening. In the immediate window you can type:
?qd.SQL
and press enter to see the result. A great way to see that code is working as expected (or not :-)

Getting the idea ?

Nic;o)
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#16: Nov 14 '07

re: Can't get query to close before looping to requery

Quote:

Originally Posted by nico5038

It's not "better", but giving the same result and personally I prefer as little as code as necessary.

I'm not sure if I understand you correctly Nico :S
Are you saying line #7 was as you wanted it? If so that was certainly something I wasn't aware of (I suppose the .SQL property is the default).
Certainly your other point was a good one and is definitely to be recommended (IE. Not copying the QueryDef every time through the loop).
Quote:

Originally Posted by nico5038

Your posted code:

Expand|Select|Wrap|Line Numbers
  1.             qd.SQL = Replace(qdT.SQL, "$PA$", rs!PA)
  2.             qd.SQL = Replace(qdT.SQL, "$WorkLocation$", rs!WorkLocation)
  3.             qd.SQL = Replace(qdT.SQL, "$PayrollContact$", rs!PayrollContact)
  4.  
won't work as you're replacing only one field and place it into qd.SQL
It shold look like:
Expand|Select|Wrap|Line Numbers
  1.             qd.SQL = Replace(qdT.SQL, "$PA$", rs!PA)
  2.             qd.SQL = Replace(qd.SQL, "$WorkLocation$", rs!WorkLocation)
  3.             qd.SQL = Replace(qd.SQL, "$PayrollContact$", rs!PayrollContact)
  4.  
The qdT.SQL should only be used "initially", then continue with the qd.SQL !

Good spot. That will certainly be causing issues for Rad.
Quote:

Originally Posted by nico5038

Best to place a breakpoint in the code and use F8 to singlestep through the code to see what's happening. In the immediate window you can type:
?qd.SQL
and press enter to see the result. A great way to see that code is working as expected (or not :-)

Getting the idea ?

Nic;o)

There is an article (Debugging in VBA) that may help with this.

Hope this helps.
Newbie
  
Join Date: Oct 2007
Location: Bay Area, CA
Posts: 26
#17: Nov 14 '07

re: Can't get query to close before looping to requery

Oh boy...

1. OK, so does

Set qd = CurrentDb.QueryDefs("qryLocationCutTemporary")

get placed in before the loop starts? If so then I get an error stating that the object could not be found as I don't create it from the template until the loop starts.

when I tried placing
Set qd = CurrentDb.QueryDefs("qryLocationCutTemporary")
inside the loop I got an error message saying that the object already exists and would I like to create it anyway.

2. The criteria for the PA, WorkLocation, and PayrollContacts fields in my queries (qryLocationCutTemplate and qryLocationCutTemporary) use the [PayrollContacts]![PayrollContact] , etc. naming convention to point to the current record in my PayrollContacts Table. I expected the query to pull the values from the current record in PayrollContacts in as criteria, however these aren't filtering at all as I keep getting the entire record set back. Any suggestions here?

Below is a chunk of the current version of the code.

I've moved the query definition back out of the loop to line 11 and have also changed lines 24 and 25 per Nico's suggestion.
Expand|Select|Wrap|Line Numbers
  1. Public Function SendEMail()
  2. Dim rs As DAO.Recordset
  3. Dim strEmailRec As String
  4. Dim strSubject As String
  5. Dim strMessageText As String
  6. Dim qd As DAO.QueryDef 'This is the original "Master" query
  7. Dim qdT As DAO.QueryDef 'This is the copy of the original for each use
  8.  
  9.         Set rs = CurrentDb.OpenRecordset("SELECT * FROM PayrollContacts")
  10.         Set qdT = CurrentDb.QueryDefs("qryLocationCutTemplate") 'Master query
  11.         Set qd = CurrentDb.QueryDefs("qryLocationCutTemporary") 'Master query copies itself to this query_
  12.                  'and replaces criteria based on current record.
  13.  
  14.  
  15.         Do While Not rs.EOF
  16.  
  17.             'Create a copy of the template called qryLocationCutTemporary
  18.             DoCmd.CopyObject , "qryLocationCutTemporary", acQuery, "qryLocationCutTemplate"
  19.  
  20.  
  21.  
  22.             'Replace SQL with that from current record
  23.             qd.SQL = Replace(qdT.SQL, "$PA$", rs!PA)
  24.             qd.SQL = Replace(qd.SQL, "$WorkLocation$", rs!WorkLocation)
  25.             qd.SQL = Replace(qd.SQL, "$PayrollContact$", rs!PayrollContact)
  26.  
nico5038's Avatar
Moderator
  
Join Date: Nov 2006
Location: The Netherlands
Posts: 2,232
#18: Nov 14 '07

re: Can't get query to close before looping to requery

1) Make sure "qryLocationCutTemporary" does exist as a "saved" query in your database.
2) drop the "DoCmd.CopyObject " line and give it a try.

Nic;o)
Newbie
  
Join Date: Oct 2007
Location: Bay Area, CA
Posts: 26
#19: Nov 14 '07

re: Can't get query to close before looping to requery

Now I'm getting the Enter Parameter Value msgbox looking for the values of PayrollContacts!PayrollContact, etc.....


Ist seems like the values from the current record in the record set are not being passed into the query...
nico5038's Avatar
Moderator
  
Join Date: Nov 2006
Location: The Netherlands
Posts: 2,232
#20: Nov 14 '07

re: Can't get query to close before looping to requery

Quote:

Originally Posted by raddrummer

Now I'm getting the Enter Parameter Value msgbox looking for the values of PayrollContacts!PayrollContact, etc.....


Ist seems like the values from the current record in the record set are not being passed into the query...

This implies that fieldnames aren't matching with the table/query.
Use the breakpoint and F8 to investigate the contents of the qd.SQL string.

Nic;o)
NeoPa's Avatar
Administrator
  
Join Date: Oct 2006
Location: London - UK
Posts: 15,730
#21: Nov 15 '07

re: Can't get query to close before looping to requery

First of all, my answers may differ from Nico's but each should work. We just have a slightly different approach and you can simply pick and choose which you prefer.
Quote:

Originally Posted by raddrummer

Oh boy...

1. OK, so does

Set qd = CurrentDb.QueryDefs("qryLocationCutTemporary")

get placed in before the loop starts? If so then I get an error stating that the object could not be found as I don't create it from the template until the loop starts.

when I tried placing
Set qd = CurrentDb.QueryDefs("qryLocationCutTemporary")
inside the loop I got an error message saying that the object already exists and would I like to create it anyway.

I would have the CopyObject outside of and before the loop, and a DeleteObject outside of and after the loop. Having a dummy QueryDef left around in your database permanently for all such occasions is another way to approach this if you prefer (Nico's way).
Quote:

Originally Posted by raddrummer

2. The criteria for the PA, WorkLocation, and PayrollContacts fields in my queries (qryLocationCutTemplate and qryLocationCutTemporary) use the [PayrollContacts]![PayrollContact] , etc. naming convention to point to the current record in my PayrollContacts Table. I expected the query to pull the values from the current record in PayrollContacts in as criteria, however these aren't filtering at all as I keep getting the entire record set back. Any suggestions here?

How complicated is the SQL from your template query? It may be worth posting that here (we can't comment much on this without seeing that). The replaces should work now but they obviously depend on the original SQL including the correct words to replace.

Try adding the following line in the code most recently displayed (post #17) after line #25 :
Expand|Select|Wrap|Line Numbers
  1. Debug.Print "qdT = " & qdT.SQL & vbCRLF & _
  2.             "qd  = " & qd.SQL & vbCRLF & _
  3.             "PA  = " & rs!PA & vbCRLF & _
  4.             "WL  = " & rs!WorkLocation & vbCRLF & _
  5.             "PC  = " & rs!PayrollContact
Hopefully, the results will be illuminating.
Reply

« previous question | next question »

Similar Microsoft Access / VBA bytes

/bytes/development

/bytes/it

Forums
Visit our community forums for general discussions and latest on Bytes

Our staff and community areas have moved to bytes.com/forums/

/bytes/about

We are a network of experts and professionals in IT and software development that help one another with answers to tough questions and share insights. Get the best answers to your questions from over 226,471 network members.

dev questions:
algorithms | asp | asp.net | c/c++ | coldfusion | c# | flash | html/css | java | javascript |
mobile dev | .net | perl | php | python | ruby | software dev | vb.net | visual basic | xml
it questions:
access | apache | careers | db2 | iis | macosx | mysql | networking | oracle | postgresql | sql server | unix | windows

About Bytes | Copyright 1995-2009 BYTES. All rights Reserved