Split table to different export files based on table field value

[Scaniafan]

Good morning all,

First, lets start with my goal:

I've got a VBA which creates a temporary table with some data fields, including one field called Forwarding Agent. This table is created based on forwarding agents selected in a multi-select box on a form. The field can contain one forwarding agent up to 292 (current number of agents). These agents are listed in a source table. What I'm aiming to do is export the records in the temporary table to different export folders per forwarding agent. For example, if the table contains data of Agent1 and Agent2, the table should be split and exported to folder CurrentProject.Path \Export\Agent1\ etc. If the folder doesn't exist, it should be created.

What have I been thinking of:

I can drive this based on below code, which means I have to create 292 different strSQL for each agent.

strSQL "SELECT TBL_Gross_Performance_Forwarder_Temp.* " _
 & "INTO TBL_Gross_Performance_Forwarder_Temp_2" _
  & "FROM TBL_Gross_Performance_Forwarder_Temp" _
   & "WHERE (((TBL_Gross_Performance_Forwarder_Temp.[Forwarding agent])="Agent1"));"

DoCmd.Run strSQL

If DCount("*", "TBL_Gross_Performance_Forwarder_Temp_2") > 0 Then

 If Len(Dir(CurrentProject.Path & "\Export\Agent1\", vbDirectory)) = 0
  Then MkDir CurrentProject.Path & "\Export\Agent1\"
 End If
                                   
  DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, "TBL_Gross_Performance_Forwarder_Temp_2", CurrentProject.Path & "\Export\Agent1\" & Format(Date, "yyyymmdd") & " - " & Format(Time, "hhnn") & " - Gross Performance Agent1 (" & Environ("Username") & ").xlsx", True
            
   MsgBox "Export completed. File exported to:" & vbNewLine & vbNewLine & CurrentProject.Path & "\Export\Agent1\", vbInformation, "Export completed"
            
  Else
            
   MsgBox "No records in master data for given criteria.", vbInformation, "No records found"
        
End If

Preferably, I would have a code which checks the unique entries in the temporary table and populates a variable based on these, with a strSQL which cycles through this variable.

This would ease the amount of coding and has the benefit that I can replace all the "Agent1" in the code above with the variable entry.

Any help getting this up and running is appreciated

 

[Scaniafan]

Solved this one myself with Google help

First I call the sub that creates the overall temporary table. From that table, I create a secondary table that simply contains the Forwarder Agents that are in the temporary table.

Public Sub GrossPerformanceForwarder_Click()

        Call GrossPerformance
        
        DoCmd.SetWarnings False
        
          DoCmd.RunSQL "SELECT TBL_Gross_Performance_Forwarder_Temp.ForwardingAgent INTO TBL_Forwarding_Agent_Temp FROM TBL_Gross_Performance_Forwarder_Temp GROUP BY TBL_Gross_Performance_Forwarder_Temp.ForwardingAgent;"
    
        DoCmd.SetWarnings True

Second I use below code of which I do not exaclty know how it works As far as I can follow, it uses the secondary table as input for the BaseSQL, creating a separate query for each entry in the table, running the query, exporting the results and deleting the specific query. After that, it loops with the next entry.

          Dim TBL_Forwarding_Agent_Temp As Recordset
          Dim ForwarderCode As String
          Dim Base_SQL As String
          Dim QueryDefName As String
        
          Base_SQL = "SELECT * FROM TBL_Gross_Performance_Forwarder_Temp WHERE ForwardingAgent = "
        
          Set TBL_Forwarding_Agent_Temp = CurrentDb.OpenRecordset("TBL_Forwarding_Agent_Temp")
        
          Do While Not TBL_Forwarding_Agent_Temp.EOF
        
            ForwarderCode = TBL_Forwarding_Agent_Temp("ForwardingAgent")
                    
            QueryDefName = "QRY_" & ForwarderCode & "_Gross"
            CurrentDb.CreateQueryDef QueryDefName, Base_SQL & "'" & ForwarderCode & "'"
           
        If Len(Dir(CurrentProject.Path & "\Export\Forwarder Files\" & ForwarderCode & "\", vbDirectory)) = 0 Then
                MkDir CurrentProject.Path & "\Export\Forwarder Files\" & ForwarderCode & "\"
        End If
           
            DoCmd.TransferSpreadsheet acExport, acSpreadsheetTypeExcel12Xml, QueryDefName, CurrentProject.Path & "\Export\Forwarder Files\" & ForwarderCode & "\" & Format(Date, "yyyymmdd") & " - " & Format(Time, "hhnn") & " - Gross Performance " & ForwarderCode & ".xlsx", True
                       
            CurrentDb.QueryDefs.Delete QueryDefName
        
            TBL_Forwarding_Agent_Temp.MoveNext
        
          Loop
          
        MsgBox "Export completed. Files exported to the carrier specific folder in:" & vbNewLine & vbNewLine & CurrentProject.Path & "\Export\Forwarder Files\"

    End Sub