Execute Query inside a Do Until Loop

[BamaColtsFan]

I'm having a little trouble getting started with my Do Until loop. I think I understand the structure I need to use except that I'm not really sure how to get the loop to execute my query using the values from the control list as variables. Basically, I want the loop to execute the query and return only those records belonging to the specified department for each run of the loop. What I think I'm missing is how do I pass the value for department into the query so the loop knows what to do with it? Below is what I have so far, but I'm sure I'm way off. Please offer any tricks that jump to mind...

Public Function CopyToWorkbook()

Dim db As DAO.Database
Dim newPath As DAO.Recordset
Dim strPath As String
Set db = CurrentDb()
Set newPath = db.OpenRecordset("Set_Path")

Set DeptList = db.OpenRecordset("qryDepartmentList")

strPath = newPath!path & "CombinedTimecards_Crosstab.xlsx"


Do Until DeptList.EOF

     DoCmd.TransferSpreadsheet acExport, 8, "qryDelinquentList", "strPath", True, "Delinquent_List"

Loop

End Function

 

[pbaldy]

Have the query look to a form control or public function in its criteria, and set either during your loop. You may want to set the path inside the loop too, or the file will keep overwriting itself.

 

[BamaColtsFan]

Paul - I'm sorry but I think I am just being dense about this... I've tried the better part of the afternoon to figure out how set up a public function for the criteria and I'm getting no where. I'm really not even sure this is what you meant for me to do...

This is what I have so far:

Public Function SetMyDept()

Dim db As DAO.Database
Dim myDept As DAO.Recordset
Dim newDept As String
Set db = CurrentDb()
Set myDept = db.OpenRecordset("qryDepartmentCodes")

Do Until myDept.EOF

newDept = myDept!Dept

MsgBox newDept

newDept = Nothing

Loop

End Function

What this does is select the first department in the list, display it in the message box and then locks into a loop that it can't get out of. It continues to display the message box with the first department code and won't advance to the next. It also won't stop this loop. By the way, I'm using the message box just so I have something to display the code, I'm assuming I need to remove it later so it can be called into the query. Again, any help you can provide is appreciated!

 

[pbaldy]

Sorry, I didn't notice you missed this in the original too:

Do Until myDept.EOF

newDept = myDept!Dept

MsgBox newDept

newDept = Nothing
myDept.MoveNext
Loop

 

[BamaColtsFan]

Paul - Ok, great! That works for looping through the list. Now I just have to figure out how to get it to do what I need! Thanks again!

 

[pbaldy]

Easiest is probably to have the query look to a form for its criteria:

Forms!FormName.ControlName

Then in your loop before running the export:

Forms!FormName.ControlName = myDept!FieldName