Run Code for each record (1 Viewer)

[gsdude]

Hey I'm trying to see if its possible to run some code for each record in a query.
This is my scenario right now,
I have multiple employees and each are assigned to a supervisor.
For each Supervisor I export the list of employees to an excel sheet template. (signin sheet)
Right now I have a drop down that selects the supervisor and i press the button to run the code and it opens excel and fills in the data.
I would like to run this same code for each supervisor that I have with a push of a button.
Any help would be appreciated.

 

[Adam Caramon]

You'll need to provide the code for someone to confidently give you a good answer. It might be that the drop-down is applying a filter to the data, and then the data is exported to Excel. If that were the case, you would either remove the filter, or change the drop-down so that when a supervisor is not selected, it is treated as a select all type filter (*).

 

[gsdude]

The dropdown is from a Query of Active Supervisors
This is the code I run when I Press the button.

Private Sub cmdSigninSheet_Click()
Dim objXL As Object
Dim xlWB As Object
Dim xlWS As Object
Dim rst As dao.Recordset
Dim lngCount As Long
Dim Directory, OldPath, NewPath As String

 'Start Excel
    On Error Resume Next
    Set objXL = GetObject(, "Excel.Application")   
    If Err.Number <> 0 Then   
        Err.Clear
        On Error GoTo Error_Handler
        Set objXL = CreateObject("excel.application")
    End If
    On Error GoTo Error_Handler
    
Directory = CurrentProject.Path
OldPath = [Directory] & "\" & "Attachments\Templates\SIGN-IN.xlsx"
NewPath = [Directory] & "\" & "Attachments\Temporary\SIGN-IN-" & Me.ComboActiveSupervisors.Column(4) & ".xlsx"
FileCopy FullPath, NewPath

objXL.Visible = True

Set xlWB = objXL.Workbooks.Open([NewPath])
 
Set xlWS = xlWB.Worksheets("SIGN-IN")
 
Set rst = CurrentDb.OpenRecordset("SELECT * FROM qrySupervisortoEmployees where " & "[SupervisorID] = " & Me.ComboActiveSupervisors & "")
 
lngCount = 7
 
With rst
   Do Until .EOF
      xlWS.Range("E" & lngCount).Value = rst!LastName
      xlWS.Range("G" & lngCount).Value = rst!FirstName
      xlWS.Range("H" & lngCount).Value = rst!Code
      xlWS.Range("I" & lngCount).Value = rst![J&ECode]
     ' and so on
      .MoveNext
      lngCount = lngCount + 1
   Loop
 End With
Set rst = Nothing
 objXL.Visible = True


Set objXL = Nothing

Error_Handler:
    Exit Sub
    
End Sub

 

[jdraw]

The general logic for your intended approach would be along this pattern.

Make a recordset of your QueryForSupervisors
Do while not  rsQueryForSupervisors.eof
   
        [COLOR="Blue"]Do this block of code/processing
                    for each Supervisor[/COLOR]
rsQueryForSupervisors.movenext 
Loop

 

[gsdude]

I tried your suggestion, but when i ran the code all it did was run the first supervisor in the recordset. Which came out fine. Is the problem my code that opens an excel file and fills in the data?

 

[MarkK]

Probably this would be simplest and clearest if you used two loops,

1) an outer loops that goes thru each super and...
   1a) creates a file F for current super, and 
   2) an inner loop, that, for current super, goes thru each related employee, and
      2a) adds the current employee to the file F.
      2b) gets the next employee
   1b) closes the file F
   1c) gets the next super

You appear to be trying to do this in one loop on a joined query, which is possible, but then you need code that keeps track of when the current super changes, and then creates a new file when that change occurs.

hth
Mark

 

[gsdude]

Thanks for the help, I was able to make it work, by simply making my original code into a function and then called it within the loop and seemed to work fine, for whatever reason it would not work just by simply put the code directly into the loop.